Tweak my query (very complex) - 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 2005 Forums

Transact-SQL (2005)

Tweak my query (very complex)

Author

Topic

JokerOfACoder
Starting Member

18 Posts

Posted - 2007-05-14 : 13:50:01

There are inclusion lists. Users can create inclusion lists which contain lists of users and add them to albums so they can view the album.

Basically, a user sets up an inclusion group. Adds users to that group. When they create a album or a blog post, they can add which groups can see it. There are system groups called "Friends" and "Public" also.

The following code takes 3 seconds to execute and uses 80% cpu power on a 2.13ghz Core 2 E6400 with 4 gig ram. All tables contain 5 million rows. This query is going to be the most executed query in the application.

Is that normal?

DECLARE @EditingUserId INT;
SET @EditingUserId = 2;

DECLARE @EditingUserIsAdmin INT;
SET @EditingUserIsAdmin = 0;

DECLARE @PublicInclusionId INT;-- Record Id of friend inclusion rec
SET @PublicInclusionId = 1;

DECLARE @FriendsInclusionId INT; -- Record Id of friend inclusion rec
SET @FriendsInclusionId = 2;

SELECT * FROM album WHERE userid = 1 AND
(

EXISTS
(
SELECT referenceid FROM inclusion_reference WHERE referenceid = album.id AND
EXISTS
(
SELECT inclusionid FROM inclusion_user WHERE userid = @EditingUserId AND inclusion_user.inclusionid = inclusion_reference.inclusionid
)
)
OR
EXISTS
(
SELECT referenceid FROM inclusion_reference WHERE referenceid = album.id AND inclusionid = @PublicInclusionId;

)
OR
EXISTS
(
SELECT referenceid FROM inclusion_reference WHERE referenceid = album.id AND inclusionid = @FriendsInclusionId AND
EXISTS
(
SELECT user_friends WHERE userid = album.userid AND targetid = @EditingUserId
)
)
OR
(
album.userid = @EditingUserId OR @editingUserIsAdmin = 1
)
)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-14 : 14:09:47

You have not explained to us the logic you are trying to apply in this SQL statement; what is it doing? How about some sample data and expected results? I am pretty sure that there is a much more efficient way to write it than this, but without more info it is very hard to know for sure. Also, we don't know what kind of indexes you have on your tables.

It is much easier to help you if you

1) provide some CREATE TABLE statements
2) provide some INSERTS that give us sample data
3) explain in detail what you need to write
4) show us the expected results based on that sample data

Otherwise, it is just a guessing game. Much easier if you tell us exactly what you need, so we can give you exactly what you want. Makes sense, right?

- Jeff
http://weblogs.sqlteam.com/JeffS

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-05-14 : 14:35:57

First, try changing the AND EXISTS reference to inner joins (the same logical equivalent).


SELECT *
FROM   album
WHERE  userid = 1
       AND (EXISTS (SELECT referenceid
                    FROM   inclusion_reference
                           inner join inclusion_user
                             on inclusion_reference.inclusionid = inclusion_user.inclusionid
                    WHERE  inclusion_reference.referenceid = album.id
                           and inclusion_user.userid = @EditingUserId)
             OR EXISTS (SELECT referenceid
                        FROM   inclusion_reference
                        WHERE  referenceid = album.id
                               AND inclusionid = @PublicInclusionId)
             OR EXISTS (SELECT referenceid
                        FROM   inclusion_reference
                               inner join user_friends
                                 on user_friends.userid = album.userid
                        WHERE  referenceid = album.id
                               AND inclusionid = @FriendsInclusionId
                               and user_friends.targetid = @EditingUserID)
             OR album.userid = @EditingUserId
             OR @editingUserIsAdmin = 1)

Then, try changing to a UNION query


SELECT	album.*
FROM	album
		inner join inclusion_reference on album.id = inclusion_referenceid
		inner join inclusion_user on inclusion_reference.inclusionid = inclusion_user.inclusionid
WHERE	userid = 1
		and inclusion_user.userid = @EditingUserId
UNION
select	album.*
		inner join inclusion_reference on album.id = inclusion_referenceid
from	album
WHERE	userid = 1
		and inclusion_reference.inclusionid = @PublicInclusionId
UNION
select	album.*
		inner join inclusion_reference on album.id = inclusion_referenceid
		inner join user_friends on user_friends.userid = album.userid
from	album
WHERE	userid = 1
		and inclusion_reference.inclusionid = @FriendsInclusionId
		and user_friends.targetid = @EditingUserID
UNION
select	album.*
from	album
where	album.userid = @EditingUserId
		OR @editingUserIsAdmin = 1

And of course, make sure your data is indexed properly.

e4 d5 xd5 Nf6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources