SQL Server does not use Index without a hint

I have 2 questions....

1) in the query below, the index is not used unless the WITH clause is supplied. How can I get the query to use that index automatically?  There are 3 indexes; one for each of the columns (From_Column, To_Column) and an index with both columns

2) In its present state, the query still takes too long to run (sometimes 1 sec).  I have a list of numbers (up to 200 rows) and I need to call this query for each number in the list.  This could take up to 3 minutes to run.  What changes (process, query, indexes) need to be made to get this process to run quickly?

declare @inNumbigint
@inNum = 23568978
select *
from Table01 WITH (INDEX = Table01_Index01)
where
      @inNum>= From_Column AND
      @inNum<= To_Column


Thanks.
hexvaderAsked:
Who is Participating?
 
Chris MangusConnect With a Mentor Database AdministratorCommented:
I'd probably change the Numeric(11,0) to an int column, since you're not using any values to the right of the decimal point.  That's 4 bytes of storage vs. 8 bytes which may speed the query.  

Yes, a covered query would be one where you're index covers the From_Column, To_Column, and your PK.  The covered query only needs to do the data lookup in the index and never even has to go to the actual data.  This is very fast.

How granular is the from and to data?  Do you have many unique values or many repeating values?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
is the index on From_Column + To_Column a clustered index? (at least that is what I would try first...)
what data types are the fields From_Column and To_Column?
0
 
Chris MangusDatabase AdministratorCommented:
How granular is the data in the from and to columns?

Also, obviously, don't do SELECT *, only return the columns you really need.

You may want to explore a "covered" query.  This is a query where all of the columns returned are part of a single index.  That is as fast as you can get.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
hexvaderAuthor Commented:
I get the same results if I specifically name the columns to return, so that is part of my problem.   The From and To columns are numeric(11,0).  There is a clustered index, but it is on the primary key, which is an identity column.

So, for a "covered" query, I just create an index for all the columns?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>The From and To columns are numeric(11,0).

then change the variable declaration:

declare @inNum numeric(11,0)
set @inNum = 23568978
select *
from Table01 WITH (INDEX = Table01_Index01)
where
      @inNum>= From_Column AND
      @inNum<= To_Column
0
 
Scott PletcherSenior DBACommented:
To me, your best hope is:
1) create a separate index on (from_column, to_column)
2) re-work the WHERE clauses so that the columns appear on the left-hand side:
where
      From_Column <= @inNum AND To_Column >= @inNum

However, this is still no guarantee, esp. if the query is in a stored proc.  Stored procs are compiled ahead of time, so SQL does not know the value for @inNum, so must use a default %-of-rows assumption.


>> I have a list of numbers (up to 200 rows) and I need to call this query for each number in the list. <<

In that situation, definitely insert the values into a temp table / temp table variable **with a pk specified** and do an INNER JOIN of that table to the data table.
0
 
hexvaderAuthor Commented:
Changing the data type of the columns did not change query times.  I did change the columns to bigint since that is the real data type.  Using a "covered" query and a clustered index on the PK reduced query times from my whole data set from over 2 min to less than 15 sec - very cool.

I have not looked into using temp tables, but by rule I avoid them if possible. In this case, I will give it a try and review the differences.

Thanks for all the input.
0
 
Chris MangusDatabase AdministratorCommented:
Kimberly Tripp has written extensively on covered queries.  They are very cool in that they are so fast.  Search for her...
0
 
hexvaderAuthor Commented:
I looked at using a temp table, but in this case it will not work.  The data I get is a long int, which will fall in the range of the FROM and TO columns. The data I would put in a temp table will not properly join with my data table.

Thanks again for the help.
0
 
hexvaderAuthor Commented:
Wanted to make a final follow up the this issue....

I imported my incoming data to a temp table then joined it.  During my tests, the total time to run this data (up to 200 rows) was 23 sec.  I then skipped the import to a temp table and the total time went down to 15 sec.  This was done with a covered query.
0
All Courses

From novice to tech pro — start learning today.