really slow SP - 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 2000 Forums

Transact-SQL (2000)

really slow SP

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-08-05 : 04:49:58

I have a stored proc that I am running that runs fine. I made a 2nd stored proc that is just slightly modified and it runs hundreds of times slower. I'm not exactly sure why, is this a bad way of writing my stored proc?

This proc returns instantly

CREATE PROCEDURE select_topReferers_cache

AS SET NOCOUNT ON

SELECT TOP 1000 nameOnline, tblUserDetails.userID, age, statusID, date, (select COUNT(tblReferalLog.userid) as tblReferalCount FROM tblReferalLog WHERE tblUserDetails.userid = tblReferalLog.userid) AS ReferredCount, (select count(userID) as tblthumb_count from tblthumbs where status = 1 AND tblUserDetails.userid = tblthumbs.userid) as thumb_count,

case when exists (select userID from tblZeo where tblUserDetails.userid = tblZeo.userid AND active ='1' ) then
'Y'
else
'N'
end as ZeoExist,
case when exists (select userID from tblThumbs where tblUserDetails.userid = tblThumbs.userid AND active ='1' AND thumbID ='99' ) then
'Y'
else
'N'
end as four
FROM tblUserDetails ORDER BY referredCount desc

GO

and this proc below takes well over 1 minute to run

CREATE PROCEDURE select_referers_month
(
@referDate datetime
)
AS SET NOCOUNT ON

SELECT TOP 1000 nameOnline, tblUserDetails.userID, age, statusID, date, (select COUNT(tblReferalLog.userid) as tblReferalCount FROM tblReferalLog WHERE tblUserDetails.userid = tblReferalLog.userid AND referalDate BETWEEN dateadd(mm,datediff(mm,0,@referDate),0) AND dateadd(mm,datediff(mm,0,@referDate)+1,0)) AS ReferredCount, (select count(userID) as tblthumb_count from tblthumbs where status = 1 AND tblUserDetails.userid = tblthumbs.userid) as thumb_count,

case when exists (select userID from tblZeo where tblUserDetails.userid = tblZeo.userid AND active ='1' ) then
'Y'
else
'N'
end as ZeoExist,
case when exists (select userID from tblThumbs where tblUserDetails.userid = tblThumbs.userid AND active ='1' AND thumbID ='99' ) then
'Y'
else
'N'
end as extra

FROM tblUserDetails ORDER BY referredCount desc

GO

any help on this is greatly appreciated.

Thanks
Mike123

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-08-05 : 07:13:31

can you evaluate the "BETWEEN dateadd(mm,datediff(mm,0,@referDate),0) AND dateadd(mm,datediff(mm,0,@referDate)+1,0))" dates into 2 seperate variables beforehand?....given that @referDate is passed into the SP, it doesn't have a different value per record read. this may help the query processor to (re)use any indices used in the 1st query.

also...if would be useful for you (+ us) to have a look at (and include here) the query plans for both queries. this would highlight where the major performance difference is occurring.

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2003-08-05 : 08:20:59

Instead on using sub queries try using this, even after that if ur stored procedure is running slow then probably u have to check u execution plan, so that u can take necessary measures to improve them.

CREATE PROCEDURE select_referers_month
(
@referDate datetime
)
AS SET NOCOUNT ON

SELECT TOP 1000 nameOnline,
tblUserDetails.userID,
age, statusID,
date,
tblReferalCountTbl.ReferredCount,

ThumbTbl.thumb_count,

case when exists (select userID from tblZeo where tblUserDetails.userid = tblZeo.userid AND active ='1' ) then
'Y'
else
'N'
end as ZeoExist,
case when exists (select userID from tblThumbs where tblUserDetails.userid = tblThumbs.userid AND active ='1' AND thumbID ='99' ) then
'Y'
else
'N'
end as extra

FROM tblUserDetails
left outer join (
select userID, count(userID) as tblthumb_count
from tblthumbs A
where A.status = 1
group by A.userID )
as ThumbTbl
on tblUserDetails.userid = ThumbTbl.userid
left outer join (
select userid,COUNT(tblReferalLog.userid) as tblReferalCount
FROM tblReferalLog
WHERE
referalDate BETWEEN dateadd(mm,datediff(mm,0,@referDate),0)
AND dateadd(mm,datediff(mm,0,@referDate)+1,0)
Group By userid
) as tblReferalCountTbl
on tblUserDetails.userid = tblReferalCountTbl.userid
ORDER BY referredCount desc

V.Ganesh
Net assetmanagement.com
vganesh76@rediffmail.com

Enjoy working

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-08-05 : 18:23:37

thanks vganesh76

I'd really like to try your query to see how it runs, but cant seem to fix the following errors:

Server: Msg 207, Level 16, State 3, Procedure select_referers_month, Line 7
Invalid column name 'ReferredCount'.
Server: Msg 207, Level 16, State 1, Procedure select_referers_month, Line 7
Invalid column name 'thumb_count'.
Server: Msg 207, Level 16, State 1, Procedure select_referers_month, Line 7
Invalid column name 'referredCount'.

Any ideas?

Thanks alot

Mike123

edit: I'll try to post the execution plan asap as well thx

shsmonteiro
Constraint Violating Yak Guru

290 Posts

Posted - 2003-08-05 : 23:43:24

Mike,

Use this version of vganesh76 proc. It should work:

CREATE PROCEDURE select_referers_month
(
@referDate datetime
)
AS SET NOCOUNT ON

SELECT TOP 1000 nameOnline,
tblUserDetails.userID,
age, statusID,
date,
tblReferalCountTbl.tblReferalCount,
ThumbTbl.tblthumb_count,
case when exists (select userID from tblZeo where tblUserDetails.userid = tblZeo.userid AND active ='1' ) then
'Y'
else
'N'
end as ZeoExist,
case when exists (select userID from tblThumbs where tblUserDetails.userid = tblThumbs.userid AND active ='1' AND thumbID ='99' ) then
'Y'
else
'N'
end as extra
FROM tblUserDetails
left outer join (
select userID, count(userID) as tblthumb_count
from tblthumbs A
where A.status = 1
group by A.userID )
as ThumbTbl
on tblUserDetails.userid = ThumbTbl.userid
left outer join (
select userid,COUNT(tblReferalLog.userid) as tblReferalCount
FROM tblReferalLog
WHERE
referalDate BETWEEN dateadd(mm,datediff(mm,0,@referDate),0)
AND dateadd(mm,datediff(mm,0,@referDate)+1,0)
Group By userid
) as tblReferalCountTbl
on tblUserDetails.userid = tblReferalCountTbl.userid
ORDER BY tblReferalCountTbl.tblReferalCount desc

Regards,

Sérgio Monteiro
Trust in no Oracle

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2003-08-06 : 00:46:01

Sorry Mike, I had some typing flaws, here is the corrected one,I think even Sérgio Monteiro has corrected the code for me?
Any way thanx Sérgio Monteiro.

CREATE PROCEDURE select_referers_month
(
@referDate datetime
)
AS SET NOCOUNT ON

SELECT TOP 1000 nameOnline,
tblUserDetails.userID,
age, statusID,
date,
tblReferalCountTbl.ReferredCount,

ThumbTbl.thumb_count,

case when exists (select userID from tblZeo where tblUserDetails.userid = tblZeo.userid AND active ='1' ) then
'Y'
else
'N'
end as ZeoExist,
case when exists (select userID from tblThumbs where tblUserDetails.userid = tblThumbs.userid AND active ='1' AND thumbID ='99' ) then
'Y'
else
'N'
end as extra

FROM tblUserDetails
left outer join (
select userID, count(userID) as thumb_count
from tblthumbs A
where A.status = 1
group by A.userID )
as ThumbTbl
on tblUserDetails.userid = ThumbTbl.userid
left outer join (
select userid,COUNT(tblReferalLog.userid) as ReferalCount
FROM tblReferalLog
WHERE
referalDate BETWEEN dateadd(mm,datediff(mm,0,@referDate),0)
AND dateadd(mm,datediff(mm,0,@referDate)+1,0)
Group By userid
) as tblReferalCountTbl
on tblUserDetails.userid = tblReferalCountTbl.userid
ORDER BY tblReferalLog.referredCount desc

Enjoy working

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources