query taking longer than expected 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

SQL Server 2005 Forums

Transact-SQL (2005)

query taking longer than expected to run

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-03-08 : 15:54:51

Hi,

I have 3 queries that are running quite fast (under 1 second each).

I have a 4th query that is a combination of the 3 prior queries. It selects the same data and basically just combines all their WHERE clauses as you can see below. The problem is that its taking over 1 minute to run and really halting the server. I have no idea why its taking so long and can't understand how it can take this long. Is there something wrong with the way I am writing my query?

Any help is much appreciated.. thanks again! :)

mike123

SPROC #1 (takes 1 second, as do all the variants for tblfriendslist1,tblfriendslist2,tblfriendslist3 etc..)

CREATE PROCEDURE dbo.select_LatestPhotos_FriendsList1
(
@userID int
)
AS SET NOCOUNT ON

SELECT top 100 EP.userID,counterID,caption,UD.nameOnline FROM tblextraphotos EP
JOIN tblUserDetails UD on UD.userID = EP.userID
WHERE status = 1
AND
EP.userID IN (SELECT friendID FROM tblFriends1 WHERE userID = @userID)
ORDER BY photoDate DESC
GO

SPROC #2

CREATE PROCEDURE dbo.select_LatestPhotos_All_Lists
(
@userID int
)

AS SET NOCOUNT ON

SELECT top 10 EP.userID,counterID,caption,UD.nameOnline FROM tblextraphotos EP
JOIN tblUserDetails UD on UD.userID = EP.userID
WHERE status = 1

AND

EP.userID IN (SELECT friendID FROM tblFriends1 WHERE userID = @userID)
OR
EP.userID IN (SELECT friendID FROM tblFriends2 WHERE userID = @userID)
OR
EP.userID IN (SELECT friendID FROM tblFriends2 WHERE userID = @userID)

ORDER BY photoDate DESC

GO

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-03-08 : 16:20:03

1 minute is reasonable for 3 subsets with an OR

first blush, you can try creating a subset of the 3 ORs and select from there

or post some ddl and dml if you want a query

HTH

--------------------
keeping it simple...

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-03-08 : 16:30:31

Hey Jen,

Can you recommend the best way to write this query? I am unfamiliar with what I am doing exactly, and confused with the subsets terminology.

I'm figuring if I can get all the data with 3 seperate SELECT's (my first 3 SPROCS) within just a second I should be able to somehow get this in one sproc in approx the total execution time of those 3 sprocs. Am I wrong? I think this is maybe what you are referring to by saying "you can try creating a subset of the 3 ORs and select from there"

I'll be able to get the DDL and DML shortly here too if we need to go to that :)

Thanks again !
mike123

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-08 : 16:39:04

It seems to me that you'd want to use a join instead of the subquery:

SELECT top 100 EP.userID,counterID,caption,UD.nameOnline
FROM tblextraphotos EP
JOIN tblUserDetails UD
ON UD.userID = EP.userID
JOIN tblFriends1 F
ON UD.userID = F.userID
WHERE status = 1 AND F.userID = @userID
ORDER BY photoDate DESC

To optimize your other stored procedure, we need sample data and expected result set.

Why do you have this duplication though?:

EP.userID IN (SELECT friendID FROM tblFriends1 WHERE userID = @userID)
OR
EP.userID IN (SELECT friendID FROM tblFriends2 WHERE userID = @userID)
OR
EP.userID IN (SELECT friendID FROM tblFriends2 WHERE userID = @userID)

And more importantly why do you have two tblFriends tables?!!!

Tara Kizer

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-03-08 : 16:49:09

probably something like...

userid in (select friendid from tbl1 where userid=@userid union select friendid from tbl2 where userid=@userid union select friendid from tbl3 where userid=@userid )

--------------------
keeping it simple...

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-03-08 : 23:55:17

After reading through your query, I am confused as to what is being accomplished by first looking up a user, then building a list of friends, then searching to see if that user exists in that listing of friends. Since it is not obvious to me as to the real-world application of this, I'm going to assume that what you are actually looking to do is simply looking to find out if their are any friends for that user in the tables. If this is what you are looking to accomplish, See if this is faster for you. If this is not what you want, please post some test data and explain what you are trying to accplish to help clearify, I have a feeling there is a much better way to write this, but I need to know what you are looking to accomplish.
[CODE]
SELECT top 10 EP.userID,counterID,caption,UD.nameOnline FROM tblextraphotos EP Inner JOIN tblUserDetails UD
on UD.userID = EP.userID
WHERE status = 1
and EP.UserID = @UserID
and
(
exists(select * FROM tblFriends1 aa WHERE aa.friendID = ep.userID)
or
exists(select * FROM tblFriends2 bb WHERE bb.friendID = ep.userID)
or
exists(select * FROM tblFriends3 cc WHERE cc.friendID = ep.userID)
)
[/CODE]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources