Indexes/Joins

[Bruce Monaghan]

We have an large table (4 million rows +) that we query frequnetly

most queries run fine using a non-clustered index on 5 columns (call them a,b,c,d,e !!) - a is a datetime field that cuts down most of the rows

however we have one query that joins to a second mush smaller table to get the values for c,d,e

ie SELECT * from big_table big INNER JOIN little_table little
ON big.c = little.c and big.d = little.d and big.e = little.e
WHERE big.a = 'date' and little.f = 'value'

Now the problem is that it only uses the index to select on date(a) before filtering in the c,d,e restraints - execution time is in the 45 second region as opposed to 1 second where we specify the values of c,d,e in the query explicitly

Can I force the query to calculate the possible values of c,d,e first and then use this to retrieve the rows required - I can only think of building up the values using a cursor first and then passing this into the statement, whoch seems very clunky

[rogerjh]

You could force the big table to use the non-clustered index by placing (INDEX=<index_name> after &#34;big&#34; in your query. Not sure if this will solve the problem, but it should assist in troubleshooting.

------------
Bruce Monaghan at 2/11/2002 9:29:48 AM


We have an large table (4 million rows +) that we query frequnetly

most queries run fine using a non-clustered index on 5 columns (call them a,b,c,d,e !!) - a is a datetime field that cuts down most of the rows

however we have one query that joins to a second mush smaller table to get the values for c,d,e

ie SELECT * from big_table big INNER JOIN little_table little
ON big.c = little.c and big.d = little.d and big.e = little.e
WHERE big.a = &#39;date&#39; and little.f = &#39;value&#39;

Now the problem is that it only uses the index to select on date(a) before filtering in the c,d,e restraints - execution time is in the 45 second region as opposed to 1 second where we specify the values of c,d,e in the query explicitly

Can I force the query to calculate the possible values of c,d,e first and then use this to retrieve the rows required - I can only think of building up the values using a cursor first and then passing this into the statement, whoch seems very clunky