SQL Server Performance Forum – Threads Archive
how to force a query to use the index
I have a table with around 1 million rows. the table has an index for FIELDA. When I run the following query the execution plan shows me that it performs a full table scan.How do I force this query to use the index. SELECT [FIELDA], [FIELDB]
FROM [Table]
where FIELDA = ‘2006-5-24’
and FIELDB = ‘text’
SQL try to execute the best plan. So I don’t think you will have more performance forcing some index.
May be you have the wrong index. Try with one index on fielda, fieldb.
Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS†with no warranties for accuracy.
It will not allow me to create an index on both fields as fielda id a datetime field length 8 and fieldb is nvarchar 2048. I get an error on the size being too big if I put both fields in the index. How to I create a hint to force it to use the index.
Ok. SELECT COUNT(*) FROM SALES S (INDEX = IX1), CUSTOMER C, DATEDIM D HTH Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS†with no warranties for accuracy.
Does the table have an index with fieldA as the first column?
Have you updated statistics before running the query?
Probably the optimizer has a very good reason to choose the table scan. How selective is the value of ‘2006-5-24’? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
you are correct, when I tried forcing the index it actually took longer to run. It turned out in my testing environment a good portion of the rows had the date, 2006-5-24. When I ran the querey with a different date with less rows the query used the index. When I move the query to production where there 14 million rows and 6 months of data the query will know to use the index no mater which date is chosen. It does look like the optimizer knows best.
"It does look like the optimizer knows best" Indeed.
Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS†with no warranties for accuracy.
quote:Originally posted by LuisMartin
"It does look like the optimizer knows best"
Indeed.
Not allways, but it makes much less mistakes nowdays then what it is accused for.
]]>