Posted by Don Li on 11/25/07 02:22

Hi,

Env: MS SQL Server 2000

DB Info (sorry no DDL nor sample data):
tblA has 147249 rows -- clustered index on pk (one key of
datatype(int)) and
has two clumns, both are being used in joins;

intersecTbl4AB has 207016 rows -- clustered index on two fks and
this intersection table has six colums but only the two fks are being
used for joins;

tblB has 117597 rows -- clustered index on pk (one key of
datatype(bigint)) and
has four columns but only its key are being used for joins

A complex query involving the above the three tables includes inner
and outer joins, aggregate, sorting, predicate, math function, derived
table etc.
On the first run, the query takes about 4200ms to finish;
after some research on index optimization, I provided some index hint,
then the query runs at
about 3000ms. (That was yesterday).
Just now I realized that MS SQL Server 2000 is quite "intelligent", I
think it saves search terms
into cache because the second time search of the a same term is much
much faster. Now, a couple of questions:

a) if I construct a long long list of "common" terms and
programmatically let the sql server to cache them would it speed up
the overal query performance in my case? (Or it may depend on the
quality of the "common" terms?) and if your answer is yes (supposedly
you've been there, do you have to know where I could find such a
"common" term list, for everyday life or the general public?)

b) what other techniques out there to speed up the above described
query? Bring it down to 1000ms would be most desirable.

Thanks.

[Back to original message]