Yet another slow query - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Yet another slow query

Author

Topic

MontyMagic
Starting Member

12 Posts

Posted - 2011-09-20 : 08:52:57

Hi and thanks to SWEPESO, visakh16 and EHORN for all your help so far.

I have yet another querie that is grinding the application into the floor with time.

Could you cast your eyes over this one and give me any suggestions please. Many thanks in advance.

Monty

Query:

update SLX_DTS.dbo.con set
cdref = (select top 1 SLX_DTS.dbo.c_score.targetid from SLX_DTS.dbo.c_score where SLX_DTS.dbo.c_score.sourceid = SLX_DTS.dbo.con.recordid and SLX_DTS.dbo.c_score.scoretotal > 78 order by scoretotal desc)
where (select top 1 SLX_DTS.dbo.c_score.targetaccid from SLX_DTS.dbo.c_score where SLX_DTS.dbo.c_score.sourceid = SLX_DTS.dbo.con.recordid and SLX_DTS.dbo.c_score.scoretotal > 78 order by scoretotal desc) is not null

Every Day's a School Day

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-20 : 09:18:10

Hello MontyMagic,

Perhaps something like this?;

UPDATE c
SET c.cdref = d.targetid
FROM SLX_DTS.dbo.con c
INNER JOIN
(
	SELECT 
		MAX(s.scoretotal) AS scoretotal, 
		s.sourceid,
		s.targetid
	FROM SLX_DTS.dbo.con c 
	JOIN SLX_DTS.dbo.c_score s ON c.recordid = s.sourceid
	GROUP BY s.sourceid, s.targetid
	WHERE s.scoretotal > 78 AND s.targetaccid IS NOT NULL
) d ON d.sourceID = c.recordid

EDIT. I am not understanding the TOP 1(targetid) and TOP 1(targetaccid) for filtering. Could they be different if the ordering is the same (scoretotal) or is it simply an existence check? Perhaps the others fellows can provide a solution which does not rely on singleton but captures these requirements using sets.

I am sure it can be reworked, but I do not understand the data well enough from this one statement. Not knowing the data, I cannot determine how aggregates might result, but Top 1 needs to be replaced with an aggregate which will allow set based rather than singleton scan for each record.

If you could, would you provide some sample data for the 2 tables and an expected output? That would likely bring more solutions to the thread.

HTH.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-09-20 : 09:47:10

[code]UPDATE c
SET c.CdRef = f.TargetID
FROM SLX_DTS.dbo.Con AS c
CROSS APPLY (
SELECT TOP(1) w.TargetID
FROM SLX_DTS.dbo.c_score AS w
WHERE w.SourceID = c.RecordID
AND w.ScoreTotal > 78
ORDER BY w.ScoreTotal DESC
) AS f(TargetID)
WHERE f.TargetID IS NOT NULL[/code]

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

MontyMagic
Starting Member

12 Posts

Posted - 2011-09-20 : 10:50:26

Thanks Guys.

Just waiting for the server to get to the point where I can test both of these solutions. I really appreciate the help, this is making a world of difference..:)

Monty

Every Day's a School Day

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-20 : 10:52:56

quote:

Originally posted by SwePeso

UPDATE		c
SET		c.CdRef = f.TargetID
FROM		SLX_DTS.dbo.Con AS c
CROSS APPLY	(
			SELECT TOP(1)	w.TargetID
			FROM		SLX_DTS.dbo.c_score AS w
			WHERE		w.SourceID = c.RecordID
					AND w.ScoreTotal > 78
			ORDER BY	w.ScoreTotal DESC
		) AS f(TargetID)
WHERE		f.TargetID IS NOT NULL

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

That is elegant and should perform much better than the original query.

Thanks for posting.

Best wishes to you and OP.

MontyMagic
Starting Member

12 Posts

Posted - 2011-09-20 : 10:54:49

Thanks for the support eHORN.

I am about to post yet another of my headaches as a new post for your analysis.... Please could you take a look if you get a chance.

and once again. thanks.

Every Day's a School Day

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources