Rewrite query for better performance

Question

Rewrite query for better performance

EasyBoy

SSCertifiable

Points: 5301
More actions
December 16, 2015 at 10:37 am

#303300

We are supporting one client and i found through SQL Trace that they are running one query with different parameter through out the day. This query is taking about 15-20 seconds to execute every single time. Is there any way, i can optimize this query for better performance/
Thanks a lot for your help in advance.
/*
set statistics time on
set statistics io on
*/
SELECT sample.accn + sample.samplenumber ,
min(samples.eventdate) ,
Location.LocationCode ,
Location.LocationName ,
make.WorkstationID ,
make.makehine_Name ,pksample
FROM contain
INNER join containitems
on containitems.fkContainer= contain.pkcontainer
INNER join samples
on samples.pksample = ( SELECT max(pksample) as pkeventsample
FROM samples
WHERE fktype = 26 and fksample = containitems.parentpkvalue)
INNER JOIN make
on samples.fkWorkStationid = make.WorkstationID
INNER join Location
ON Location.LocationID = make.LocationID
INNER join sample
on sample.pksample = containitems.parentpkvalue
WHERE contain.ContainerNo = 'S0000825268' And contain.Active = 1 and containitems.active = 1
GROUP BY sample.accn_# + sample.samplenumber,Location.LocationCode,
Location.LocationName,make.WorkstationID,make.makehine_Name,pksample
Logical reads are for the samples table is about 915842. Rest of the tables seems to be small.
Couple of indexes on this table.
nonclustered fkSample, fkType
clustered, unique, primary key located on PRIMARYpkSample
Thanks a lot for your help in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

Can you post some DDL and the execution plan?

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Table definitions, index definitions and execution plan (as a .sqlplan file) please.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

EasyBoy SSCertifiable Points: 5301 More actions · Answer 3

It is SQL server 2000. So, i am not able to save the sqlplan.

Anyhow, i have captured the main part and attached for your consideration.

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Picture of the plan is useless, and you should have mentioned it's SQL 2000, as that removes a lot of options.

Please run the query with SHOWPLAN_ALL and put the plan's resultset in excel.

Can you post table def and index defs for the tblEventSample table? I didn't see the Sample table anywhere in the plan, not sure why that one's included.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

EasyBoy SSCertifiable Points: 5301 More actions · Answer 5

GilaMonster (12/16/2015)
Picture of the plan is useless, and you should have mentioned it's SQL 2000, as that removes a lot of options.
Please run the query with SHOWPLAN_ALL and put the plan's resultset in excel.
Can you post table def and index defs for the tblEventSample table? I didn't see the Sample table anywhere in the plan, not sure why that one's included.

Hello Gila,

Sorry for any confusion.

I have attached updated the query within SQLPlan and table structure.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

And does tblEventSample have any nonclustered indexes? If so, please post definitions of all of them.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

EasyBoy SSCertifiable Points: 5301 More actions · Answer 7

GilaMonster (12/16/2015)
And does tblEventSample have any nonclustered indexes? If so, please post definitions of all of them.

Only one Nonclustered index:

nonclustered : fkSample, fkEventType