-
Indexes/Joins
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
-
Indexes/Joins (reply)
You could force the big table to use the non-clustered index by placing (INDEX=<index_name> after "big" 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 = '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
