Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
catdavis67
Starting Member
6 Posts |
 Posted - 2005-07-20 : 14:31:11
|
Hi all! I've got the following table which I'll call tZipStuff with the following data (reduced greatly since the actual table has about 1.2 million rows): OrigZipStart, OrigZipEnd, DestZipStart, DestZipEnd, SomeType, SomeVal 52001, 52999, 30001, 31999, A, 1 52001, 52999, 30001, 31999, B, 6 77001, 79999, 52001, 52999, A, 3 I've got a stored proc which runs the following query (where the variables listed are input parameters): SELECT SomeValue FROM tZipStuff WHERE @OrigZip BETWEEN OrigZipStart AND OrigZipEnd AND @DestZip BETWEEN DestZipStart AND DestZipEnd AND SomeType = @SomeType The "SomeType" column only has about 6 distinct values. Is there any good indexing I can do on this table to speed up the query? I'm not sure how effectively the compiler would use an index on say OrigZipStart since it's used in a BETWEEN operation. Thanks in advance for your help! Cat  |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-20 : 14:39:22
|
| Wild guess: Create an index with SomeType, OrigZipStart, OrigZipEnd, DestZipStart and DestZipEnd so that the WHERE clause is "covered". Maybe include SomeValue too IF that's the only thing in your SELECT statement (or you can make it into a correlated query so that it is)Possibly change the WHERE clause from@OrigZip BETWEEN OrigZipStart AND OrigZipEndto@OrigZip >= OrigZipStart AND @OrigZip <= OrigZipEndetc. (I doubt the optimiser will treat this differnetly, but it might!)Kristen |
 |
|
|
|
|
|
|
|
Need help on indexing