|
|
Posted by Gert-Jan Strik on 11/25/07 12:51
Don,
For performance questions, the database structure is very important.
With the information you provided, it is hard to say if all relevant
information is posted.
So I can only post a few generic recommentations here:
- make sure you have a properly defined foreign key between
intersecTbl4AB and tblB. If no columns are selected from tblB, and no
filtering is done based on tblB, then this foreign key constraint can
eliminate this table's access for this query
- if only the primary key columns are used in intersecTbl4AB, then make
sure this table has nonclustered indexes for both column orders. IOW,
try adding an index on intersecTbl4AB(fkB, fkA). Also, add a
nonclustered index on top of the clustered index. IOW, try adding a
nonclustered index on intersecTbl4AB(fkA, fkB). You can later drop the
indexes that the query does not use (check the query plan for that)
- avoid expressions in the join predicates, and use only columns
(whenever possible) without calculations. Make sure the columns are of
the same data type (and length)
- avoid scalar UDFs
HTH,
Gert-Jan
Don Li wrote:
>
> 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.
Navigation:
[Reply to this message]
|