January 28, 2016 at 12:29 pm
Hello All,
There is a sporadic performance problem with a batch process (nested sprocs) in SQL Server 2012. Sometimes, it takes much longer than usual.
The process rebuilds certain tables based on input parameters. RULE table has a statement column that has 70+ different statements (involving 20+ different columns) to be used in the WHERE clause in dynamic sql.
So, the DELETE, at each process run, not only has different parameters, but also different types and number of columns in the WHERE clause.
What would you recommend other than managing stats and index tuning? The dev team is open to code and schema changes.
SELECT rul.SQL_STATEMENT
FROM APP_RULE rul
LEFT JOIN APP_RULE_EXCEPTION exc
ON rul.RULE_ID = exc.RULE_ID
WHERE rul.APP_ID = @AppId
AND (exc.RULE_ID IS NULL OR exc.RULE_ID NOT IN (
SELECT RULE_ID FROM APP_RULE_EXCEPTION ))
SET @SQLStatement =
'DELETE FROM EMPLOYEE ' +
'WHERE APP_ID = ' + CAST(@AppId AS VARCHAR(10)) +
' AND EMPLOYEE_ID NOT IN (' +
'SELECT EMPLOYEE_ID FROM EMPLOYEE_NEW ' +
'WHERE '+ @SQLStatement + ')'
EXEC (@SQLStatement)
SET @SQLStatement =
'DELETE FROM ' + @TableName + ' ' +
'WHERE EMPLOYEE_ID NOT IN (' +
'SELECT EMPLOYEE_ID FROM EMPLOYEE_STG ' +
'WHERE APP_ID = ' + CAST(@AppId AS VARCHAR(10)) +''')'
EXEC (@SQLStatement)
SET @SQLStatement =
'INSERT INTO ' + @DestinationTable + ' '+'( [APP_ID], ' + @AttributeList + ')'+
'SELECT ' + CAST(@AppId AS VARCHAR(10)) + ',''' + @ExposedAttributeList +
'FROM ' + @SourceTable + ' ' +
'WHERE [DATE] = ''' + @TDate + ''''
EXEC (@SQLStatement)
The following are two sample DELETEs generated by dynamic sql.
DELETE FROM EMPLOYEE WHERE APP_ID = 103 AND APP_TYPE = 'IE' AND EMPLOYEE_ID NOT IN (
SELECT EMPLOYEE_ID FROM EMPLOYEE_NEW WHERE APP_TYPE = 'IE' )
DELETE FROM EMPLOYEE WHERE APP_ID = 103 AND APP_TYPE = 'IE' AND COUNTRY='USA' AND EMPLOYEE_ID NOT IN (
SELECT EMPLOYEE_ID FROM EMPLOYEE_NEW WHERE APP_TYPE = 'IE' AND EMPLOYEE_ID IN (
SELECT EMPLOYEE_ID FROM EMPLOYEE_EMAIL WHERE ISNULL(EMAIL_ADDRESS,'')<>'' and EType='OFFICE' ))
Thanks,
Kuzey
January 28, 2016 at 7:19 pm
Looks like a custom rules engine implemented in T-SQL. I inherited one such system and watched another one get commissioned. I was late to the party in both cases and maintained the first one for a year for a telecom and tried steering folks away from the second one but it was produced anyway (I left that party shortly after). But I digress...
It is hard to see how the conditions cascade through your code from the small sample. It may help your specific performance issue, it may not, but it could! Either way I think it is a smart move for your system to convert all the statements that execute dynamic SQL to use sp_executesql instead of EXEC(). You'll get better data-type choices during parameterization and give the optimizer the best chance possible of picking a good plan.
An example of how to port the calls, instead of this from your sample:
SET @SQLStatement =
'DELETE FROM EMPLOYEE ' +
'WHERE APP_ID = ' + CAST(@AppId AS VARCHAR(10)) +
' AND EMPLOYEE_ID NOT IN (' +
'SELECT EMPLOYEE_ID FROM EMPLOYEE_NEW ' +
'WHERE '+ @SQLStatement + ')'
EXEC (@SQLStatement)
This:
SET @SQLStatement =
'DELETE FROM EMPLOYEE ' +
'WHERE APP_ID = @AppIdParameter' +
' AND EMPLOYEE_ID NOT IN (' +
'SELECT EMPLOYEE_ID FROM EMPLOYEE_NEW ' +
'WHERE '+ @SQLStatement + ')'
EXEC sys.sp_executesql
@SQLStatement,
'@AppIdParameter INT', -- assumption this should be an INT
@AppIdParameter = @AppId;
Adding a "parameter" suffix is not something I do in my own code because I am used to the syntax but I wanted you to be able to differentiate the parameter from your local variable in my example.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 28, 2016 at 7:25 pm
Sorry, I neglected to speak to your larger question of getting away from dynamic SQL. it looks like you have SQL fragments or conditions sitting in tables, like this
SELECT rul.SQL_STATEMENT
FROM APP_RULE rul
LEFT JOIN APP_RULE_EXCEPTION exc
ON rul.RULE_ID = exc.RULE_ID
that you are bringing into variables and string for later execution. If that is how the system was designed to work then dynamic SQL is your only option unless you do a complete refactor of the data model to store rules differently and rewrite all the SQL to move everything to a declarative code-model.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply