Slow Performing Query - 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)

Slow Performing Query

Author

Topic

hacktothefuture
Starting Member

10 Posts

Posted - 2007-12-17 : 18:12:39

Hello All

I am not the greatest at designing complex queries and feel that I rely on subqueries much more than I should. Well this has lead me to a performance problem in one of my queries. Im trying to get much better at this and would certainly welcome any help that I can get (Including any helpful books on the subject you can recommend).

This is the the situation. I have two tables. One is a reference table used for storing possible user preferences. Its structure is like this:

Preferences
Id PrefName
----------------------
1 Blue Cars
2 Candy Bars
3 Long Walks
4 Sunsets

The other table stores each users selected preferences. It must not only store current preferences but also prefences the user has selected in the past and has since deactivated. Like this:

UserPrefences
Id UserId Pref Status
--------------------------------
1 1 1 0
2 1 2 1
3 2 1 0
4 2 2 1
5 2 3 1

I need to display all preferences options for a user and whether the user has selected it. Like this:

Preferences for UserId: 1

Id PrefName IsSelected
-----------------------------------
1 Blue Cars 0
2 Candy Bars 1
3 Long Walks 0
4 Sunsets 0

Here is the query I used to solve the problem:


declare @userid int
set @userid = 1

select id, PrefName,
ISNUMERIC((select UserId FROM UserPreferences up 
		WHERE up.pref = p.Id and up.UserId = @userId and status = 1)) As IsSelected
from Preferences p

Well this query works great, except for one thing, its slow, very slow. The Preferences table has grown very large so that Users can have a large number of preferences and the users table is also quite large. Eventually this query will not work, actually its pretty much there now.

Is the structure of the tables flawed or is the query? I cannot really change the table structure but I would like to know if that is recommended but is there a way to query these tables more efficiently and quickly.

Thanks very much for any help you can give.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-18 : 00:11:42

Try this. Also put an index on id columns on both tables (that used in join )and also UserId column of second table

SELECT p.Id,p.PrefName,ISNULL(u.Status,0) AS 'IsSelected'
FROM Preferences p
LEFT OUTER JOIN UserPreferences u
ON u.Pref=p.Id
WHERE u.UserId={req user id}

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-18 : 01:21:22

UserPreferences should not have the ID column. the PK in this table should be a composite key on UserID, PrefID.

With your current design, you could have a user that simultaneously loves and hates long walks and sunsets. you don't want that to be allowed do you?

Can you post the DDL for these two tables? are the "number" columns integer types, or are they char/varchar/text/etc?

elsasoft.org

hacktothefuture
Starting Member

10 Posts

Posted - 2007-12-18 : 11:50:17

Thanks for your replies!

visakh16, thanks for the hints on the indexes they are in place but your query did not return the desired results. All records for user 1 were returned instead of all preferences and which ones user 1 had selected.

jezemine, this is a very interesting idea that I had not thought about. The tables are designed to indicate the current preferences of the user (not the things he doesn't). If at one point the user did not like long walks but now he does that is ok. All number columns are in fact integers. As far as the DDL, this is more of an example of my actual tables. The tables themselves have long complicated field names and have other fields that are not relevant to the question and I didnt want to confuse the issue.

matty
Posting Yak Master

161 Posts

Posted - 2007-12-19 : 04:48:49

SELECT P.ID,PrefName,ISNULL(status,0) As IsSelected
FROM Preferences p
LEFT JOIN UserPreferences up
ON up.pref = p.Id AND up.UserId = 1

caisys
Starting Member

16 Posts

Posted - 2007-12-19 : 07:34:49

How about this?

SELECT P.ID,PrefName,ISNULL(status,0) As IsSelected
FROM Preferences p
LEFT OUTER JOIN
(Select [id] from UserPreferences
where userid = 'YOUR USER' and status = 1) up
ON up.id = p.Id

hacktothefuture
Starting Member

10 Posts

Posted - 2007-12-20 : 18:08:07

Im afraid those did not work as expected either. Thanks for your help though...

hacktothefuture
Starting Member

10 Posts

Posted - 2008-01-23 : 15:13:13

Can anyone provide any further insight on this query issue?

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-23 : 15:44:55

[code]DECLARE @Userid INT
SET @Userid = 1

SELECT p.PrefName, ISNULL(a.Status, 0) as Status
FROM Preferences p
LEFT OUTER JOIN ( SELECT u.UserId, u.Pref, u.Status
FROM Preferences p
LEFT OUTER JOIN UserPreferences u ON p.Id = u.Pref
WHERE u.UserId = @UserId ) a ON p.Id = a.Pref[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources