Procedure taking a long time to run...

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

General SQL Server Forums

New to SQL Server Programming

Procedure taking a long time to run...

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-08-24 : 09:54:08

When I added the join to the bottom of this procedure it takes a while to run. Is there a way to get it to run faster? Do I have the join set up in the wrong spot?


SELECT A.FO, n.region_ltr as reg, n.regionacronym as regname, n.area, A.MMPending, A.RecPending, B.MMPendingLastWk, B.RecPendingLastWk, ABS(A.MMPending-B.MMPendingLastWk) AS MMPendingCleared, ABS(A.RecPending- B.RecPendingLastWk) AS RecPendingCleared

FROM
 
(select FO, count(claim) as MMPending, count(distinct claim) as RecPending

from CurrentWeek
group by fo) A
 
INNER JOIN
(select FO, count(claim) as MMPendingLastWk, count(distinct claim) as RecPendingLastWk

from Lastweek
group by fo) B
 
ON A.FO = B.FO
join docfile n on n.doc=a.fo
order by reg

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-24 : 10:01:10

what are indexes available on docfile? whats the purpose of that join?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-24 : 10:09:35

Cleaned up sql a litte (formatting)


SELECT
	A.FO
	, n.region_ltr as reg
	, n.regionacronym as regname
	, n.area
	, A.MMPending
	, A.RecPending
	, B.MMPendingLastWk
	, B.RecPendingLastWk
	, ABS(A.MMPending-B.MMPendingLastWk) AS MMPendingCleared
	, ABS(A.RecPending- B.RecPendingLastWk) AS RecPendingCleared
FROM

	(
		select
			FO
			, count(claim) as MMPending
			, count(distinct claim) as RecPending
		from
			CurrentWeek
		group by	
			fo
		)
		A
 
	INNER JOIN (
		select FO
			, count(claim) as MMPendingLastWk
			, count(distinct claim) as RecPendingLastWk
		from Lastweek
		group by fo
		)
		B ON A.FO = B.FO

	-- The join causing the problem????
	join docfile n on n.doc = a.fo

order by
	reg

Usual questions:

Visakh covered the index question and why you are joining

Anything obvious in the actual query plan generated? (Cntrl + M and rerun in MSMS)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-08-24 : 10:22:31

Thanks!

I'm joining because I want to use three columns from the docfile to use on the front end.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-08-24 : 10:51:04

I decided to put the results into a table so it's fine it's running so slowly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources