CODEWITHSUNDEEP
sqlsql-serverazureazure-sql-database
user2687506
user2687506

Reputation: 800

Indexing SQL-database slow down inserts too much

I have 2 queries taking too long time, timing out when running them inside an azure website.

1st.

SELECT Value FROM SEN.ValueTable WHERE OptId = @optId

2d

INSERT INTO SEN.ValueTable (Value, OptId) 
SELECT Value, OptId FROM REF.ValueTable WHERE OptId = @optId

The both SELECTS will always return 7860 Values. The problem is that I do around 10 of these queries with different @optId. First I ran without any Indexes, then the 1st Query would timeout every now and then. I then added a non-clustered index to SEN.ValueTable and then the 2d Query began to timeout.

1st Query from an Azure VM

2d Query from an Azure-WebApp

I've tried to increase the timeout-time through the .config-files, but they still timeout within 30seconds (There is no time limit from the customer, the retrieving of data from the sql-database will not be the slow thing of the application anyway).

Is there anyway to speed it up/get rid of the timeouts? Will indexing the REF.ValueTable speed the insert up anything?

Upvotes: 0

Views: 1991

Answers (3)

shivam
shivam

Reputation: 11

Try resumable online index rebuild - https://azure.microsoft.com/en-us/blog/resumable-online-index-rebuild-is-in-public-preview-for-azure-sql-db/

Upvotes: 0

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

If you really run such a query:

SELECT Value, OptId
FROM REF.ValueTable
WHERE OptId = @optId;

The best index for it would be the following:

CREATE INDEX idx_ValueTable_OptId_Value
    ON REF.ValueTable (OptId)
    INCLUDE (Value);

Any index will slow inserts down, but will benefit read queries. If you want more elaborate answer, post more details - table DDLs and execution plans.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269753

First, the obvious solution is to add an index to SEN.ValueTable(OptId, Value) and to have no index on REF.ValueTable(OptId, Value). I think this gets around your performance problem.

More importantly, it should be not be taking 30 seconds to fetch or insert 7,860 rows -- nothing like that. So, what else is going on? Is there a trigger on REF.ValueTable() that might be slowing things down? Are there other constraints? Are the columns particularly wide? I mean, if Value is VARCHAR(MAX) and normally 100 Mbytes, then inserting values might be an issue.

Upvotes: 2

Related Questions