Appropriate Query Optimization Technique(s) Sought for the Following Case

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.






.

Relevant Pages

  • Re: SELECT Optimalization
    ... This query would always require an index scan (at least up to and ... clause is a good solution. ... and execution plan is as simple as posible (100% clustered index scan) ... Are you sure the bottleneck is in SQL Server and not on the client? ...
    (comp.databases.ms-sqlserver)
  • Re: Clustered Index question
    ... Columnist, SQL Server Professional ... Without having primary key in the table, will it slow down the query that I ... the clustered index on PACKET_TIME, and instead have a clustered index on ...
    (microsoft.public.sqlserver.programming)
  • Re: Whats the deal with PAGEIOLATCH_SH? - PAGEIOLATCHIssue20040426.zip (0/1)
    ... internal iterative steps in the query plan probably require individual ... > This table used to have very poorly chosen clustered index, ... SQL Server is certainly capable of handling ...
    (microsoft.public.sqlserver.programming)
  • Re: IS NULL on field is not using index placed on that field
    ... > criteria in your WHERE clause the query engine needs to look through 2 ... > NULL" you get a nice quick plan using your nonclustered index too. ... > efficiently by just scanning the whole clustered index until it's got ... > even took out the null values and made the DeliverDate column NOT NULL ...
    (microsoft.public.sqlserver.programming)
  • Re: Whats the deal with PAGEIOLATCH_SH? - PAGEIOLATCHIssue20040426.zip (0/1)
    ... That table has no primary key and no clustered index. ... This table used to have very poorly chosen clustered index, ... SQL Server is certainly capable of handling ... >being missing) is messing up the main query. ...
    (microsoft.public.sqlserver.programming)