February 13, 2014 at 8:04 am
I'm writing a stored procedure with a fairly complex SQL statement that I've converted to Dynamic SQL due to the number and nature of parameters being passed. If I run the procedure and just print the resulting SQL query to the screen, I can add parameter values and it runs in < 10 seconds, but when I run the proc and actually run the dynamic SQL it takes ~35 minutes to generate the full data set. Anyone have any hints on how to improve performance on Dynamic SQL?
February 13, 2014 at 8:12 am
I'm guessing it's going to be an issue with parameter sniffing
How are you running the dynamic SQL? Adding your values to a string and then using
EXEC(@SQL)
or a parameterized query like:
EXECUTE sp_executesql @sql,
N'@_Parameter1 INT',@_Parameter1 = @Parameter1;
Also, how many values potentially are you using and do some of them have a wide range? Dates for example?
February 13, 2014 at 8:16 am
With so little information given, you could check this article: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
February 13, 2014 at 8:55 am
Here are the parameters that I'm passing, and I'm using the two Date parameters in multiple places inside the dynamic SQL statement.
SET @ParmDefinition = N'@DateStart Date,' + @NewLine
+ '@DateEnd Date, ' + @NewLine
+ '@Agency varchar(5),' + @NewLine
+ '@PeriodID nvarchar(10)' + @NewLine
+ '@Department nvarchar(20) ' + @NewLine
+ '@Worker bigint'
EXECUTE sp_executesql @sql , @ParmDefinition,
@DateStart = @DateStart,
@Agency = @Agency,
@DateEnd = @DateEnd,
@PeriodID = @PeriodID,
@Department = @Department,
@Worker = @Worker
February 13, 2014 at 9:01 am
Not sure it will help, but here's the full query...
ALTER PROC NA001MissingTimeReport @DateStart Date, @DateEnd Date, @PeriodID nvarchar(10), @Agency nvarchar(5), @Department nvarchar(20), @Worker bigint = 0,
@AccrualFormat bit, @IncludeFreelancers bit, @MissingTimeOnly bit
AS
DECLARE @sql nvarchar(max), @WhereClause nvarchar(max), @ParmDefinition nvarchar(max), @NewLine nvarchar(max)
SET @NewLine = CHAR(13) + CHAR(10)
SET @WhereClause = ''
IF @AccrualFormat = 1
BEGIN
SELECT @Agency = '', @Department = '', @Worker = 0, @IncludeFreelancers = 0, @MissingTimeOnly = 1
END
SELECT @sql =
N'SELECT UPPER(u.COMPANY) PRIMARYCOMPANY, c.DATAAREA AGENCY, ISNULL(dd.DEPARTMENT, ''****'') DEPARTMENT, ''"'' + n.LASTNAME + '', '' + n.FIRSTNAME +
CASE n.MIDDLENAME WHEN '''' then ''"'' ELSE +SPACE(2) + MIDDLENAME + ''"'' END NAME,
pws.PERIODID, CONVERT(Date, ppl.PERIODFROM, 101) PERIODFROM, CONVERT(Date, ppl.PERIODTO, 101) PERIODTO,
CONVERT(Date, d.GREGORIANDATE , 101) GREGORIANDATE,
CASE DATEPART(dw, d.GREGORIANDATE) WHEN 2 THEN ''Mon'' WHEN 3 THEN ''Tue'' WHEN 4 THEN ''Wed''
WHEN 5 THEN ''Thu'' WHEN 6 THEN ''Fri''
ELSE CAST(DATEPART(dw, d.GREGORIANDATE) as nvarchar) END WEEKDAY,
SUM(ISNULL(ts.HOURS, 0)) HOURS, 8-SUM(ISNULL(ts.hours, 0)) MISSINGHOURS
FROM HCMWORKER hrw
JOIN DIRPERSONNAME n on hrw.PERSON = n.PERSON
JOIN HCMEMPLOYMENT e on hrw.RECID = e.WORKER
JOIN COMPANYINFO c on e.LEGALENTITY = c.RECID
JOIN DIRPERSONUSER p ON p.PERSONPARTY = hrw.PERSON
JOIN USERINFO u on p.USER_ = u.ID
JOIN PROJWORKERSETUP pws on c.DATAAREA = pws.PERIODDATAAREAID AND hrw.RECID = pws.WORKER
JOIN PROJPERIODEMPL ppe on pws.PERIODDATAAREAID = ppe.DATAAREAID AND hrw.RECID = ppe.WORKER
JOIN PROJPERIODLINE ppl on ppe.DATAAREAID = ppl.DATAAREAID AND ppe.PERIODID = ppl.PERIODID AND ppe.PERIODFROM = ppl.PERIODFROM
JOIN FISCALCALENDARDATE d on d.GREGORIANDATE >= ppl.PERIODFROM AND d.GREGORIANDATE <= ppl.PERIODTO
LEFT OUTER JOIN (SELECT d.DEFAULTDIMENSION, d.DISPLAYVALUE DEPARTMENT from DEFAULTDIMENSIONVIEW d
JOIN DIMENSIONATTRIBUTE a on d.DIMENSIONATTRIBUTEID = a.RECID
where a.NAME = ''Department'') dd on e.DEFAULTDIMENSION = dd.DEFAULTDIMENSION
LEFT OUTER JOIN (SELECT l.DATAAREAID, l.WORKER, l.LINEPROPERTYID, w.DAYFROM, ts.APPROVALSTATUS, t.PROJTRANSDATE, SUM(t.HOURS) HOURS
from TSTIMESHEETTRANS t
JOIN TSTIMESHEETLINEWEEK w on t.TSTIMESHEETLINEWEEK = w.RECID
JOIN TSTIMESHEETLINE l on w.DATAAREAID = l.DATAAREAID AND w.TSTIMESHEETLINE = l.RECID
JOIN TSTIMESHEETTABLE ts on t.DATAAREAID = ts.DATAAREAID AND t.TIMESHEETNBR = ts.TIMESHEETNBR
WHERE t.APPROVALSTATUS = 6
GROUP BY l.DATAAREAID, l.WORKER, l.LINEPROPERTYID, w.DAYFROM, ts.APPROVALSTATUS,
t.PROJTRANSDATE) ts
ON ts.WORKER = hrw.RECID AND ppe.PERIODFROM = ts.DAYFROM AND d.GREGORIANDATE = ts.PROJTRANSDATE '
SELECT @WhereClause = N'WHERE e.VALIDTO >= @DateStart AND pws.PERIODID = @PeriodID
AND ppl.PERIODFROM >= @DateStart AND ppl.PERIODTO <= @DateEnd
AND d.GREGORIANDATE <= e.VALIDTO AND d.GREGORIANDATE >= e.VALIDFROM
AND n.VALIDTO >= GeTdate() AND DATEPART(dw, d.GREGORIANDATE) <> 1 AND DATEPART(dw, d.GREGORIANDATE) <> 7'
IF @Agency <> ''
BEGIN
SELECT @WhereClause = @WhereClause + @NewLine + N'AND c.DATAAREA = @Agency'
END
IF @AccrualFormat = 1
BEGIN
SELECT @WhereClause = @WhereClause + @NewLine + N'AND u.COMPANY <> ''CDGP'' AND u.COMPANY <> ''MEDI'''
END
ELSE
BEGIN
SELECT @WhereClause = @WhereClause + @NewLine + N'AND (u.COMPANY <> ''CDGP'' OR (u.COMPANY = ''CDGP'' AND c.DATAAREA = ''CDGP''))'
END
IF @IncludeFreelancers = 0
BEGIN
SELECT @WhereClause = @WhereClause + @NewLine + N'AND n.MIDDLENAME NOT LIKE ''(_)'''
END
IF @Department <> ''
BEGIN
SELECT @WhereClause = @WhereClause + @NewLine + N'AND dd.DEPARTMENT LIKE @Department'
END
IF @Worker <> 0
BEGIN
SELECT @WhereClause = @WhereClause + @NewLine + N'AND hrw.RECID = @Worker'
END
SELECT @WhereClause = @WhereClause + @NewLine + N'GROUP BY c.DATAAREA, dd.DEPARTMENT, pws.PERIODID, n.LASTNAME, n.FIRSTNAME, n.MIDDLENAME,' + @NewLine +
'ppl.PERIODFROM, ppl.PERIODTO, d.GREGORIANDATE, u.COMPANY'
IF @MissingTimeOnly = 1
BEGIN
SELECT @WhereClause = @WhereClause + @NewLine + N'HAVING 8-SUM(ISNULL(ts.hours, 0)) > 0'
END
SELECT @WhereClause = @WhereClause + @NewLine + N'ORDER BY c.DATAAREA, dd.DEPARTMENT, NAME, ppl.PERIODFROM, ppl.PERIODTO, d.GREGORIANDATE'
SELECT @sql = @sql + @NewLine + @WhereClause
SET @ParmDefinition = N'@DateStart Date,' + @NewLine
+ '@DateEnd Date, ' + @NewLine
+ '@Agency varchar(5),' + @NewLine
+ '@PeriodID nvarchar(10)' + @NewLine
+ '@Department nvarchar(20) ' + @NewLine
+ '@Worker bigint'
EXECUTE sp_executesql @sql , @ParmDefinition,
@DateStart = @DateStart,
@Agency = @Agency,
@DateEnd = @DateEnd,
@PeriodID = @PeriodID,
@Department = @Department,
@Worker = @Worker
February 13, 2014 at 9:04 am
If you're using a lot of dates where the range can vary from say days to weeks/months/years it's likely the plan being used isn't optimal if the parameters being passed have changed significantly.
Out of interest have you tried using any HINTS?
OPTION (RECOMPILE)
OPTION (OPTIMIZE FOR UNKNOWN)
OPTION (OPTIMIZE FOR (@Parameter1 UNKNOWN))
February 13, 2014 at 11:56 am
Ok, that's my first 'Duh' moment of the day... I used a combination of the 'Optimize For' and 'Recompile' hints and got the execution time back down to < 2 minutes.
Thanks for the help, it is much appreciated!
February 14, 2014 at 4:37 am
This stuff is likely killing your performance too. It can't use indexes
DATEPART(dw, d.GREGORIANDATE) <> 1 AND DATEPART(dw, d.GREGORIANDATE) <> 7'
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply