very heavy read query slowing down system

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

SQL Server Development (2000)

very heavy read query slowing down system

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-08-29 : 03:37:30

Hi,

I have an event that runs every morning that when fully enabled, basically puts my sqlserver to a halt because of all the processing involved. I've had to disable the extended functionality.

The query brings back about 2000 rows, with bunch of values in them. I want to add on a sub query on each row, which would be about 2000 extra queries, which totally kills the server.

I don't care how long it takes to execute as it only runs one time per day, I just dont want it to take ALL the resources on the LIVE server. Is there any way I can limit the resources available to this SPROC ? Or set some sort of delay etc......

Anything? Any input much appreciated as always!!

Thanks very much!!
mike123

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-08-29 : 06:44:57

I don't know if you can set priority on one specific procedure but you can limit the amount of ram/cpu the sql server will use, however I don't think that will do you any good. You'd probably be far better off trying to optimize your queries...I want to add on a sub query on each row tells me that you are using a cursor of some sort and your procedure would probably perform *alot* better without it. Post your procedure and I'm sure you'll get plenty of performance tips.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-29 : 07:02:32

Sounds more like a correlated subquery.
You can probably recode to use less resources.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-29 : 07:08:42

Post the query here, I am sure some talented people will have a look at it.

Peter Larsson
Helsingborg, Sweden

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-08-30 : 01:06:10

Sure, thanks I'd love to have an opinion on this.

Let me know if posting any other data will be helpful but I think it might get pretty messy with all the tables etc. Here is the query, perhaps I've got the wrong approach:)

Thanks again!

mike123

CREATE PROCEDURE dbo.select_Throttled_EmailUpdates_Mail_IM_Comment_Photos_TEST
(
@daysAgo int = -3 ,
@hoursBuffer int = -48
)
AS SET NOCOUNT ON

select u.userID, u.nameOnline, u.lastLoggedIn, u.emailAddress,
isnull(a.IMCount, 0) as IMCount,
isnull(b.MailCount, 0) as MailCount,
isnull(c.commentCount, 0) as CommentCount,
isnull(d.My_PhotosApprovedCount, 0) as My_PhotosApprovedCount,
isnull(e.Friends_PhotosApprovedCount, 0) as Friends_PhotosApprovedCount

from tblUserDetails u

left join
(
select UD.userID, count(*) as IMCount
from tblUserDetails UD inner join tblInstantMessage IM
on IM.messageToID = UD.userID
and IM.checked = 0
and IM.Date > UD.lastLoggedIN
and IM.Date > dateadd(dd, @daysAgo, getdate())
and IM.Date < dateadd(hh, @hoursBuffer, getdate())
group by UD.userID
having count(*) > 0
) a
on u.userID = a.userID

left join
(
select UD.userID, count(*) as MailCount
from tblUserDetails UD inner join tblMessage M
on M.messageTo = UD.userID
and M.checked = 0
and M.Date > UD.lastLoggedIN
and M.Date > dateadd(dd, @daysAgo, getdate())
and M.Date < dateadd(hh, @hoursBuffer, getdate())
group by UD.userID
having count(*) > 0
) b

on a.userID = b.userID

left join
(
select UD.userID, count(*) as CommentCount
from tblUserDetails UD inner join tblComment C
on C.commentTo = UD.userID
--and M.checked = 0
and c.Date > UD.lastLoggedIN
and c.Date > dateadd(dd, @daysAgo, getdate())
and c.Date < dateadd(hh, @hoursBuffer, getdate())
group by UD.userID
having count(*) > 0
) c
on a.userID = c.userID

left join
(
select UD.userID, count(*) as My_PhotosApprovedCount
from tblUserDetails UD inner join tblextraphotos D
on D.userID = UD.userID
and D.photoDate > UD.lastLoggedIN
and d.photoDate > dateadd(dd, @daysAgo, getdate())
and d.photoDate < dateadd(hh, @hoursBuffer, getdate())
-- and d.status = 1
group by UD.userID
having count(*) > 0
) d
on a.userID = D.userID

left join
(
select UD.userID, count(*) as Friends_PhotosApprovedCount
from tblUserDetails UD
inner join tblFriends F
on UD.userID = F.userID
inner join tblextraphotos E
on E.userID = F.friendID
and E.photoDate > dateadd(dd, -1, getdate())

group by UD.userID
having count(*) > 0
) e
on a.userID = e.userID

where u.emailNotification = 1

--FILTER OUT BOUNCES
AND u.emailaddress NOT IN (SELECT bouncedEmailAddress FROM NDR_EmailMessages WHERE NDR_TypeID = 1)

AND
a.IMCount is not null
or b.MailCount is not null
or c.commentCount is not null
or d.My_PhotosApprovedCount is not null
or e.Friends_PhotosApprovedCount is not null

GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-30 : 04:00:13

I think we have suggested optimizations for this query before, haven't we?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70008
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70407

This should run faster, with appropriate indexes.

CREATE PROCEDURE dbo.select_Throttled_EmailUpdates_Mail_IM_Comment_Photos_TEST
(
	@daysAgo int = -3 ,
	@hoursBuffer int = -48
)
AS

SET NOCOUNT ON

SELECT		u.userID,
		u.nameOnline,
		u.lastLoggedIn,
		u.emailAddress,
		SUM(CASE WHEN IM.messageToID IS NULL THEN 0 ELSE 1 END) IMCount,
		SUM(CASE WHEN M.messageTO IS NULL THEN 0 ELSE 1 END) MailCount,
		SUM(CASE WHEN c.commentTO IS NULL THEN 0 ELSE 1 END) CommentCount,
		SUM(CASE WHEN d.userID IS NULL THEN 0 ELSE 1 END) My_PhotosApprovedCount,
		SUM(CASE WHEN e.userid IS NULL THEN 0 ELSE 1 END)  Friends_PhotosApprovedCount
FROM		tblUserDetails u
LEFT JOIN	tblInstantMessage IM on IM.messageToID = u.userID
			and IM.checked = 0
			and IM.Date > u.lastLoggedIN
			and IM.Date > dateadd(dd, @daysAgo, getdate())
			and IM.Date < dateadd(hh, @hoursBuffer, getdate())
LEFT JOIN	tblMessage M on M.messageTo = u.userID
			and M.checked = 0
			and M.Date > u.lastLoggedIN
			and M.Date > dateadd(dd, @daysAgo, getdate())
			and M.Date < dateadd(hh, @hoursBuffer, getdate())
LEFT JOIN	tblComment C on C.commentTo = u.userID
			--and M.checked = 0
			and c.Date > u.lastLoggedIN
			and c.Date > dateadd(dd, @daysAgo, getdate())
			and c.Date < dateadd(hh, @hoursBuffer, getdate())
LEFT JOIN	tblextraphotos D on D.userID = u.userID
			and D.photoDate > u.lastLoggedIN
			and d.photoDate > dateadd(dd, @daysAgo, getdate())
			and d.photoDate < dateadd(hh, @hoursBuffer, getdate())
			-- and d.status = 1
LEFT JOIN	tblFriends F on F.userID = u.userID
LEFT JOIN	tblextraphotos E on E.userID = F.friendID
			and E.photoDate > dateadd(dd, -1, getdate())
LEFT JOIN	NDR_EmailMessages n ON n.bouncedEmailAddress = u.emailaddress AND n.NDR_TypeID = 1
WHERE		u.emailNotification = 1
		--FILTER OUT BOUNCES
		AND n.bouncedEmailAddress IS NULL
		AND SUM(CASE WHEN IM.messageToID IS NULL THEN 0 ELSE 1 END) > 0
		AND SUM(CASE WHEN M.messageTO IS NULL THEN 0 ELSE 1 END) > 0
		AND SUM(CASE WHEN c.commentTO IS NULL THEN 0 ELSE 1 END) > 0
		AND SUM(CASE WHEN d.userID IS NULL THEN 0 ELSE 1 END) > 0
		AND SUM(CASE WHEN e.userid IS NULL THEN 0 ELSE 1 END) > 0
GROUP BY	u.userID,
		u.nameOnline,
		u.lastLoggedIn,
		u.emailAddress

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-30 : 04:08:26

Or this one, since it only does a GROUP BY on one column.

CREATE PROCEDURE dbo.select_Throttled_EmailUpdates_Mail_IM_Comment_Photos_TEST
(
	@daysAgo int = -3 ,
	@hoursBuffer int = -48
)
AS

SET NOCOUNT ON

SELECT		q.userID,
		w.nameOnline,
		w.lastLoggedIn,
		w.emailAddress,
		q.IMCount,
		q.MailCount,
		q.CommentCount,
		q.My_PhotosApprovedCount,
		q.Friends_PhotosApprovedCount
FROM		(
			SELECT		u.userID,
					u.nameOnline,
					u.lastLoggedIn,
					u.emailAddress
			FROM		tblUserDetails u
			LEFT JOIN	NDR_EmailMessages n ON n.bouncedEmailAddress = u.emailaddress AND n.NDR_TypeID = 1
			WHERE		u.emailNotification = 1
					--FILTER OUT BOUNCES
					AND n.bouncedEmailAddress IS NULL
		) w
INNER JOIN	(
			SELECT		u.userID
					SUM(CASE WHEN IM.messageToID IS NULL THEN 0 ELSE 1 END) IMCount,
					SUM(CASE WHEN M.messageTO IS NULL THEN 0 ELSE 1 END) MailCount,
					SUM(CASE WHEN c.commentTO IS NULL THEN 0 ELSE 1 END) CommentCount,
					SUM(CASE WHEN d.userID IS NULL THEN 0 ELSE 1 END) My_PhotosApprovedCount,
					SUM(CASE WHEN e.userid IS NULL THEN 0 ELSE 1 END)  Friends_PhotosApprovedCount
			FROM		tblUserDetails u
			LEFT JOIN	tblInstantMessage IM on IM.messageToID = u.userID
						and IM.checked = 0
						and IM.Date > u.lastLoggedIN
						and IM.Date > dateadd(dd, @daysAgo, getdate())
						and IM.Date < dateadd(hh, @hoursBuffer, getdate())
			LEFT JOIN	tblMessage M on M.messageTo = u.userID
						and M.checked = 0
						and M.Date > u.lastLoggedIN
						and M.Date > dateadd(dd, @daysAgo, getdate())
						and M.Date < dateadd(hh, @hoursBuffer, getdate())
			LEFT JOIN	tblComment C on C.commentTo = u.userID
						--and M.checked = 0
						and c.Date > u.lastLoggedIN
						and c.Date > dateadd(dd, @daysAgo, getdate())
						and c.Date < dateadd(hh, @hoursBuffer, getdate())
			LEFT JOIN	tblextraphotos D on D.userID = u.userID
						and D.photoDate > u.lastLoggedIN
						and d.photoDate > dateadd(dd, @daysAgo, getdate())
						and d.photoDate < dateadd(hh, @hoursBuffer, getdate())
						-- and d.status = 1
			LEFT JOIN	tblFriends F on F.userID = u.userID
			LEFT JOIN	tblextraphotos E on E.userID = F.friendID
						and E.photoDate > dateadd(dd, -1, getdate())
			GROUP BY	u.userID
		) q ON q.userID = w.userid
WHERE		AND q.IMCount > 0
		AND q.MailCount > 0
		AND q.CommentCount > 0
		AND q.My_PhotosApprovedCount > 0
		AND q.Friends_PhotosApprovedCount > 0

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-30 : 06:22:08

Or maybe this? Substitute .PkCol with the Primary Key Column for each table.

SELECT		u.userID,
		u.nameOnline,
		u.lastLoggedIn,
		u.emailAddress,
		COUNT(DISTINCT IM.PkCol) IMCount,
		COUNT(DISTINCT M.PkCol) MailCount,
		COUNT(DISTINCT C.PkCol) CommentCount,
		COUNT(DISTINCT D.PkCol) My_PhotosApprovedCount,
		COUNT(DISTINCT E.PkCol) Friends_PhotosApprovedCount
FROM		tblUserDetails u
LEFT JOIN	tblInstantMessage IM on IM.messageToID = u.userID
			and IM.checked = 0
			and IM.Date > u.lastLoggedIN
			and IM.Date > dateadd(dd, @daysAgo, getdate())
			and IM.Date < dateadd(hh, @hoursBuffer, getdate())
LEFT JOIN	tblMessage M on M.messageTo = u.userID
			and M.checked = 0
			and M.Date > u.lastLoggedIN
			and M.Date > dateadd(dd, @daysAgo, getdate())
			and M.Date < dateadd(hh, @hoursBuffer, getdate())
LEFT JOIN	tblComment C on C.commentTo = u.userID
			--and M.checked = 0
			and c.Date > u.lastLoggedIN
			and c.Date > dateadd(dd, @daysAgo, getdate())
			and c.Date < dateadd(hh, @hoursBuffer, getdate())
LEFT JOIN	tblextraphotos D on D.userID = u.userID
			and D.photoDate > u.lastLoggedIN
			and d.photoDate > dateadd(dd, @daysAgo, getdate())
			and d.photoDate < dateadd(hh, @hoursBuffer, getdate())
			-- and d.status = 1
LEFT JOIN	tblFriends F on F.userID = u.userID
LEFT JOIN	tblextraphotos E on E.userID = F.friendID
			and E.photoDate > dateadd(dd, -1, getdate())
LEFT JOIN	NDR_EmailMessages n ON n.bouncedEmailAddress = u.emailaddress AND n.NDR_TypeID = 1
WHERE		u.emailNotification = 1
		--FILTER OUT BOUNCES
		AND n.bouncedEmailAddress IS NULL
		AND COUNT(DISTINCT IM.PkCol) > 0
		AND COUNT(DISTINCT M.PkCol) > 0
		AND COUNT(DISTINCT C.PkCol) > 0
		AND COUNT(DISTINCT D.PkCol) > 0
		AND COUNT(DISTINCT E.PkCol) > 0
GROUP BY	u.userID,
		u.nameOnline,
		u.lastLoggedIn,
		u.emailAddress

Peter Larsson
Helsingborg, Sweden

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-08-31 : 00:52:57

Hi Peso,

Thanks again for the help!!

I setup all 3 of your suggestions as SPROCS on my system. The results differed in that my original query only brought back rows that had at least one column with a value (see below...)

a.IMCount is not null
or b.MailCount is not null
or c.commentCount is not null
or d.My_PhotosApprovedCount is not null
or e.Friends_PhotosApprovedCount is not null

this was omitted on your queries so all the records in the main table end up bring brought back. (300k instead of the previous avg of 2k) I'm not sure if this was by done on purpose or not. I tried adding the code in that was omitted but it errors out and looks like it'll take a bit of change to get it working.

I ran the 3 queries on my development box, which is underpowered and got these inconsistent results. Not sure if its any help at all but I'll show you anyways. The times after the SPROC are the total execution times.. not sure why they vary so much?

EXEC select_Throttled_EmailUpdates_Mail_IM_Comment_Photos_TEST 6:20 5:48 2:39 1:52 1:18 1:53 (2k rows returned)
EXEC select_Throttled_EmailUpdates_Mail_IM_Comment_Photos_TEST_1 1:15 2:50 2:23 1:11 0:59 (300k, (ALL) returned)
EXEC select_Throttled_EmailUpdates_Mail_IM_Comment_Photos_TEST_2 0:59 1:59 2:13 2:20 1:33 (300k, (ALL) returned)
EXEC select_Throttled_EmailUpdates_Mail_IM_Comment_Photos_TEST_3 3:58 3:59 3:00 4:42 3:48 (300k, (ALL) returned)

Does this give any ideas to the best approach? I know its pretty difficult to tell from just this, so if you would like me to post anything else just let me know .. thanks!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-31 : 02:14:31

I omitted them on purpose, since the original

where	u.emailNotification = 1
	--FILTER OUT BOUNCES
	AND u.emailaddress NOT IN (SELECT bouncedEmailAddress FROM NDR_EmailMessages WHERE NDR_TypeID = 1)
	AND a.IMCount is not null
	or b.MailCount is not null
	or c.commentCount is not null
	or d.My_PhotosApprovedCount is not null
	or e.Friends_PhotosApprovedCount is not null

seems to have a flaw. You want the records where emailNotification=1, emailaddress is not bounced and imcount is not null.

AND has priority over OR. AND is calculated before OR.

All three of them must be fulfilled. Or you want the records where MailCount is not null, or commentCount is not null, or My_PhotosApprovedCount is not null, or Friends_PhotosApprovedCount is not null.

It seems to me that a paranthesis should be present as


where	u.emailNotification = 1
	--FILTER OUT BOUNCES
	AND u.emailaddress NOT IN (SELECT bouncedEmailAddress FROM NDR_EmailMessages WHERE NDR_TypeID = 1)
	AND	(
			a.IMCount is not null
			or b.MailCount is not null
			or c.commentCount is not null
			or d.My_PhotosApprovedCount is not null
			or e.Friends_PhotosApprovedCount is not null
		)

Am i right?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-31 : 02:19:05

See Books Online for Operator Precedence

Operator Precedence
When a complex expression has multiple operators, operator precedence determines the sequence in which the operations are performed. The order of execution can significantly affect the resulting value.

Operators have these precedence levels. An operator on higher levels is evaluated before an operator on a lower level:

+ (Positive), - (Negative), ~ (Bitwise NOT)

* (Multiply), / (Division), % (Modulo)

+ (Add), (+ Concatenate), - (Subtract)

=, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)

^ (Bitwise Exlusive OR), & (Bitwise AND), | (Bitwise OR)

ALL, ANY, BETWEEN, IN, LIKE, OR, SOME

= (Assignment)

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources