Here is a list of things to keep in mind when converting dynamic tsql stored procedures into a static version (for performance enhancement).
1) Remove quotes (carefully). - Going through and eliminated the outside quotes is the easy part. You do have to be careful of the single inner quotes, for example a single quote(s) might be necessary in a varchar statement.
2) Look over the convert statements. - A majority of the convert statements, especially the converting to varchar will need to be looked over and possibly removed in most cases.
3) Replace variables that dealt with parsing. - Some stored procedures might be sending in multiple data into one variable for example @Y = ('1,2,3,4,5') and being accessed like: WHERE x in @Y. This will need to be replaced with either a PATINDEX or CHARINDEX.
4) Remove unnecessary declarations - Some variables are going to end up not being used now, for example the varchar variable that was used to make the dynamic tsql string.
These are the tips, I might remember some more and add them later.
2 comments:
Thanks. This was very helpful
Husain
Also don't forget to check out a previous post I made for some more hints:
http://andrusdevelopment.blogspot.com/2007/07/changing-dynamic-sql-to-static-sql.html
Post a Comment