Tune a SQL SP with cursors and dynamic SQL

Question

Post reply

Tune a SQL SP with cursors and dynamic SQL

dwg1011

SSC Rookie

Points: 45
More actions
May 5, 2011 at 8:50 pm

#249921

I have SP that runs rather slow. I am attaching the code to this post. The obvious comments are I should eliminate the cursors and change from dynamic SQL to static. These are both valid comments, but when I run the "generated" SQL it takes almost the same time as when the SP is directly invoked. I believe this is because the cursors return a small number of rows (say 15-20). Also, I need the dynamic SQL because all the databases are on the same server, so I don't want to have different versions of the SP for each environment. I am currently doing this for another app and it is a royal pain. I can't supply the DDL for some of the tables because they are vendor tables (CodCode, EmpComp, EmpPers, Company)
ALTER PROCEDURE [dbo].[TimeGridSelect]
-- Add the parameters for the stored procedure here
@CoID char(5),
@TspLocCode char(6),
@TspPayGroup char(6),
@DbName char(128) = 'Time',
@TspPeriodType char(1),
@TspPayDate datetime
AS
BEGIN
DECLARE @CommonQuerySelectFields varchar(7000)
DECLARE @CommonQueryRestOfQuery varchar(1000)
DECLARE @TspEarnCode char(5)
DECLARE @TecShiftCode char(2)
DECLARE @ErnLongDesc varchar(40)
DECLARE @CursorStatus int
DECLARE @CursorQuery varchar(8000)
DECLARE @QueryStringComp as nvarchar(4000)
DECLARE @CompParm as nvarchar(100)
DECLARE @CompName as char(50)
DECLARE @CompPrefix as char(50)
DECLARE @CompPrefixLen as int
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Get the company prefix
SELECT @QueryStringComp = N'SELECT @CompPrefix = CtsPrefix
FROM dbo.TimeCompanySheet
WHERE CtsCoID = ''' + @CoID + ''''
SELECT @CompParm = N'@CompPrefix char(50) OUTPUT'
--SELECT @CompParm
--SELECT @QueryStringComp
EXEC sp_executesql @QueryStringComp,@CompParm,@CompPrefix = @CompName OUTPUT
-- Length of prefix
SELECT @CompPrefixLen = LEN(RTRIM(@CompName)) + 1
-- GET Common Fields for every client
IF LEN(@CompPrefixLen) > 0
BEGIN
SELECT @CommonQuerySelectFields = 'SELECT DISTINCT '''' AS [Insert],'''' AS [Delete],EecEmpNo AS [EmployeeNo],
RTRIM(EepNameLast) + '', '' + RTRIM(EepNameFirst) + '' '' + SUBSTRING(ISNULL(EepNameMiddle,''''), 1, 1)
AS Name, CmpCompanyCode AS [Company Code], TspLocCode as [Location],
TspPayGroup as [PayGroup], TspPayDate as [PayDate],
CASE
WHEN RTRIM(TspCode) = ''0'' THEN ''0 - Select a Project''
ELSE SUBSTRING(RTRIM(TspCode),' + CAST(@CompPrefixLen as char(1)) +
',LEN(RTRIM(TspCode)) - ' + CAST(@CompPrefixLen - 1 as varchar(2)) +
') + '' - '' + RTRIM(CodDesc)
END as [Project],
TspCode as [OldProject] '
END
ELSE
BEGIN
SELECT @CommonQuerySelectFields = 'SELECT DISTINCT '''' AS [Insert],'''' AS [Delete],EecEmpNo AS [EmployeeNo],
RTRIM(EepNameLast) + '', '' + RTRIM(EepNameFirst) + '' '' + SUBSTRING(ISNULL(EepNameMiddle,''''), 1, 1)
AS Name, CmpCompanyCode AS [Company Code], TspLocCode as [Location],
TspPayGroup as [PayGroup], TspPayDate as [PayDate],
''0 - Select a Project'' as [Project],
TspCode as [OldProject] '
END
--SELECT @CommonQuerySelectFields
SELECT @CommonQueryRestofQuery = '
FROM dbo.TimeSheetPayPeriod TSP
INNER JOIN ' + RTRIM(@DbName) + '.dbo.Codes C
ON (RTRIM(TspCode) = RTRIM(CodCode))
INNER JOIN ' + RTRIM(@DbName) + '.dbo.EmpComp EC
ON (TspCoID = EecCoID)
AND (TspPayGroup = EecPayGroup)
AND (TspEEID = EecEEID)
AND (TspPayDate = ''' + cast(@TspPayDate as varchar(30)) + ''')
INNER JOIN ' + RTRIM(@DbName) + '.dbo.EmpPers
ON EecEEID = EepEEID
INNER JOIN ' + RTRIM(@DbName) + '.dbo.Company
ON EecCoID = CmpCoID
WHERE ((EecEmplStatus = ''A'')
OR (EecEmplStatus = ''T'' AND EecDateOfTermination >= ''' +
cast(@TspPayDate as varchar(30)) + '''))
AND (EecCoID = ''' + @CoID + ''')
AND (TspPeriodType = ''' + @TspPeriodType + ''')
AND CodTable = ''PROJECT'' '
IF @TspLocCode <> 'ALL '
SELECT @CommonQueryRestofQuery = @CommonQueryRestofQuery + '
AND (TspLocCode = ''' + @TspLocCode + ''') '
IF @TspPayGroup <> 'ALL '
SELECT @CommonQueryRestofQuery = @CommonQueryRestofQuery + '
AND (TspPayGroup = ''' + @TspPayGroup + ''') '
SELECT @CommonQueryRestofQuery = @CommonQueryRestofQuery + '
AND (TspPayDate = ''' + cast(@TspPayDate as varchar(50)) + ''')
AND (RTRIM(EepNameLast) + '', '' + RTRIM(EepNameFirst) IS NOT NULL)
ORDER BY NAME'
-- Cursor processing for each deduction
SELECT @CursorQuery = '
DECLARE Deductions_Cursor CURSOR GLOBAL SCROLL FOR
SELECT TecEarnCode,TecShiftCode,ErnLongDesc
FROM dbo.TimeEarnCodes
INNER JOIN ' + RTRIM(@DbName) + '.dbo.EarnCode
ON TecEarnCode = ErnEarnCode
WHERE TecCoID = ''' + @CoID + ''''
IF @TspLocCode <> 'ALL '
SELECT @CursorQuery = @CursorQuery + '
AND TecLocCode = ''' + @TspLocCode + ''''
IF @TspPayGroup <> 'ALL '
SELECT @CursorQuery = @CursorQuery + '
AND TecPayGroup = ''' + @TspPayGroup + ''''
SELECT @CursorQuery = @CursorQuery + '
AND TecIsActive = ''Y''
ORDER BY TecEarnSequence'
--SELECT @CursorQuery
EXECUTE (@CursorQuery)
OPEN Deductions_Cursor
FETCH NEXT FROM Deductions_Cursor INTO @TspEarnCode,@TecShiftCode,@ErnLongDesc
SELECT @CursorStatus = @@FETCH_STATUS
-- Check @CursorStatus to see if there are any more rows to fetch.
WHILE @CursorStatus = 0
BEGIN
-- Build Query Cursor (Z shift code -- No shift differential -- check)
IF @TecShiftCode = 'Z '
BEGIN
SELECT @CommonQuerySelectFields = @CommonQuerySelectFields + ',' +
'(SELECT TspEarnAmount FROM dbo.TimeSheetPayPeriod ' +
'INNER JOIN ' + RTRIM(@DbName) + '.dbo.Codes c
ON (RTRIM(TSP.TspCode) = RTRIM(CodCode))
WHERE TspCoID = ''' + @CoID + ''' '
IF @TspLocCode <> 'ALL '
BEGIN
SELECT @CommonQuerySelectFields = @CommonQuerySelectFields +
'AND TspLocCode = ''' + @TspLocCode + ''' '
END
IF @TspPayGroup <> 'ALL '
BEGIN
SELECT @CommonQuerySelectFields = @CommonQuerySelectFields +
'AND TspPayGroup = ''' + @TspPayGroup + ''' '
END
SELECT @CommonQuerySelectFields = @CommonQuerySelectFields +
'AND CodTable = ''PROJECT'' ' +
'AND TspCode = TSP.TspCode ' +
'AND TspEEID = EC.EecEEID ' +
'AND TspPeriodType = TSP.TspPeriodType ' +
'AND TspPayDate = ''' + cast(@TspPayDate as varchar(50)) + ''' ' +
'AND TspEarnCode = ''' + @TspEarnCode + ''') AS [' +
SUBSTRING(@ErnLongDesc,1,20) + ']'
END
ELSE
BEGIN
SELECT @CommonQuerySelectFields = @CommonQuerySelectFields + ',' +
'(SELECT TspEarnAmount FROM dbo.TimeSheetPayPeriod ' +
'INNER JOIN ' + RTRIM(@DbName) + '.dbo.Codes
ON (RTRIM(TSP.TspCode) = RTRIM(CodCode)) ' +
'WHERE TspCoID = ''' + @CoID + ''' ' +
'AND CodTable = ''PROJECT'' ' +
'AND TspCode = TSP.TspCode ' +
'AND TspLocCode = ''' + @TspLocCode + ''' ' +
'AND TspPayGroup = ''' + @TspPayGroup + ''' ' +
'AND TspPeriodType = ''' + @TspPeriodType + ''' ' +
'AND TspEEID = EC.EecEEID ' +
'AND TspPayDate = ''' + cast(@TspPayDate as varchar(50)) + ''' ' +
'AND TspEarnCode = ''' + @TspEarnCode + ''') AS [' +
SUBSTRING(@ErnLongDesc,1,17) + '_' + @TecShiftCode + ']'
END
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM Deductions_Cursor INTO @TspEarnCode,@TecShiftCode,@ErnLongDesc
SELECT @CursorStatus = @@FETCH_STATUS
--SELECT @CommonQuerySelectFields
END
CLOSE Deductions_Cursor
-- Add Total Hours column
SELECT @CommonQUerySelectFields = @CommonQuerySelectFields + ','''' as [Total Hours] '
--SELECT cast(@CommonQuerySelectFields + @CommonQueryRestofQuery as varchar(4000))
EXECUTE (@CommonQuerySelectFields + @CommonQueryRestofQuery)
-- Reference the named cursor with a cursor variable.
DEALLOCATE Deductions_Cursor

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

There are a couple of things that might help:

1. You are using functions on columns in the where clause which means they are not SARGable so you are getting scans.

2. You are using an OR which tends to cause scans as well.

3. Your CURSOR is even less efficient than is has to be. You don't need a scrollable cursor because you are only going forward (FETCH NEXT) so if you use a FAST_FORWARD cursor you should get better performance

4. It also looks like that your cursor is added a correlated sub-query to the select list. This is essentially another cursor. If you can find another way to do that without the corrlated sub-query it will likely run faster as well. I've found that a CTE or derived table can often replace correlated sub-queries and perform better.

Without structures and query plans it's hard to give any more specific recommendations.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

dwg1011 SSC Rookie Points: 45 More actions · Answer 2

Jack,

Thanks for getting back to me. As for your comments/recommendations

1. You are using functions on columns in the where clause which means they are not SARGable so you are getting scans.

2. You are using an OR which tends to cause scans as well.

These comments make perfect sense, but do you have any recommendations on how I can get around this.

3. Your CURSOR is even less efficient than is has to be. You don't need a scrollable cursor because you are only going forward (FETCH NEXT) so if you use a FAST_FORWARD cursor you should get better performance

I will implement this.

4. It also looks like that your cursor is added a correlated sub-query to the select list. This is essentially another cursor. If you can find another way to do that without the correlated sub-query it will likely run faster as well. I've found that a CTE or derived table can often replace correlated subqueries and perform better.

Since I have an undetermined number of columns I don't think I can use either a CTE or derived table. Let me describe why I did it this way and see if we can come up with a better solution. For each client in the database there is variable number of earnings codes. Rather than have a de-normalized design, I have one row for each client's compound key for each earnings type. When I call the SP I need to return all the earnings types for each employee and load it into a FarPoint spread control.

Would a temp table predefined with the "correct" number of columns for the client that gets populated one earnings type at a time be faster than a correlated sub-query?

I am posting the "generated" code so you can see what the procedure produces.

SELECT DISTINCT '' AS [Insert],'' AS [Delete],EecEmpNo AS [EmployeeNo],

RTRIM(EepNameLast) + ', ' + RTRIM(EepNameFirst) + ' ' + SUBSTRING(ISNULL(EepNameMiddle,''), 1, 1)

AS Name, CmpCompanyCode AS [Company Code], TspLocCode as [Location],

TspPayGroup as [PayGroup], TspPayDate as [PayDate],

CASE

WHEN RTRIM(TspCode) = '0' THEN '0 - Select a Project'

ELSE SUBSTRING(RTRIM(TspCode),1,LEN(RTRIM(TspCode)) - 0) + ' - ' + RTRIM(CodDesc)

END as [Project],

TspCode as [OldProject] ,(SELECT TspEarnAmount FROM dbo.TimeSheetPayPeriod INNER JOIN dbo.Codes c