asked
query timeout
I have a report builder,User can add any field that need and also can filter on any field/s. the base table has more than 20 million records and I show only 20-30 rows [using paging]. when I filter the query, time out error happens. should I create covered index? on which fields ? Thanks in advance
Do you need to let your users choose any columns to filter on? if you could limit it to some common ones you could get the indexing right for those otherwise you will find getting the right indexing difficult but keeping an eye on the missing index stats and unused indexes might be a good way to go, i.e. Let your users dictate what indexes would be best.
I can't tell you what to do with the query because I can't see the query, the structures or the execution plan. You would need to look to all three to understand what to do to make the query run faster. The execution plan is especially important to understand what the optimizer chose to do with the query. If you need help reading an execution plan, you can download a [free copy of the book][1] I wrote on the subject. [1]:
http://www.sqlservercentral.com/articles/books/65831/
Thanks for your answer. I downloaded the book.also I attach execution plan.but note that the select fields and conditions can change depending on a user choice. now as a try/error case, I added a covered index on all foreign keys and now the query runs fast. but I 'm not sure that it answers in all different queries & is the best way! here is my query : DECLARE @ROWCOUNT INT; SELECT @ROWCOUNT = COUNT(*) FROM [LO].[vTaskKolli] as [TK] WHERE ([TK].[Fk_HeadingCode] = 4) AND ([TK].[Fk_ApprovalCode] = 1) SELECT @ROWCOUNT AS TotalRowCount ; SELECT [TK].[Fk_BankCodeValue] as TK_Fk_BankCodeValue, [TK].[Fk_PurposeOfCreditCodeValue] as TK_Fk_PurposeOfCreditCodeValue, [TK].[Fk_HeadingCodeValue] as TK_Fk_HeadingCodeValue, [TK].[Fk_ApprovalCodeValue] as TK_Fk_ApprovalCodeValue FROM [LO].[vTaskKolli] as [TK] WHERE ([TK].[Fk_HeadingCode] = 4) AND ([TK].[Fk_ApprovalCode] = 1) ORDER BY [TK].[Id] OFFSET 0 ROWS FETCH NEXT 30 ROWS ONLY
Which is going against a view right? So now we have other issues that are masked there as well. In general, you're filtering on two columns and sorting by a third. You could have an index with one of those two filtering columns as the leading edge and the other two columns as part of the key and you'll possibly see good performance... BUT, that assumes that we're looking at a table. Since we're looking at a view, I don't know that what I typed is actually accurate or even possible (you can't index across multiple tables).
16 People are following this question.
