SQL Server Performance

queuing records - ensuring one record per user

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by cbtoolkit, Dec 8, 2004.

  1. Primotack New Member

    I'm just getting into something very similar to this. I have a call center of about 200 users pulling from a single call list. When they pull the account they will work it just submitting a note.

    This process does exactly what I need it to and is pretty simple. It pulls a single account updates a cell in the table that says if the accoutn is currently in a "working" status.

    ALTER PROCEDURE db:confused:utboundQue

    (
    @type int,
    @repid varchar(10)
    )

    AS
    declare @id as bigint
    declare @noticedate as Numeric(8,0)
    declare @env as varchar(2)
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
    BEGIN TRANSACTION L1


    DECLARE @LockResult int
    set @lockresult=1
    while(@lockresult<>0)
    Begin
    EXECUTE @LockResult = sp_getapplock
    @Resource = 'RepeatableRead_TRANSACTION',
    @LockMode = 'Exclusive',
    @LockTimeout = 0
    IF @LockResult <> 0
    BEGIN
    WAITFOR DELAY '00:00:01'
    END
    End

    if(@type=1)
    Begin
    Update CallList
    set Working=1, @id=id, @noticedate=Noticedate, @env=env, LAPulltype='A'
    where ID=(
    Select top(1) id
    From CallList U2
    Where U2.Working=0 and U2.Worked=0 and region<>3 and ExcludeReason is null and U2.Timezone in (select Timezone from OutboundCallableTimeZones where datepart(hour,getdate()) between starthour and closehour)
    Order by NoticeManager, Priority1 desc, NoticeDue asc,Priority2 desc, NoticeAmt desc
    )
    Insert into WorkHistory (id, BTN, NoticeDate, Rep, AccessTime)
    Select id, BTN, Noticedate, @repid, getdate() from CallList where id=@id
    End
    if(@type=2)
    Begin
    Update CallList
    set Working=1, @id=id, @noticedate=Noticedate, @env=env, LAPulltype='A'
    where ID=(
    Select top(1) id
    From CallList U2
    Where U2.Working=0 and U2.Worked=0 and region=3 and ExcludeReason is null and U2.Timezone in (select Timezone from OutboundCallableTimeZones where datepart(hour,getdate()) between starthour and closehour)
    Order by NoticeManager, Priority1 desc, NoticeDue asc,Priority2 desc, NoticeAmt desc
    )
    Insert into WorkHistory (id, BTN, NoticeDate, Rep, AccessTime)
    Select id, BTN, Noticedate, @repid, getdate() from CallList where id=@id
    End
    EXECUTE sp_releaseapplock
    @Resource = 'RepeatableRead_TRANSACTION'

    IF @@ERROR<>0 ROLLBACK TRANSACTION L1 ELSE COMMIT TRANSACTION L1
    if(@id is null)
    begin
    return
    end
    Select * from CallList where id=@id
  2. Luis Martin Moderator

    Welcome to the forums!.
    Even this thread is 8 years old, thanks for your collaboration.

Share This Page