degrading query performance (index)

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)

degrading query performance (index)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-03-31 : 17:00:37

Hi,

I have a query that was doing executions in 1 second .. now its gone up to like 15 seconds - 30 seconds in some cases .. and I'm not exactly sure why... there are more records in the table now for sure, but not exponentially more like execution time has gone up..

one thing I did was add a "visible" column, and modify my query so a visible =1 is added.

I am thinking this is probably the problem ? I am not very good with indexes and any guidance on this is greatly appreciated ! (I usually use index wizard =\)

Thanks again

,
mike123

QUERY:

CREATE PROCEDURE [dbo].[select_ProfileViews_TOP3]
(
@userID int,
@genderID tinyint
)

AS SET NOCOUNT ON

IF @genderID > 2

SELECT TOP 3 viewerUserID,viewerNameOnline,MAX(viewDate) AS max_viewDate
FROM (

SELECT viewerUserID,UD.nameOnline as viewerNameOnline, viewDate

FROM tblProfileViews_Users PVU WITH (NOLOCK)

JOIN tblUserDetails UD on PVU.viewerUserID = UD.userID

WHERE profileUserID = @userID AND ( active = 1 or active = 4) AND visible = 1

) d

GROUP BY viewerUserID ,viewerNameOnline
ORDER BY max_viewDate DESC

ELSE

SELECT TOP 3 viewerUserID,viewerNameOnline,MAX(viewDate) AS max_viewDate
FROM (

SELECT viewerUserID,UD.nameOnline as viewerNameOnline, viewDate

FROM tblProfileViews_Users PVU WITH (NOLOCK)

JOIN tblUserDetails UD on PVU.viewerUserID = UD.userID

WHERE profileUserID = @userID AND UD.genderID = @genderID AND ( active = 1 or active = 4) AND visible = 1

) d

GROUP BY viewerUserID ,viewerNameOnline
ORDER BY max_viewDate DESC

GO

TABLE:

CREATE TABLE [dbo].[tblProfileViews_Users](
[viewID] [int] IDENTITY(1,1) NOT NULL,
[viewerUserID] [int] NOT NULL,
[profileUserID] [int] NOT NULL,
[viewDate] [smalldatetime] NOT NULL,
[visible] [tinyint] NULL
) ON [PRIMARY]

GO

INDEX:

CREATE NONCLUSTERED INDEX [_dta_index_tblProfileViews_Users_7_1191011324__K3_K2_4] ON [dbo].[tblProfileViews_Users]
(
[profileUserID] ASC,
[viewerUserID] ASC
)
INCLUDE ( [viewDate]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-31 : 17:05:59

I think its because you use variables during your select as below

profileUserID = @userID

Can you try giving the actual value instead of the variable and re-run the query and see how long it takes.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-31 : 17:16:14

quote:
Originally posted by vijayisonly

I think its because you use variables during your select as below

profileUserID = @userID

Can you try giving the actual value instead of the variable and re-run the query and see how long it takes.

Huh? Are you attempting to address a parameter sniffing issue?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-31 : 17:21:05

yeah..kinda..I've faced issues before where if I used a variable rather than the actual value...the query runs a lot slower..

came here for help as well

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=119740

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-03-31 : 17:21:21

quote:
Originally posted by vijayisonly

I think its because you use variables during your select as below

profileUserID = @userID

Can you try giving the actual value instead of the variable and re-run the query and see how long it takes.

I am passing a variable tho, I am not sure what you are getting at?

thx!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-31 : 17:37:10

He's referring to parameter sniffing. If parameter sniffing is an issue, you can easily fix it by doing this:

CREATE PROCEDURE [dbo].[select_ProfileViews_TOP3]
(
@userID int,
@genderID tinyint
)

AS

SET NOCOUNT ON

DECLARE @uid int, @gid tinyint

SELECT @uid = @userID, @gid = @genderID

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-03-31 : 18:17:35

ah ok .. didnt even know this issue existed :) ...

I do think it has something to do with the extra column tho and it needing an index...

if anyone has any index suggestions would be greatly appreciated...

I am no index guru and I have about 500,000,000 rows + in this table, so getting it perfect is really important ...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-31 : 18:45:56

You should be partitioning a table of that size if you expect good performance. Have you looked at partitioned tables yet?

If the possible values of visible are 1 and 0 or maybe even NULL, then you should not index that column as it is not at all selective. You could add it to an existing index though, but I'm not sure if you'd see any gains with that.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-03-31 : 18:51:58

My bad only 191,000,000 rows, but still I have not partitioned it yet. It was working fast until recently, so thats why I am a little confused.

You are correct in assuming values of only 1 or 0 in this "visible" column.

To include it into an existing index I should just drop the existing index, and recreate it with a new column ? for example

CREATE NONCLUSTERED INDEX [_dta_index_tblProfileViews_Users_7_1191011324__K3_K2_4] ON [dbo].[tblProfileViews_Users]
(
[profileUserID] ASC,
[viewerUserID] ASC
)
INCLUDE ( [viewDate]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

changes to:

CREATE NONCLUSTERED INDEX [_dta_index_tblProfileViews_Users_7_1191011324__K3_K2_4] ON [dbo].[tblProfileViews_Users]
(
[profileUserID] ASC,
[viewerUserID] ASC,
[visible] ASC
)
INCLUDE ( [viewDate]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-31 : 18:56:12

Yes that's how to add a column to an existing index. But I doubt it will help with your performance issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-03-31 : 19:37:07

quote:
Originally posted by tkizer

If the possible values of visible are 1 and 0 or maybe even NULL, then you should not index that column as it is not at all selective. You could add it to an existing index though, but I'm not sure if you'd see any gains with that.

Hey Tara,

Just curious, what do you mean by the column is "not selective"

I do have it in my where clause, and was under the impression a index actually would help..

WHERE profileUserID = @userID AND UD.genderID = @genderID AND ( active = 1 or active = 4) AND visible = 1

Just to be clear, thanks again!

mike123

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-31 : 19:49:27

It isn't selective if it's the first column in an index. Since 2 different values amongst millions of rows is not selective enough. Selectivity needs to be very high perhaps 90%, I've heard even 95%. 2/191000000 is practically zero.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-03-31 : 20:04:13

quote:
Originally posted by tkizer

It isn't selective if it's the first column in an index.

Hi Tara,

Ok, but just to be sure, my index is as shown below. This column of "visible" is not the first column in an index, at least the way I am interpreting it.

Thanks again :)
mike123

CREATE NONCLUSTERED INDEX [_dta_index_tblProfileViews_Users_7_1191011324__K3_K2_4] ON [dbo].[tblProfileViews_Users]
(
[profileUserID] ASC,
[viewerUserID] ASC
)
INCLUDE ( [viewDate]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2009-03-31 : 20:10:47

Mike,

have you look into your query's execution plan? I believe your table statistics are up to date and indexes are rebuilt or reorganized regurlary. My two cents.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-31 : 20:12:02

quote:
Originally posted by mike123

quote:
Originally posted by tkizer

It isn't selective if it's the first column in an index.

Hi Tara,

Ok, but just to be sure, my index is as shown below. This column of "visible" is not the first column in an index, at least the way I am interpreting it.

That's correct. I just wanted to give you some information on indexes.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources