Please help tune the query:: urgent

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Please help tune the query:: urgent

Author

Topic

companionz
Yak Posting Veteran

54 Posts

Posted - 2009-04-14 : 05:43:46

Guys!

I've a query which takes too much time to execute.
Can anyone pls help me tune the same asap::

Below is the query::

WITH Members AS
(
SELECT
(select Max(EventData.UsageMeter)
From EventHeader
Left Join EventData on EventData.EventUid=EventHeader.Uid
Where EventHeader.EventType=0
And EventHeader.EventTime <= '03/30/2008 06:00:00'
And EventHeader.AssetUid = AssetInfo.AssetUid
) as beginUsage
,
(select Max(EventData.UsageMeter)
From EventHeader
Left Join EventData on EventData.EventUid=EventHeader.Uid
Where EventHeader.EventType=0
And EventHeader.EventTime <= '04/14/2009 05:59:59'
And EventHeader.AssetUid = AssetInfo.AssetUid
) as endUsage
,AssetInfo.Id
,AssetInfo.AssetUid
,(select errorstatus from asset where uid = assetinfo.assetuid) as errorStatus
,ROW_NUMBER() OVER (Order By assetInfo.Id ASC) AS RowNumber
FROM
assetInfo
WHERE
(AssetInfo.CompanyUid in (150000108)) AND id is not null
)
SELECT *
FROM Members
--WHERE RowNumber BETWEEN 1 AND 500
ORDER BY RowNumber

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-14 : 05:57:21

[code];WITH Members
AS (
SELECT u.umBegin AS beginUsage,
u.umEnd AS endUsage,
ai.ID,
ai.AssetUID,
a.ErrorStatus,
ROW_NUMBER() OVER (ORDER BY ai.ID) AS RowNumber
FROM AssetInfo AS ai
LEFT JOIN Asset AS a ON a.UID = ai.assetUID
LEFT JOIN (
SELECT eh.AssetUID,
MAX(CASE WHEN eh.EventTime <= '03/30/2009 06:00:00' THEN ed.UsageMeter ELSE NULL END) AS umBegin,
MAX(CASE WHEN eh.EventTime < '04/14/2009 06:00:00' THEN ed.UsageMeter ELSE NULL END) AS umEnd
FROM EventHeader AS eh
LEFT JOIN [EventData] AS ed ON ed.EventUID = eh.UID
WHERE eh.EventType = 0
AND eh.EventTime < '04/14/2009 06:00:00'
GROUP BY eh.AssetUID
) AS u ON u.AssetUID = ai.AssetUID
WHERE ai.CompanyUID = 150000108
AND ai.ID IS NOT NULL
)

SELECT beginUsage,
endUsage,
ID,
AssetUID,
RowNumber
FROM Members
WHERE RowNumber BETWEEN 1 AND 500
ORDER BY RowNumber[/code]Please post back the time it took before, and the time it takes with my suggestion.

E 12°55'05.63"
N 56°04'39.26"

companionz
Yak Posting Veteran

54 Posts

Posted - 2009-04-14 : 06:45:34

The execution time increased from 20 min of old query to 24 min for the new query... :-(

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-14 : 06:54:44

Run the query again.
Depending on your data, you may want to substitute the LEFT JOIN to INNER JOIN.

E 12°55'05.63"
N 56°04'39.26"

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2009-04-14 : 09:18:27

what are the indexes on Asset, AssetInfo, EventHeader, EventData

EDIT: also might post the query plan. look for scans on large tables - they will hurt you.

elsasoft.org

companionz
Yak Posting Veteran

54 Posts

Posted - 2009-04-14 : 11:27:52

Indexes :
Asset:: PRimary key :: uid
Assetinfo :: PRIMARY KEY (clustered):: AssetUid, CompanyUid
EventHeader:: PRimary key :: uid
EventData :: Primary key:: EventUid

I've broken the query into two and now want to merge the output

SELECT

AssetInfo.Id
,AssetInfo.AssetUid
,(select errorstatus from asset where uid = assetinfo.assetuid) as errorStatus
,ROW_NUMBER() OVER (Order By assetInfo.Id ASC) AS RowNumber
FROM
assetInfo
WHERE
(AssetInfo.CompanyUid in (150000108)) AND id is not null

SELECT MAX(CASE WHEN eh.EventTime <= '03/30/2008 06:00:00' THEN ed.UsageMeter ELSE NULL END) AS umBegin,
MAX(CASE WHEN eh.EventTime < '04/14/2009 06:00:00' THEN ed.UsageMeter ELSE NULL END) AS umEnd
from eventheader eh left join eventdata ed
on ed.eventuid = eh.uid
where eh.eventtype = 0
and eh.assetuid = 150020118

group by assetuid

companionz
Yak Posting Veteran

54 Posts

Posted - 2009-04-15 : 05:34:18

Guys, i finally modified my query :::

WITH Members
AS (
SELECT u.umBegin AS beginUsage,
u.umEnd AS endUsage,
ai.ID,
ai.AssetUID,
(select errorstatus from asset where uid = ai.assetuid) as errorStatus
,ROW_NUMBER() OVER (Order By ai.Id ASC) AS RowNumber
FROM AssetInfo AS ai
--LEFT JOIN Asset AS a ON a.UID = ai.assetUID
LEFT JOIN (
SELECT assetuid, MAX(CASE WHEN eh.EventTime <= '03/30/2008 06:00:00' THEN ed.UsageMeter ELSE NULL END) AS umBegin,
MAX(CASE WHEN eh.EventTime < '04/14/2009 06:00:00' THEN ed.UsageMeter ELSE NULL END) AS umEnd
from eventheader eh left join eventdata ed
on ed.eventuid = eh.uid
where eh.eventtype = 0

group by assetuid
) AS u ON u.AssetUID = ai.AssetUID
WHERE ai.CompanyUID = 150000108
AND ai.ID IS NOT NULL
)

SELECT *
FROM Members
ORDER BY RowNumber

But, still its taking 17 min compared to 23 from old query.
Still need some expertise on this.

Thanks,
sourav

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-15 : 05:36:15

You have no other indexes than the primary keys?

E 12°55'05.63"
N 56°04'39.26"

companionz
Yak Posting Veteran

54 Posts

Posted - 2009-04-15 : 06:06:02

Couldn't fine more indexes.. only some foreign keys apart from the ones i mentioned earlier.

Let me know if u need the extimated execution plan for any reference.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources