Getting 10 Records the Fastest Way Possible // ROWLOCK, UPDLOCK, READPAST

microsoft.public.sqlserver.programming

3 May 2007 7:06 PM

sloan

I got interested in a post I saw a few days ago.

http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_frm/thread/f58090fb59ef879f/a242e4eeb2ae00fd?lnk=gst&q=UPDLOCK&rnum=2#a242e4eeb2ae00fd

Before I go any further, let me mentions that this is 2005.

I'm trying to write the absolute most efficient query I can.

The (mock) scenario goes like this.

Imagine a HelpDesk, that answers tickets. When a help-desk-worker wants
more work, he/she alwasy gets 10 items at a time. ( The gurus have figured
out that 10 at a time makes everybody more efficient by doing 10 at a time )

There are ~~alot of HelpDesk workers, and they're all busy trying to get
HelpDeskTicket(s).

When they want 10 new items, I don't care about any "order by", I just want
them to get 10 HelpDesk tickets as fast/efficient as possible.

When a help-desker-worker gets the item, I want to mark the 10 records as
"Somebody got a Hold of them, don't give them to anyone else".

And I want to mark the HelpDeskTicket(item) with the uniqueid of the person
who got them.

At some point, the HelpDeskTicket will be completed, so I want to exclude
them also.

Here is the schema I've come up with for the mock scenario.

if exists (select * from sysobjects

where id = object_id('HelpDeskTicket'))

DROP TABLE HelpDeskTicket

CREATE TABLE dbo.HelpDeskTicket(

HelpDeskTicketID int IDENTITY (1,1) PRIMARY KEY CLUSTERED, --

CreateDate smalldatetime NULL default getdate() ,

SubjectID varchar(64) ,

ObjectID varchar(64) ,

IsCompleted bit not null default 0 ,

SomebodyAlreadyHasMe bit default 0 not null ,

WhoHasMe uniqueidentifier null

)

GO

I've put 1,000,000 records into this table in my test setup.

Here is the query I've come up with. I have not multi-threaded tested it,
I'm setting that up soon.

But I'd like anybody's opinion about the good/bad of it.

declare @newPersonUUID uniqueidentifier

select @newPersonUUID = NEWID() --<<just fake a UUID for the person

print @newPersonUUID

UPDATE dbo.HelpDeskTicket

SET

SomebodyAlreadyHasMe = 1 --<< bit value saying somebody got a hold of it

, WhoHasMe = @newPersonUUID --<<UUID of the person

FROM dbo.HelpDeskTicket wfi WITH ( ROWLOCK, UPDLOCK, READPAST )

JOIN

(

Select top 10 HelpDeskTicketID from dbo.HelpDeskTicket innerWfi WITH (
ROWLOCK, UPDLOCK, READPAST ) where

innerWfi.SomebodyAlreadyHasMe = 0 and innerWfi.WhoHasMe is null and
innerWfi.IsCompleted = 0

// notice that innerWfi has NO order by clause, because I know from
experience that will kill performance

)

as derived1

ON

derived1.HelpDeskTicketID = wfi.HelpDeskTicketID

I think its an interesting problem to think about.

The basic idea is that "I need 10 items, I need them fast, and I don't care
that much about them, except that somebody else hasn't grabbed them first".

THANKS for any input ............ This is going to be a life saver when I
get the best solution worked out.

Production numbers would probably have 5,000,000 "tickets", and 4000 or 5000
"help desk workers"....