Indexing Question
-
Hi
I have noticed that some of our queries are not using the indexes that I think they should be. I thought that the way it worked was that the query would look for an index that uses the first criteria in the WHERE clause. Think I may be wrong with this assumption? We have a fairly straightforward SELECT query which is also doing a few SUMS on some columns which has the criteria WHERE INVOICENUMBER >= @invoicestart AND INVOICENUMBER <= @invoiceend
We have an index on the InvoiceNumber column but the query is not using the index. Why would this be? The table has a few million rows in it so the query is working really hard to get the data out. Currently takes 20 mins to return the results but if I force it to use the InvoiceNumber index it runs in 5 mins.
Any ideas why it won't use the index of it's own accord?
Thanks
-
Perhaps you can post the query?
Is the INVOICENUMBER column the only column specified in the index?
-
This is the query, it's pretty simple, I guess it's taking so long because there are millions of rows in the table. Am confused why it doesn't automatically use the Invoice Index. When I look the execution plan it is using the clustered index which does not contain the Invoice field which is why I've added the Index in as a hint below.
declare @invoicestart int
declare @invoiceend int
set @invoicestart = 553973
set @invoiceend = 567116
SELECT InvoiceNumber, InvoiceSegNumber, 'Voice' AS 'Type', Network, DiscCat1,
SUM(GrossWSPrice) AS 'GrossWSPrice',
SUM(WSPrice) AS 'WSPrice', SUM(SellPrice) AS 'SellPrice',
SUM(CASE WHEN NETWORK = 'C' THEN WSPriceDC1 ELSE 0 END) AS 'O2CreditDue'
FROM SPCALLS
with (index(ByInvoiceAndInvoiceSeg))
WHERE INVOICENUMBER >= @invoicestart AND INVOICENUMBER <= @invoiceend
group by invoicenumber, invoicesegnumber, network, disccat1
-
Well, it's possible that if there are several columns in the index it won't be considered in the query if only one column is in the list. If possible you might consider adding a very narrow index on only the INVOICENUMBER column or perhaps widening it a bit to include the columns in the GROUP BY clause and seeing if this has any effect on index selectivity. You might also consider using the BETWEEN syntax instead of >= and <=. I've seen some situations where differing syntax can have an effect on index selectivity (though not that example in specific).
-
The reason for not using the index is that it considers more expensive to perform bookmarkLookup " for the rest of the columns in your query" than scaning the clustered.
You could probably build a composite index to cover the entire thing but I have no clue how your tables look like.
* Noel -
The above comments are good advice. I will also update statistics on the table(s)/indexes referenced. What happens sometimes is that the statistics on the particular table is outdated and the data has changed quite substantially.
The query optimizer then makes a choice based on the statistics and might decide not to use your index.
Hope this helps.
-
Thanks everyone for your replies.
I tried the query using the between operator but this didn't make any difference. It did make a difference when I took out all the columns other than the invoice columns, the query then used the correct Index, as soon as I put one column back into the select list that was not specified in the index it started using a different index again. I'll take a look at adding some extra columns to the index.
Is it ok to force the use of the index in the query or would it be better to alter the index to include more columns?
-
I would not advise you to force the query to use a specific index. Rather structure your indexes so that the optimizer chooses the correct index.
There are always exceptions to the rule though
Martin.
-
you might want to try dropping your query into a proc and passing parameters to the proc and see if there's a difference.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
1) Parameter sniffing could be at issue here. Perhaps the same query was called with a range of invoice numbers that was 50% of the entire table, in which case the optimizer chose a table scan. Then subsequent calls would use that same cached plan.
2) The numbers you use in your example appear to include roughly 10K rows assuming contiguous numbering. This should be about 4% of your table, in which case the optimizer 'should' choose index seek/bookmark lookup. Perhaps your statistics are stale? Try updating the stats and see if query improves.
3) I would also make a sproc and give it the WITH RECOMPILE option to ensure the best query plan is developed for each set of parameters.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Queries with criteria will use index if this index is clustered.
Even if you query for less than 1% of rows in table optimizer does not know that when it's compiling SP. That's why it uses more generic approach which will be more effective in most cases.
If your index is clustered it guarantees that all rows in you table are in order you need and there is no need to read all rows placed before @parameter if you query WHERE Value >= @parameter.
_____________
Code for TallyGenerator
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply