|
|
Posted by Don Li on 11/26/07 02:21
Thank you, Gert-Jan and Erland, I did the following for
>make
> sure this table has nonclustered indexes for both column orders.
for my intersecTbl4AB. Also, I found the data type mismatch for a
column, which was odd... fixed.
Now, the query runs like a rocket :)
Once again I greatly appreciate it, you both are the men of MS SQL
Server xxxx :)
Don
>On Nov 25, 7:51 am, Gert-Jan Strik <so...@toomuchspamalready.nl> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -
Navigation:
[Reply to this message]
|