rewrite query? Causing deadlocks / timeouts

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)

rewrite query? Causing deadlocks / timeouts

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-10-25 : 03:36:07

This query probably looks pretty ugly to you guys, hopefully theres some obvious things to be improved. It's timing out all the time, and being the victim of deadlocks, and seriously degrading performance of the whole db server.

Can I improve this? If so how? Any help is appreciated.

Thanks again
mike123



CREATE PROCEDURE select_online_members_byLetter 
	(@letter as char)
	
AS SET NOCOUNT ON
if @letter = '*'
	SELECT TOP 10
--uq_users.sess, 

uq_users.userID, UD.nameOnline, UD.age, UD.genderID, UD.statusID, UD.points, UD.votes, 
UD.date, UD.userNote, UD.city, UD.stateProvID, 

(select count(userID) as tblthumb_count from tblExtraPhotos EP where status = 1 AND UD.userid = EP.userid) as thumb_count, 

case when exists (select userID from tblVideo where UD.userid = tblVideo.userid AND active ='1' ) then 
	'Y' 
else 
	'N' 
end as VideoExist,

case when exists (select userID from tblExtraPhotos where UD.userid = tblExtraPhotos.userid AND active ='1' AND photoID ='99' ) then 
	'Y' 
else 
	'N' 
end as superPhoto  

FROM (SELECT userID, min(sessionID) AS sess FROM tblactive_users WHERE userID > '0' GROUP BY userID) AS uq_users 

INNER JOIN tblactive_users AS info ON uq_users.sess = info.sessionID AND uq_users.userID = info.userID 
JOIN tblUserDetails UD on UD.userID = uq_users.userID WHERE Ascii(substring(UD.nameonline, 1, 1)) NOT BETWEEN 65 AND 122  ORDER BY sessionID DESC


else

	SELECT TOP 20
--uq_users.sess, 

uq_users.userID, UD.nameOnline, UD.age, UD.genderID, UD.statusID, UD.points, UD.votes, 
UD.date, UD.userNote, UD.city, UD.stateProvID, 

(select count(userID) as tblthumb_count from tblExtraPhotos EP where status = 1 AND UD.userid = EP.userid) as thumb_count, 

case when exists (select userID from tblVideo where UD.userid = tblVideo.userid AND active ='1' ) then 
	'Y' 
else 
	'N' 
end as VideoExist,

case when exists (select userID from tblExtraPhotos where UD.userid = tblExtraPhotos.userid AND active ='1' AND photoID ='99' ) then 
	'Y' 
else 
	'N' 
end as superPhoto 

FROM (SELECT userID, min(sessionID) AS sess FROM tblactive_users WHERE userID > '0' GROUP BY userID) AS uq_users 
INNER JOIN tblactive_users AS info ON uq_users.sess = info.sessionID AND uq_users.userID = info.userID 
JOIN tblUserDetails UD on UD.userID = uq_users.userID 
WHERE UD.nameonline like @Letter + '%' ORDER BY sessionID DESC
GO

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-10-25 : 12:06:07

yeah, that is ugly. You can format your code using the code tags in the forum editor so we can read it a little easier.

Here are some questions:

1. Do you have any indexes on your underlying tables?
2. Do you have indexes on your foreign keys used in joins?
3. Do you have an index on UD.nameonline?
4. Do you have an index on sessionid?
5. What does your execution plan look like?

-ec

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-10-25 : 21:06:08

Hi EyeChart,

I created a JPG of the Execution plan so hopefully its easier to read :)

tblActiveUsers shows an index seek
tblExtraPhotos shows an clustered index seek
tblVideos shows an clustered index seek
tblUserDetails shows an clustered index seek

Yes I have an index on UD.nameonline
No I don't have an index on sessionid

Thanks very much

mike123

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-10-26 : 01:55:33

do you have an index on tblextraphotos.userid ?

It would also help if we had the DDL for the tables and any indexes these tables have (including the PK). thanks.

-ec

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-10-26 : 04:27:11

Hi EyeChart,

Here is the info you requested. Thanks again!

mike123

tblExtraPhotos - clustered index on photoID, userID
tblActiveUsers - non clusted userID
tblVideos - clustered userID
tblUserDetails - clustered userID
tblUserDetails - nonclustered nameOnline



CREATE TABLE [dbo].[tblActive_Users] (
	[sessionID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[userID] [int] NULL ,
	[pageView] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[tblExtraPhotos] (
	[counterID] [int] IDENTITY (1, 1) NOT NULL ,
	[photoID] [tinyint] NOT NULL ,
	[userID] [int] NOT NULL ,
	[photoDate] [smalldatetime] NOT NULL ,
	[caption] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[status] [tinyint] NOT NULL ,
	[ratingID] [tinyint] NULL ,
	[galleryID] [tinyint] NULL ,
	[guid] [uniqueidentifier] NULL 
) ON [PRIMARY]
GO



 
CREATE TABLE [dbo].[tblVideos] (
	[photoID] [int] IDENTITY (1, 1) NOT NULL ,
	[userID] [int] NOT NULL ,
	[URL] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[active] [tinyint] NULL 
) ON [PRIMARY]
GO




CREATE TABLE [dbo].[tblUserDetails] (
	[UserID] [int] IDENTITY (1, 1) NOT NULL ,
	[NameOnline] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[NameFirst] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[NameLast] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Password] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Address] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[City] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PhoneNumber] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[mobileNumber] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[StateProvID] [tinyint] NULL ,
	[CountryID] [tinyint] NULL ,
	[PostalCode] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[EmailAddress] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[GenderID] [tinyint] NULL ,
	[Date] [datetime] NULL ,
	[LastLoggedIn] [datetime] NULL ,
	[LastUpdated] [smalldatetime] NULL ,
	[Active] [tinyint] NULL ,
        [GUID] [uniqueidentifier] NULL 

) ON [PRIMARY]
GO

Kristen
Test

22859 Posts

Posted - 2004-10-26 : 08:27:52

I reckon we'll need the DDL for the indexes too please

krsiten

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-10-26 : 18:20:47

I went to each of the tables in EM and went 'generate sql script' I then chose to create the table with indexes. Here is what I got for all the tables. My experience with indexes goes as far as sqltuning wizard. It doesnt appear by these statements the indexes all exist?

Maybe its time to run the index tuning wizard again? It hasn't been run in sometime and I believe some new tables have been made.

(note in my orignal post i omitted columns in 'tbluserdetails' that I thought were not important and would make it easier to read. since there are some indexes on some of the columns as I jsut found out I will repost the create statement below)

Thanks again

Mike123



 CREATE  INDEX [tblActive_Users8] ON [dbo].[tblActive_Users]([userID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE  INDEX [tblUserDetails6] ON [dbo].[tblUserDetails]([UserID], [NameOnline], [GenderID], [SexualityID], [Date], [Votes], [Points], [Active]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [tblUserDetails7] ON [dbo].[tblUserDetails]([Date], [UserID], [NameOnline], [GenderID], [Votes], [Points], [Active]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO





CREATE TABLE [dbo].[tblUserDetails] (
	[UserID] [int] IDENTITY (1, 1) NOT NULL ,
	[NameOnline] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Password] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[StateProvID] [tinyint] NULL ,
	[CountryID] [tinyint] NULL ,
	[EmailAddress] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[GenderID] [tinyint] NULL ,
	[SexualityID] [tinyint] NULL ,
	[Date] [datetime] NULL ,
	[LastLoggedIn] [datetime] NULL ,
	[LastUpdated] [smalldatetime] NULL ,
	[Votes] [int] NULL ,
	[Points] [int] NULL ,
	[Active] [tinyint] NULL ,
	[GUID] [uniqueidentifier] NULL 
	 
) ON [PRIMARY]
GO

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-10-27 : 16:26:28

mike, I haven't forgotten about this post. will try to work on something for you today.

-ec

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-10-27 : 20:48:51

quote:
Originally posted by eyechart

mike, I haven't forgotten about this post. will try to work on something for you today.

-ec

thanks alot :) .. your help is very much appreciated :)

mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources