ordering by IDENTITY instead of DATE

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)

ordering by IDENTITY instead of DATE

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-04-13 : 17:21:04

Hey Guys,

I have a table that I order by DATE. Its a table I use for logging, so the IDENTITY column coincides with the "viewDate" column.

Is there any general opinion on using an ORDER BY on the IDENTITY column , rather than the "date" column ?

I'm trying to speed up this query any way I can, as its going too slow for me.

Thanks again!
mike123

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-13 : 17:26:50

Deciding what to sort on depends upon your requirement. There is no general opinion on it as it just depends on the requirement of the application.

Can you post the query and what indexes you have? Also how many rows do the tables have and how many rows are returned by the query?

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-04-13 : 17:42:12

Hey Tara,

Well since the data is inserted in a log style fashion (never updated), we know that the ORDER BY should return the same data, whether we have it on the IDENTITY column, or on the DATE column.

You can see we are only returning 3 rows, unfortuantely the sub query inside this SP can return 100's or 1000's of rows. There are about 150 million rows in this table.

I will post the Query and Indexes below :

Thanks once again!, very much appreciated!
mike123

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

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 Structure:

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-13 : 17:46:45

Do you have a clustered index on the table?

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-04-13 : 17:53:09

Hi Tara,

No, this is the only INDEX listed under the INDEX folder for this table in SSMS. The table is inserted into quite often, and I relied on the index tuning wizard to make this decision for me

Do you recommend I have a different index structure?

Thanks again!
Mike123

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-13 : 19:55:51

Don't you have viewID as the primary key constraint? If you do, then is that clustered or non-clustered? If you handle the constraint via the application, then I'd highly suggest adding a PK constraint to it.

If it were my system, I'd go with viewDate, viewID as the composite primary key and I'd make it clustered.

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-04-13 : 20:02:47

Hey Tara,

I could be pretty off in my thinking here, but I did not put a constraint because I figured it would be faster without one. What benefit would I have by having a constraint?

If userA views userB multiple times, we insert multiple entries. This logic perhaps should be changed, and could help reduce the number of rows, and allow us to execute the query faster.

thanks again! :)
mike123

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-13 : 23:43:38

Constraints do not slow your queries down. They have to do with protecting data integrity and are not related to performance. You do get indexes created with PK and unique constraints, but not with unique constraints. But that's as close of a comparison we can make between constraints and 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."

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-04-14 : 00:52:19

Hey Tara,

Ok, but it can slow my INSERT statements ? I know I do not use Constraints enough as I should, and need to improve on this, but I still don't see how a constraint in beneficial in this situation. How will it protect data integrity in this situation ?

Is a clustered index going to be faster than a non clustered index for my select query ?

Appreciate your input!

Thanks again,
mike123

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-14 : 01:22:12

It will not slow or speed up any queries. That's the job of indexes.

An example of why you need indexes is let's says someone with sysadmin runs the following on your system:

SET IDENTITY_INSERT tblProfileViews_Users ON

INSERT INTO tblProfileViews_Users (viewID, ...)
SELECT 1234, ...

Now let's say 1234 exists in your table already. Without a constraint, there is nothing preventing this "duplicate" from getting inserted into your table. With a constraint, that would throw an error and prevent the duplicate.

This is just a simple example. Contraints are there to protect data. It's to protect against sysadmins as well as bad code from developers. They are imperative. I can only assume that you don't have a DBA as this is very high on our list of things that need get done on a database.

The clustered index could help with the sort operation. You'll need to compare execution plans in a test environment to see what works best.

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