Faster Update Wanted - 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 2000 Forums

SQL Server Development (2000)

Faster Update Wanted

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-10 : 23:44:21

CREATE TABLE [PageHits] (
[PageHitID] [int] IDENTITY (1000000, 1) NOT NULL ,
[ClientID] [int] NOT NULL ,
[UserID] [int] NOT NULL ,
[CourseID] [int] NULL ,
[ModuleID] [int] NULL ,
[UrlDate] [datetime] NOT NULL DEFAULT (getdate()),
[Url] [varchar] (300) NOT NULL ,
[NextURLDate] [datetime] NULL
) ON [PRIMARY]
END
-------------------------------------
CREATE CLUSTERED INDEX [PageHitIndex] ON [dbo].[PageHits]([ClientID], [UserID], [CourseID], [ModuleID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
---------------------------------------------------

UPDATE PageHits WITH (UPDLOCK)
SET NextURLDate = (select min(UrlDate) FROM PageHits P2 where P2.UrlDate > P1.URLDate and P2.UserID=1268101 )
FROM PageHits P1
WITH (NOLOCK)
WHERE P1.NextURLDate IS NULL AND UserID = 1268101

This query can take *way too long* to execute (60 seconds).

Can anyone propose an optimization like fining all records for the UserID once? The execution plan shows that the records are found twice.

Sam

nr
SQLTeam MVY

12543 Posts

Posted - 2003-03-11 : 01:42:53

Can you change the index to

CREATE CLUSTERED INDEX [PageHitIndex] ON [dbo].[PageHits]([UserID], [ClientID], [CourseID], [ModuleID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

Or create a new index for UserID.
You could make it UserID, NextURLDate, UrlDate.

Be careful about slowing down the inserts.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 03/11/2003 01:44:03

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-11 : 06:10:49

You're right, the clustered index isn't working. I could include the clientid in the search, or create another index, but ...

Would it be more general to drop the clustered index and replace it with 4 indicies each on clientid, userid, courseid, moduleid?

What are the tradeoffs?

Sam

nr
SQLTeam MVY

12543 Posts

Posted - 2003-03-11 : 06:33:32

Depends on the queries.
For what you have posted here only an index with UserID as the first field wouold be any use.

Having a clustered index may cause problems with the inserts.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-11 : 09:51:52

I added the ClientID to the update query, and the time was reduced to 3 seconds from 60 seconds. ClientID and UserID are the first two elements of the clustered index. CourseID and ModuleID were not specified.

This seems to say a clustered index will work, but only if the trailing elements are elminiated. The behavior indicates that clustered indicies work if trailing elements are not used?

I'm still thinking that 4 separate indicies may be the way to go. Are there any articles on choosing a clustered index?

Sam

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-11 : 12:28:09

Still perplexed

My revised query reads

UPDATE PageHits WITH (UPDLOCK)
SET NextURLDate = (select min(UrlDate) FROM PageHits P2 where P2.UrlDate > P1.URLDate and P2.UserID=1268101 AND CourseID=111)
FROM PageHits P1
WITH (NOLOCK)
WHERE P1.NextURLDate IS NULL AND UserID = 1268101 AND CourseID=111

By adding the CourseID the query result set is the same, but the execution time is reduced from 2 minutes to 2 seconds. The clustered index must have been used.

An amazing improvement in performance.

There may be a way to restructure the clustered index to benefit several other queries by moving ClientID to the end of the index and UserID to the front?? I could omit the use of ClientID in a lot of queries and gain the performance benefit of the clustered index.

The tests I've run suggest that a query will use the clustered index if all the indicies are specified in the where clause, OR, if trailing indicies are not specified.

Omit a leading index and the clustered index is not used.

Am I way off or is this correct? Any comments?

Sam

nr
SQLTeam MVY

12543 Posts

Posted - 2003-03-11 : 13:09:49

For an index to be useful the first column must be referenced.
for a query with an equality on a and b the following indexes wouold be useful

a
b
a,b,c
a,c,b (but not as good as the above)
a,b,c,d,e,f

the following will not be used
c,a,b
c
c,b,a

A clustered index will meen one less read as the root is the data page but may be worse for scans.

Read about indexes in bol.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources