Appropriate Query Optimization Technique(s) Sought for the FollowingCase

[Don Li]

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.

[Erland Sommarskog]

Don Li (ta********@gmail.com) writes:

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:

SQL Server caches:

1) Execution plans.
2) Data.

It does not cache invidiual search terms if that is what you have in
mind. If you submit one query, and then tweak that just a little bit,
you will have no benefit of that the plan for the first query is in
cache.

However, you will have benefit of that first query dragged data into
the cache. And this is something you need to watch out, as it may
distort your measurement of execution time. If you need to press the
execution time to 1000 ms, you must first determine whether that is
1000 ms with the data in cache, or with the data on disk.

If you think it's safe to assume that the table will almost always be
in cache run the same query several times, to make sure that nothing is
read from disk, and discard the first measurement.

If you what to measure worst case, issue DBCC DROPCLEANBUFFERS between
each run to flush the cache. (But don't do this on a production server!)

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

Without knowledge about the tables and the query I cannot but decline.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx