November 6, 2002 at 12:08 pm
The powers that be have mandated that I use dynamic SQL in all stored procedures I write. I thought this was not a good thing...even with sp_executesql. Does anyone have a good link/idea on the pros/cons to using dynamic sql. I am looking for hard evidence to discourage the powers that be......unless I am off base.
Thanks for your help. ajroney
November 6, 2002 at 12:40 pm
Look at the articles on Dynamic SQL by Robert Marda on this site:
http://www.sqlservercentral.com/columnists/rmarda/
Also take a look at MVP Vyas Kondreddi's article on SQL Server security. He talks a bit about dynamic SQL:
http://vyaskn.tripod.com/sql_server_security_best_practices.htm
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
Edited by - bkelley on 11/06/2002 12:40:41 PM
K. Brian Kelley
@kbriankelley
November 6, 2002 at 2:11 pm
Thanks bkelley. I checked out the sites, and got some good ideas on how to avoid using dynamic sql. However, I am still unable to find hard evidence that proves my thought that dynamic sql is a performance degrader.
In the conclusion section of one of Robert Marda's articles he states, "When performance and speed are a top priority then you should try to avoid dynamic SQL as much as possible."
What I am trying to find out is why performance and speed are degraded? I know using the "exec" command versus the "sp_executesql" system procedure may cause recompilation every time the procedure is called. So, in my mind, as long as I always use sp_executesql, I won't cause recompilations (unless I explicitly force one by something else I am doing in the code).
If anyone has any links that talk about how dynamic sql degrades performance, that would be a great help. Thanks in advance! ajroney
November 6, 2002 at 2:13 pm
PS - I should also note that table object level security in the application I am working with is not an issue. Our users connecting the database usually have access to all objects.
Therefore, the negative security implications associated with dynamic sql do not apply.
Thanks, ajroney
November 6, 2002 at 3:43 pm
Dynamic SQL is slower due to the fact that the execution plans cannot be re-used. Even with sp_executesql, the execution plan can be re-used if the query stays the same, except for the params. Keep in mind, it must also parse the dynamic SQL each time. Now if you are only changing the parameters each time, then I don't see any reason to use dynamic SQL at all, since that functionality is already covered in a regular stored proc.
You are right though...if the only thing changing are the params, and you use sp_executesql, then there is little performance degradation.
Con-Dynamic SQL is harder to maintain...in my opinion.
-Dan
-Dan
November 6, 2002 at 7:18 pm
You won't cause recompilations in the stored procedures if you use sp_executesql. However, as dj_meier indicates, you may get recompiles on the dynamic SQL itself unless it's a parameterized query.
If that's the case, depending on your memory, you could be taking a performance hit if ad hoc queries (even parameterized ones) get flushed out of cache rapidly. Keep in mind, when a stored procedure gets put in the cache, it gets inserted with its full cost. An ad hoc query gets inserted with a zero cost and as its reused, the cost is incremented until it reaches its full value. The lazywriter will therefore eliminate ad hoc queries faster than stored procedures unless the ad hoc queries are used constantly. A little more on stored procedures and caching:
http://www.sqlservercentral.com/columnists/bkelley/procedurecache.asp
Also, keep in mind that the check on security due to dynamic SQL, no matter how slight, does cost you something. If no check is needed, there is no cost.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
November 6, 2002 at 9:05 pm
Good discussion. Is there a reason they are mandating the dynamic sql? If you're doing all dynamic sql is there any gain from doing it in a proc vs doing it in the app itself? Procs loosen the coupling some, but aside from that? On the other hand, if you're expecting to be able to parameterize and get decent re-use, why not skip the dynamic sql in cases where it's not needed?
Andy
November 6, 2002 at 9:58 pm
The root of the dynamic SQL comes from the avoidance or concurrency.
The reason for the dynamic sql mandate comes for the fact that mulitple users may be updating distinct attributes of a record concurrently. Therefore, the thought process is if you can have a stored procedure that accepts a @tablename and a @columnname, then 2 users can update two different columns without overwriting the other's changes.
For example, we have a table called myTable with two columns, col1 and col2. User 1 reads a record, and wants to update col1 with a new value. In the meantime, another user, User 2 reads the record before the update. User two wants to update col2. User 2 has read the record before user 1 has committed the update. Therefore, user 1 makes his update, and user 2 makes an update after user 1 had made his update, thus wiping out user 1's update. This example assumes that a stored procedure would update all columns in a table, therefore causing the "last one in wins" scenario.
The thought was to use dynamic sql to only update the column in question.....not sure if there are other ways around this, and I would love to hear more from the community out there...
November 7, 2002 at 5:08 am
One way would be to add a timestamp to the table, only do the update if the timestamp was the same. If changed, then you'd have to do conflict resolution by comparing each column. You could do this in the proc by sending over both old and new values. ADO supports updates based on the timestamp so if you want dynamic sql it will generate it for you.
Andy
November 7, 2002 at 7:41 am
ajroney brought up an intersting sub-topic.
If 2 users request the same record at the same time in the application and make different changes to the same field the person who did it first will lose their change.
This is a valid point, but I feel one that adds too much overhead to application development to deal with. I would only consider it if I was dealing with banking software (which I've never done).
November 7, 2002 at 7:59 am
quote:
This is a valid point, but I feel one that adds too much overhead to application development to deal with. I would only consider it if I was dealing with banking software (which I've never done).
I think I would have to agree. Resolving write conflicts such as this often adds so much work for so little benefit. Even if you built a conflict management piece into the application that lets the user decide which write to commit, there's no guarantee the user will know which record to commit (most would simply force their own update over anyone else's, anyway.) Except for a few extraordinary cases, I think just writing the latest (by a timestamp) record is the most cost-effective solution.
IMHO, I believe that too often developers and designers get sidetracked into coding solutions for <1% of actual situations, and in doing so, either delay or ignore more important, critical, or useful improvements to an application's design and functionality.
November 7, 2002 at 11:31 am
Maybe I am missing something here, but in the scenario you listed, why does user 2's update whipe out the change user 1 made? They are updating 2 separate columns aren't they? For each update request, are you updating every column in the row rather than the specific field that needs to be updated? If updating the field specific to each users change then nothing should be overwritten.
quote:
For example, we have a table called myTable with two columns, col1 and col2. User 1 reads a record, and wants to update col1 with a new value. In the meantime, another user, User 2 reads the record before the update. User two wants to update col2. User 2 has read the record before user 1 has committed the update. Therefore, user 1 makes his update, and user 2 makes an update after user 1 had made his update, thus wiping out user 1's update. This example assumes that a stored procedure would update all columns in a table, therefore causing the "last one in wins" scenario.
November 8, 2002 at 10:41 am
I wanted to thank all who responded. The links and the info provided were great.
I didn't mention, but I am working with bank software, so this concurrency issue is a little bit of a sticking point.
Thanks again! ajroney
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply