Results 1 to 2 of 2

Thread: Indexes/Joins

  1. #1
    Bruce Monaghan Guest

    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

  2. #2
    rogerjh Guest

    Indexes/Joins (reply)


    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •