SQL Server Performance Forum – Threads Archive

Dynamic TSQL and Cursor use vs ASP recordset

I’m trying to improve an ASP web application I did a while back by moving the queries and work that I did with ASP recordsets into Stored Procedures. I’m finding it quite annoying, but "bit by bit" it is working and performance is improving. However, I’m beginning to wonder if the lengths I am going through for the performance improvement is worth it. As simply as possible, the application is a vacation request database for about 1000 people. Approvals are based on seniority (eod) and date of vacation request. Currently, I do this in ASP by a big outer loop that cycles through every day in a period and generate a seperate recordset for each and every day. These recordsets are ordered the way I want and then I use another loop to cycle through the records and flag the first XX people as approved. It’s relatively slow this way. Maybe 60 seconds to do a month worth of approvals. Today I wrote the following procedure. It uses Dynamic SQL and a cursor – both of which I think are generally "avoid if you can" situations. This will order and approve only ONE day worth of folks. So I still have to loop through every day in the approval period in ASP and call this procedure for every day. I suppose my question is – is using the procedure better? Is there a better way to do this that I am missing?
@seid as char(5), –employee ID
@day_off as smalldatetime AS DECLARE @shift as char(1)
DECLARE @approved_off as int
DECLARE @counter as int
DECLARE @CMD nvarchar(1000) –PULL EMPLOYEE’S SHIFT
SELECT @shift = shift
FROM EMPLOYEE_INFORMATION
WHERE @seid = e_seid –SET UP A DYNAMIC SQL COMMAND
SET @CMD =
‘SELECT @approvalnum = (SELECT [‘[email protected]+’]
FROM LEAVE_ALLOWANCES
WHERE @vacation_request = calendar_date)’ –PULL NUMBER OF PEOPLE OFF ON THE DAY
exec sp_executesql
@cmd,
N’@approvalnum int out,
@vacation_request smalldatetime,
@shift char(1)’,
@approved_off out,
@day_off,
@shift –PULL AND ORDER VACATION REQUESTS FOR A DAY
–PUT THEM INTO A CURSOR
DECLARE my_cursor cursor
FOR
SELECT
VACATION_REQUESTS.FIFO_DATE,
EMPLOYEE_INFORMATION.E_EOD,
EMPLOYEE_INFORMATION.SHIFT,
EMPLOYEE_INFORMATION.E_NAME,
VACATION_REQUESTS.STATUS,
VACATION_REQUESTS.[EXCEPTION] FROM
EMPLOYEE_INFORMATION RIGHT JOIN VACATION_REQUESTS
ON EMPLOYEE_INFORMATION.E_SEID = VACATION_REQUESTS.E_SEID WHERE
@day_off = VACATION_REQUESTS.DAY_OFF
AND0 = VACATION_REQUESTS.[EXCEPTION]
[email protected] = EMPLOYEE_INFORMATION.SHIFT ORDER BY
VACATION_REQUESTS.FIFO_DATE,
EMPLOYEE_INFORMATION.E_EOD,
EMPLOYEE_INFORMATION.E_NAME OPEN my_cursor
DECLARE
@cur_fifo_date varchar(50),
@cur_e_eod varchar(50),
@cur_shift char(1),
@cur_e_name varchar(100),
@cur_status bit,
@cur_exception bit FETCH NEXT FROM my_cursor
INTO
@cur_fifo_date,
@cur_e_eod,
@cur_shift,
@cur_e_name,
@cur_status,
@cur_exception SET @counter = 1
WHILE (@@FETCH_STATUS <> -1)
BEGIN IF (@@FETCH_STATUS <> -2)
BEGIN
IF @counter <= @approved_off
BEGIN
UPDATE VACATION_REQUESTS
SET status = 1
WHERE
CURRENT OF my_cursor END
ELSE
BEGIN
UPDATE VACATION_REQUESTS
SET status = 0
WHERE
CURRENT OF my_cursor
END FETCH NEXT FROM MY_CURSOR
INTO
@cur_fifo_date,
@cur_e_eod,
@cur_shift,
@cur_e_name,
@cur_status,
@cur_exception
END
SET @counter = @counter + 1
END
CLOSE my_cursor
DEALLOCATE my_cursor RETURN

Why are you improving this particular bit?
Was the 60 seconds per day proving a problem? I think in most systems there are always bits that could perform better. Often time is better spent on things that perform ‘just ok’, but are executed often, rather than things that perform bad, but run very infrequently

Well, my explanation was perhaps over simplified. This is a living breathing application that constantly accepts new vacation requests and deleted requests. Even though there is an "intial order" of the requests as described above – I make it "live and breathe" by re-ordering all the approvals for a day and shift every time someone requests or deletes an additional day. So, the recordset looping, (or this procedure) gets called a fair amount (although generally for far less days than the intial ordering. I suppose what I really need to do is sit down and do some timed comparison tests.

"…I’m finding it quite annoying, but "bit by bit" it is working and performance is improving." "..It’s relatively slow this way. Maybe 60 seconds to do a month worth of approvals." If you think there is an issue, chances are, your clients/boss may feel the same. "I suppose my question is – is using the procedure better? Is there a better way to do this that I am missing?" Feeling tired and not sure if its worth it? If you’re boss and clients are happy, then probably its worth while to the business (hopefully, it will pay up during bonus periods <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />). Doing a ASP based Recordset vs. stored procedure in terms of performance, the SP is the winner because of less traffic and SQL optimization. A cursor based vs. set base transaction in a SP setting, the set base is the winner (except for rare cases). It seems your stored procedure cursor’s can be change to set base transaction. If you can explain the tables, assumptions, i.e. <pre id="code"> ’SELECT @approvalnum = (SELECT [‘[email protected]+’]FROM LEAVE_ALLOWANCES WHERE @vacation_request = calendar_date)’ </pre id="code"> what this query is returning and why, maybe we can provide the set base query. For now, <pre id="code"> UPDATE VACATION_REQUESTS SET status = CASE WHEN … THEN 1 ELSE 0 END FROMEMPLOYEE_INFORMATION RIGHT JOIN VACATION_REQUESTS ON EMPLOYEE_INFORMATION.E_SEID = VACATION_REQUESTS.E_SEID, … ANOTHER JOIN HERE [email protected]_off = VACATION_REQUESTS.DAY_OFF AND0 = VACATION_REQUESTS.[EXCEPTION] [email protected] = EMPLOYEE_INFORMATION.SHIFT </pre id="code"> Check the forum for more info on cursor vs. recordset. May the Almighty God bless us all! www.empoweredinformation.com

Thanks for the response. ‘SELECT @approvalnum = (SELECT [‘[email protected]+’]FROM LEAVE_ALLOWANCES WHERE @vacation_request = calendar_date)’ pulls the number of people that can be allowed of on a particular day for a particular shift. The PK is the date, and the shift name is the field – hence my having to use DSQL to pull it. And I’m not sure what you are trying to tell me to do in the last part. I have to order the query in order to flag approvals. So, if someones position on a day <= @approved_off then they get status = 1. Else they get Status = 0. I can’t see how to do that as you describe. For a while I tried to do UPDATE the TOP @approved_off rows of the query – but I couldn’t make it work right, and I still need to flag everything after the TOP @approved_off rows as status = 0 so I gave up that approach and went to a cursor.

to:cmdr_skywalker
he is correct

Now that you mentioned it, its starting to make sense what you’re trying to do. Let me see if I understood your problem correctly. Check the query below. The query choosed the boss first PositionPriority and then the CreationDate (This seems similar to your FIFO).
SET NOCOUNT ON
SET ROWCOUNT 0
DECLARE
@VACATION_REQUEST TABLE (
VacationRequestID INT identity(1,1),
EmpID INT,
RequestedDate DATETIME,
ApproveFlag BIT DEFAULT 0,
PositionPriority INT, — 0 – worker, 1 – manager
CreationDate DATETIME NOT NULL DEFAULT GETDATE()
)
DECLARE
@LEAVE_ALLOWANCES TABLE (
CalendarDate DATETIME,
MaxApprovedCount INT
) –populate sample data
INSERT INTO @VACATION_REQUEST(
EmpID,
RequestedDate,
ApproveFlag,
PositionPriority, –represent the condition of priority
CreationDate
)
SELECT 1, ‘2006-09-01’, 0, 0, ‘2006-01-01’
UNION ALL
SELECT 2, ‘2006-09-01’, 0, 0, ‘2006-02-01’
UNION ALL
SELECT 3, ‘2006-09-01’, 0, 1, ‘2006-03-01’
UNION ALL
SELECT 4, ‘2006-09-01’, 0, 1, ‘2006-04-01’
UNION ALL
SELECT 5, ‘2006-09-01’, 0, 0, GETDATE() INSERT INTO @LEAVE_ALLOWANCES(
CalendarDate,
MaxApprovedCount
)
VALUES (‘2006-09-01’, 3) –only three will be allowed DECLARE
@AppCount INT,
@DateOff DATETIME SET @DateOff = ‘2006-09-01’ SELECT @AppCount = ISNULL(MaxApprovedCount ,0)
FROM @LEAVE_ALLOWANCES
WHERE CalendarDate = @DateOff CREATE TABLE #Selected(
EmpID INT
) SET ROWCOUNT @AppCount INSERT INTO #SELECTED
SELECT TOP 100 EMPID
FROM @VACATION_REQUEST
WHERE
EMPID IN (
SELECT TOP 100 EMPID
FROM @VACATION_REQUEST
WHERE
REQUESTEDDATE = @DATEOFF
AND APPROVEFLAG = 0
GROUP BY EMPID
HAVING COUNT(*) <= @APPCOUNT
)
ORDER BY POSITIONPRIORITY DESC, CreationDate ASC –first request shall get priority if not boss PRINT ‘Before Update:’
SET ROWCOUNT 0
SELECT *
FROM @VACATION_REQUEST UPDATE V
SET ApproveFlag = 1
FROM
@VACATION_REQUEST V JOIN #SELECTED S ON (V.EmpID = S.EmpID) SET ROWCOUNT 0
PRINT ‘After Update:’
SELECT *
FROM @VACATION_REQUEST DROP TABLE #SELECTED
GO
Result:
Before Update:
VacationRequestID EmpID RequestedDate ApproveFlag PositionPriority CreationDate
—————– ———– —————————————————— ———– —————- ——————————————————
1 1 2006-09-01 00:00:00.000 0 0 2006-01-01 00:00:00.000
2 2 2006-09-01 00:00:00.000 0 0 2006-02-01 00:00:00.000
3 3 2006-09-01 00:00:00.000 0 1 2006-03-01 00:00:00.000
4 4 2006-09-01 00:00:00.000 0 1 2006-04-01 00:00:00.000
5 5 2006-09-01 00:00:00.000 0 0 2006-06-15 07:09:17.037 After Update:
VacationRequestID EmpID RequestedDate ApproveFlag PositionPriority CreationDate
—————– ———– —————————————————— ———– —————- ——————————————————
1 1 2006-09-01 00:00:00.000 1 0 2006-01-01 00:00:00.000
2 2 2006-09-01 00:00:00.000 0 0 2006-02-01 00:00:00.000
3 3 2006-09-01 00:00:00.000 1 1 2006-03-01 00:00:00.000
4 4 2006-09-01 00:00:00.000 1 1 2006-04-01 00:00:00.000
5 5 2006-09-01 00:00:00.000 0 0 2006-06-15 07:09:17.037

May the Almighty God bless us all!
www.empoweredinformation.com

I am running out of time but here the short explanation to help,
Correction:
The code:
SELECT TOP 100 EMPID
FROM @VACATION_REQUEST
WHERE
REQUESTEDDATE = @DATEOFF
AND APPROVEFLAG = 0
GROUP BY EMPID
HAVING COUNT(*) <= @APPCOUNT
should be:
SELECT TOP 100 EMPID
FROM @VACATION_REQUEST
WHERE
REQUESTEDDATE = @DATEOFF
AND APPROVEFLAG = 0
Now, to continue, set your target date to process and maximum allowed approvals:
DECLARE
@AppCount INT,
@DateOff DATETIME SET @DateOff = ‘2006-09-01’
SELECT @AppCount = ISNULL(MaxApprovedCount ,0)
FROM @LEAVE_ALLOWANCES
WHERE CalendarDate = @DateOff It is important that you set the limit, you can add the following query to consider what has already been approved:
SELECT @AppCount = @AppCount – ISNULL(COUNT(*),0)
FROM @VACATION_REQUEST
WHERE
REQUESTEDDATE = @DATEOFF IF (@AppCount < 0) RETURN
it is important that you set the limit: SET ROWCOUNT @AppCount the rest is all the select you need
INSERT INTO #SELECTED
SELECT TOP 100 EMPID
FROM @VACATION_REQUEST
WHERE
EMPID IN (
SELECT TOP 100 EMPID
FROM @VACATION_REQUEST
WHERE
REQUESTEDDATE = @DATEOFF
AND APPROVEFLAG = 0
)
ORDER BY
POSITIONPRIORITY DESC,
CreationDate ASC
Hope this helps. May the Almighty God bless us all!
www.empoweredinformation.com

]]>