Help Speeding up Query

[bbawkon]

Good day everyone,

Thank you all for taking the time to read my question.

I have a Database that I've inherited from a merger with another company. A legacy peice of software interfaces with this database - so I cannot change the schema at all.

The database consists of four tables.

Table(Board_Header)
UID bigint ID
WONUM char
ASSYNUM char
BARCODE char
SEQNUM char
EMPNUM char
STIME datetime
ETIME datetime

Table(Lot_Ref)
REF_ID bigint
LOT_UID bigint

Table(Lot_Data)
UID bigint ID
PRTNUM char
LOTNUM char
SERNUM char

Table(Board_Placements)
BOARD_UID bigint
REFDES char
LOT_REF_UID bigint


Now, Here is the query (Which is taking quite a while to run) that I need to execute against this data

SELECT dbo.Board_Header.WONUM, dbo.Board_Header.ASSYNUM, dbo.Board_Header.BARCODE, dbo.Board_Header.SEQNUM,
dbo.Board_Placements.REFDES, dbo.Lot_Data.PRTNUM, dbo.Lot_Data.LOTNUM, dbo.Lot_Data.SERNUM, dbo.Board_Header.EMPNUM,
dbo.Board_Header.STIME, dbo.Board_Header.ETIME
FROM dbo.Lot_Data INNER JOIN
dbo.Lot_Ref INNER JOIN
dbo.Board_Header INNER JOIN
dbo.Board_Placements ON dbo.Board_Header.UID = dbo.Board_Placements.BOARD_UID ON
dbo.Lot_Ref.REF_ID = dbo.Board_Placements.LOT_REF_UID ON dbo.Lot_Data.UID = dbo.Lot_Ref.LOT_UID
ORDER BY dbo.Board_Header.WONUM, dbo.Board_Header.ASSYNUM, dbo.Board_Header.BARCODE, dbo.Board_Header.SEQNUM, dbo.Lot_Data.PRTNUM,
dbo.Lot_Data.LOTNUM, dbo.Lot_Data.SERNUM



The Relations in the database are as follows:
Board_Header.UID -> Board_Placements.BOARD_UID
One to Many (One in Board_Header, Many in Board_Placements)

Board_Placements.LOT_REF_UID -> Lot_Ref.REF_ID
One to Many (One in Board_Placements, Many in Lot_Ref)

Lot_Ref.LOT_UID -> Lot_Data.UID
One to One


Can anyone reccomend any way to speed up this query? I can change the query, or I can do things like add index fields and such. Currently, This query is taking nearly 12 minutes to execute - totally unacceptable to the powers that be. Our database server is SQL 2000 with all service packs. The machine is a 4 processors (each dual core) Opteron with 16GB of Ram, so it's a fairly heavy lifter.


Any help, HIGHLY appreciated.
Ben

 

[memdiscount]

Hi;

Few questions before any reply would help us:

What is the size of the database?
How many records you are getting in 12 minutes?

Thanks

 

[SQLSister]

I would suggest indexes are the place to start as the code itself is pretty clean. Check the execution plan to see whther it is doig table scans or using indexes.

"NOTHING is more important in a database than integrity." ESquared

 

[gmmastros]

Why do you have all the ON clauses at the end? I have no idea if this will speed up the query, but I suggest you try this....

SELECT dbo.Board_Header.WONUM, dbo.Board_Header.ASSYNUM, dbo.Board_Header.BARCODE, 
       dbo.Board_Header.SEQNUM, dbo.Board_Placements.REFDES, dbo.Lot_Data.PRTNUM, 
       dbo.Lot_Data.LOTNUM, dbo.Lot_Data.SERNUM, dbo.Board_Header.EMPNUM,
       dbo.Board_Header.STIME, dbo.Board_Header.ETIME
FROM   dbo.Lot_Data 
       INNER JOIN dbo.Lot_Ref 
         ON dbo.Lot_Data.UID = dbo.Lot_Ref.LOT_UID
       INNER JOIN dbo.Board_Placements 
         ON dbo.Lot_Ref.REF_ID = dbo.Board_Placements.LOT_REF_UID 
       INNER JOIN dbo.Board_Header 
         ON dbo.Board_Header.UID = dbo.Board_Placements.BOARD_UID 
ORDER BY dbo.Board_Header.WONUM, dbo.Board_Header.ASSYNUM, 
         dbo.Board_Header.BARCODE, dbo.Board_Header.SEQNUM, 
         dbo.Lot_Data.PRTNUM, dbo.Lot_Data.LOTNUM, dbo.Lot_Data.SERNUM

Double/Triple check that the same data is returned. Also... please post the execution time of this query.

Also... do you really need to have all those columns in the ORDER BY clause. The less work done in the ORDER BY, the faster your query will be.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bbawkon]

Thank you everyone for your helpful thoughts.

I've modified the query to include fewer sorts, and added a few indexes to the tables in question.

The query has gone from 12 minutes to less than 40 seconds - primarily due to the indexes.

THANK YOU AGAIN,
Ben