October 22, 2009 at 3:54 pm
Dear friends
I need some help with the below Query.. It is taking about 12 secs to bring up the result which is about 90 rows.
SELECT vs.assetid,
CASE
WHEN len(vs.eventDescription) > 15
THEN LEFT(vs.eventDescription, 15) + '...'
ELSE
vs.eventDescription
END,
sum(vs.interval)/60 dt
FROMvstoppagesandslowrunningbyshift vs, vasset va, cellgroupcell cgc
WHERE va.cellassetid = cgc.assetid
ANDvs.shiftauditid = va.cellcurshiftauditid
ANDcgc.cellgroupid = ?cellgroupid
ANDvs.status = 0
ANDvs.planneddowntime = 0
GROUP BY
vs.assetid, vs.eventdescription
UNION
SELECT vps.assetid,
CASE
WHEN len(vps.eventDescription) > 15
THEN LEFT(vps.eventDescription, 15) + '...'
ELSE
vps.eventDescription
END,
sum(vps.interval)/60 dt
FROMvprocessstoppagesandslowrunningbyshift vps, vasset va, cellgroupcell cgc
WHERE va.cellassetid = cgc.assetid
ANDvps.shiftauditid = va.cellcurshiftauditid
ANDcgc.cellgroupid = ?cellgroupid
ANDvps.status = 0
ANDvps.planneddowntime = 0
GROUP BY
vps.assetid, vps.eventdescription
ORDER BY
assetid, dt desc
Many thanks in advance.
October 22, 2009 at 4:08 pm
Please post a query plan as detailed in the link in my sig below.
October 22, 2009 at 8:28 pm
Also - how much data is in the source table(s)? the 90 records are summary data, so you could be pulling out 90 records, made up of 1000's of detail rows from a population of 100,000's.
What's the detail of the view? Why the UNION statement?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 22, 2009 at 8:37 pm
As, mentioned earlier by others, what is the Query Execution Plan displaying ???
and also how many Records exist in the Base tables and do you have Index on the columns that you are using in Where Clause, Group By and Order By.
Without those info its really hard to identify the problem.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
October 23, 2009 at 6:45 am
There's nothing immediately obvious in the query that would suggest a performance bottleneck. The hard coded values of "0" do beg the question, how selective is that data, are you getting index seeks, etc. You have to post the execution plan. Also putting up the table structure, especially the indexes, will help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 23, 2009 at 1:32 pm
PLease see the attchments:
Downtime.sqlplan and the structure of the view.
October 23, 2009 at 3:54 pm
Please also see attached vasset and cellgroupcell table structure in two worksheets. Below is the view vasset.
vasset:
SELECT assetId AS cellAssetId, assetName AS assetId, assetName, sequence, standardRate, countUnitId, targetShiftOEE,
CASE WHEN a.assettype = 'ProductionCell' THEN 'CELL' WHEN a.assettype = 'ProcessCell' THEN 'CELL' WHEN a.assettype = 'ProductionUnit' THEN 'PLANT'
WHEN a.assettype = 'ProcessUnit' THEN 'PLANT' ELSE NULL END AS assettypeid,
(SELECT TOP (1) availability
FROM dbo.availabilityHistory AS h
WHERE (vFrom <= dbo.ee_getDate()) AND (vTo > dbo.ee_getDate()) AND (assetId = a.assetId)
ORDER BY vFrom DESC) AS curavailability,
(SELECT TOP (1) status
FROM dbo.statusHistory AS h
WHERE (vFrom <= dbo.ee_getDate()) AND (vTo > dbo.ee_getDate()) AND (assetId = a.assetId)
ORDER BY vFrom DESC) AS curstatus,
(SELECT TOP (1) opMode
FROM dbo.opModeHistory AS h
WHERE (vFrom <= dbo.ee_getDate()) AND (vTo > dbo.ee_getDate()) AND (assetId = a.assetId)
ORDER BY vFrom DESC) AS curopmode,
(SELECT TOP (1) shiftId
FROM dbo.shiftHistory AS h
WHERE (vFrom <= dbo.ee_getDate()) AND (vTo > dbo.ee_getDate()) AND (assetId = a.assetId)
ORDER BY vFrom DESC) AS cellcurshiftid,
(SELECT TOP (1) shiftAuditId
FROM dbo.shiftHistory AS h
WHERE (vFrom <= dbo.ee_getDate()) AND (vTo > dbo.ee_getDate()) AND (assetId = a.assetId)
ORDER BY vFrom DESC) AS cellcurshiftauditid,
(SELECT TOP (1) runId
FROM dbo.productionRunHistory AS h
WHERE (vFrom <= dbo.ee_getDate()) AND (vTo > dbo.ee_getDate()) AND (assetId = a.assetId) AND (status IN ('RUNNING', 'CYCLING'))
ORDER BY vFrom DESC) AS cellcurrunid,
(SELECT TOP (1) crewId
FROM dbo.crewHistory AS h
WHERE (vFrom <= dbo.ee_getDate()) AND (vTo > dbo.ee_getDate()) AND (assetId = a.assetId)
ORDER BY vFrom DESC) AS cellcurcrewid
FROM dbo.asset AS a
October 23, 2009 at 6:14 pm
Oh gosh, you're joining and unioning on views. That's potentially the cause of the problem right there.
Looking at the execution plan you've got about 12 different scans, several key lookups and a lot of merge joins. These are indications of missing indexes and/or bad code. You've also got a very wide variance between your estimated and actual rows. This indicates out of date statistics.
There's no one thing to point to. You need to start from scratch and carefully rebuild this, testing as you go.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 24, 2009 at 1:57 am
Thank you Grant for your response.
I did run sp_updatestats to update the statistics. Do i have to update the statistics again. As far as missing index is concerned how did you come to that conclusion. Does Merge Join means that there's missing indexes.
Thank you in advance.
October 25, 2009 at 12:17 pm
sarvesh singh-457805 (10/24/2009)
Thank you Grant for your response.I did run sp_updatestats to update the statistics. Do i have to update the statistics again. As far as missing index is concerned how did you come to that conclusion. Does Merge Join means that there's missing indexes.
Thank you in advance.
A merge join is usually indicative of indexes missing that would help the query to perform better, yes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 25, 2009 at 1:50 pm
Hi Grant
looking at the execution plan whichs tables do you think needs indexes.
October 25, 2009 at 2:40 pm
sarvesh singh-457805 (10/25/2009)
Hi Grantlooking at the execution plan whichs tables do you think needs indexes.
For an execution plan that large, a complete break-down and set of recommendations would become billable hours.
However, a few places you need to focus on are the eventLog table and it's relationship with the statusHistory table. These two are moving the most data, 793k and 175k roe respectively. The ind_ShiftAudit table is feeding into an index spool operation, which is creating a temporary index into 2.3 milllion rows and then filtering these rows down to 175k which later get joined into the others.
That's the first place I'd spend time working. The fact that there's nothing filtering on those indexes or the code is preventing indexes getting used on there... that's where I'd start.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 26, 2009 at 7:20 am
There is a lot of work to be done here, and not just on indexing. I agree with Grant - you need to get a professional engaged on tuning this mess. A good tuner could not only refactor this but also help mentor you on how to go about tuning stuff like this yourself in the future. Also, I think Gail Shaw has some good posts on her blog (http://sqlinthewild.co.za/) or perhaps here on sqlservercentral.com on tuning methodologies if you really want to tackle this yourself.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 27, 2009 at 7:58 am
Thank you guys for the replies. I have temporarily fixed it by doing another join to the eventlog table.It takes 4 seconds now. Thank you grant for pointing me to the right direction.
It is a mess which i will get a professional to look at and hopefully pick up some tips on performance tuning.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply