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)
 help tuning query with high reads

Author  Topic 

mike123
Master Smack Fu Yak Hacker

1462 Posts
Posted - 2004-11-05 : 20:00:22

I'm trying to speed up some of my heavier queries and I'm looking at this one and wondering if there are any areas to tune that stick out. I'm new to using profiler and starting to tune queries since I haven't really HAD to do it before, now my servers dieing and I badly will do anything to speed my queries.

This is one of my heavier queries. It selects a thread based on the same design as snitz forums (the ones we are using on sqlteam) average reads are between 10,000 -50,000. Is my paging routine OK? I'm guessing there is probably a better way to do it, and I'm going to look at table datatype insetad of temp tables.

Any suggestions are greatly appreciated, I'm really excited to see how much I can improve this query. If more info is needed for specifics let me know and I'll be happy to post.

Thanks again
mike123


CREATE PROCEDURE dbo.select_thread_test
(
@topic_ID int, -- Topic ID
@Page smallint, -- Which Page
@RecsPerPage tinyint, -- Number of Records to bring back
@replyCount int OUTPUT,
@moreRecords int OUTPUT
)
AS SET NOCOUNT ON
--Create temporary table to store records
CREATE TABLE #TempReply
(
ID int IDENTITY,
userID int,
nameOnline varchar(25),
forum_Level tinyint,
forum_Posts int,
REPLY_ID int,
FORUM_ID tinyint,
R_AUTHOR int,
TOPIC_ID int,
R_STATUS tinyint,
R_DATE datetime,
R_MESSAGE text,
online tinyint,
replyCount int,
forum_Pic int
)
INSERT INTO #TempReply (userID, nameOnline, forum_Level, forum_Posts, REPLY_ID, FORUM_ID, R_AUTHOR, TOPIC_ID, R_STATUS, R_DATE, R_MESSAGE, online, replyCount, forum_Pic)
SELECT tblUserDetails.userID, tblUserDetails.nameOnline, tblUserDetails.forum_Level, tblUserDetails.forum_Posts, FORUM_REPLY.REPLY_ID, FORUM_REPLY.FORUM_ID, FORUM_REPLY.R_AUTHOR, FORUM_REPLY.TOPIC_ID, FORUM_REPLY.R_STATUS, FORUM_REPLY.R_DATE, FORUM_REPLY.R_MESSAGE,
case when exists (select userID from tblActive_Users where tblUserDetails.userid = tblActive_Users.userid) then
'1'
else
'0'
end as Online, (select count(reply_id) FROM forum_reply WHERE topic_ID = @topic_ID), tblUserDetails.forum_Pic

FROM tblUserDetails, FORUM_REPLY WHERE tblUserDetails.userID = FORUM_REPLY.R_AUTHOR AND TOPIC_ID = @topic_ID ORDER BY FORUM_REPLY.R_DATE asc
-- find out the last and first record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
--Now Return the set of paged records, plus indication of further records
set @MoreRecords =
ISNULL((
SELECT COUNT(topic_ID)
FROM #TempReply TR
WHERE TR.ID > @LastRec
),0)
set @replycount=ISNULL((select top 1 replyCount from #TempReply
WHERE ID > @FirstRec AND ID < @LastRec ),0)
SELECT ID,
userID,
nameOnline,
forum_Level,
forum_Posts,
REPLY_ID,
FORUM_ID,
R_AUTHOR,
TOPIC_ID,
R_STATUS,
R_DATE,
online,
--replyCount,
forum_Pic,
R_MESSAGE
FROM #TempReply
WHERE ID > @FirstRec AND ID < @LastRec
SET NOCOUNT OFF


GO

ehorn
Master Smack Fu Yak Hacker

1632 Posts
Posted - 2004-11-05 : 20:13:25
Have you had a look at Jeffs paging technique?

http://weblogs.sqlteam.com/jeffs/archive/2003/12/22/672.aspx
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts
Posted - 2004-11-05 : 23:01:46
hi ehorn,

thanks for the suggestion, I looked at that one and I'm not sure if that ones for me ..... but not positive .. is there anything else I can do to speed it ?

cheers

mike123
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2004-11-06 : 09:16:38
try:

change
FROM tblUserDetails, FORUM_REPLY ...
to
FROM tblUserDetails inner join FORUM_REPLY on (...)

change
case when exists (select userID from tblActive_Users where tblUserDetails.userid = tblActive_Users.userid) then
'1'
else
'0'
to
select ...,
case when userID is null then 0 else 1 end as OnLine
from ... left join tblActive_Users on tblUserDetails.userid = tblActive_Users.userid


Go with the flow & have fun! Else fight the flow
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts
Posted - 2004-11-06 : 17:20:54
spirit1,

I tried your suggestion but end up getting alot of deadlocks after the second join for some reason ? I am now using the first half of your solution because I believe its better practice but no real perf gains.

Now that I have all the execution plans in front of me I am looking at the 3 selects that are done in the temp table. They equal 17% of the relative cost of query. (table scans)

Am I right in thinking if I get this to a table datatype instead of a temp table these table scans will basically be eliminated?

Any good articles on doing this?

Thanks a bunch once again

mike123
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2004-11-06 : 17:49:35
deadlocks???? uauu.... can't see why....
i think that temp variables perform better that temp tables up to 10k rows, but i'm not sure.
it could be that they perform equal but table variables gets slower after 10k rows.

but i quess a table scan will occurr in one way or another no matter where the data is. it has to be read somehow...

articles on searching? look here, try www.sql-server-performance.com

Go with the flow & have fun! Else fight the flow
Go to Top of Page

ehorn
Master Smack Fu Yak Hacker

1632 Posts
Posted - 2004-11-06 : 18:35:06
spirit1,

The following is a good read on the temp table vs. table variable decision.

http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2004-11-06 : 18:50:05
thanx ehorn. i read it and i don't really see how table variables would offer better performance then temp tables in his case.
i guess it would depend on how many rows he's inserting into a temp storage -> read from memory vs read from disk.
maybe it would be faster because of the batch thing and recompilations...
but from my experience that kind of thing does not significantly speed up the search.

but all in all we need to wait for his answer

Go with the flow & have fun! Else fight the flow
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts
Posted - 2004-11-06 : 19:02:55
I will try the table variants and report back. One question in my head thats bugging me however is this.

If you look at the above SPROC, all the table columsn that are being dumped into the temp table etc are pretty small columns except for R_MESSAGE text which can be quite large.

Is it unneceesary IO overhead to be moving this column around when sometimes many of the rows arent needed?

Could we determine which rows we need in the temp table first, and then once we have that, join it onto the wider columns?

Does this make sense?


Thanks a bunch guys

mike123
Go to Top of Page

ehorn
Master Smack Fu Yak Hacker

1632 Posts
Posted - 2004-11-06 : 19:04:00
I see a couple of things to try to work on, I believe:

1.) (select count(reply_id).. may cause a scan
2.) case statement may cause a scan

Also, what indexes do you have on:

tblUserDetails - userid, etc.. ??
forum_reply - topic_id, reply_id ??
tblActive_Users - userid ??

Does this query give you your desired results -- Does it show a comparably better plan ??
SELECT 
	ud.userID, 
	ud.nameOnline, 
	ud.forum_Level, 
	ud.forum_Posts, 
	fr.REPLY_ID, 
	fr.FORUM_ID, 
	fr.R_AUTHOR, 
	fr.TOPIC_ID, 
	fr.R_STATUS, 
	fr.R_DATE, 
	fr.R_MESSAGE,
	case when e.userID is null then 0 else 1 end as Online, 
	d.Reply_id_Count,
	ud.forum_Pic 

FROM 
	tblUserDetails ud
	join FORUM_REPLY fr on fr.R_AUTHOR = ud.userID
	join
	(
		select top 100 percent topic_id, count(reply_id) Reply_id_Count
		FROM FORUM_REPLY 
		WHERE topic_ID = @topic_ID
		GROUP BY topic_id
	) d on d.topic_id = fr.topic_id

	left outer join
	(
		select userID 
		from tblActive_Users
	) e on e.userID = ud.userID

WHERE 
	fr.TOPIC_ID = @topic_ID 

ORDER BY 
	fr.R_DATE asc
quote:
Could we determine which rows we need in the temp table first, and then once we have that, join it onto the wider columns?
I think the penalty comes from the text types increasing the rows over more pages in FORUM_REPLY. For this to provide improvement you would need to move the text column(s) into another table other than FORUM_REPLY which will improve the rows/page ratio for the lookups.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site