SQL Server Performance Forum – Threads Archive
Dynamic SQL & Connection Pooling
Hi,Our client has a 2-tier VB appl + SQL 7.0 . Currently, the app makes extensive use of SQl queries in VB code and on top of that Views have been used extensively degrading the performance.
Conversion of views to stored procs poses a problem as the no. of VB controls (their selection forms the basis of WHERE condition) is too many(more than 40/50 in some cases. 1. ) Can anyone tell me the drawback of forming a stored proc having DYNAMIC SQL? (relative to usage of a view)? Intend to pass WHERE condition & Order/Group by as args to SP and concat’g it with "Select .." clause 2.) also, can someone tell me how connection pooling works in VB6/ADo 2.7/UDL scenario? Just opening a connection when its needed and closing+setting it to nothing as soon as processing is done–> does this help in connection pooling? Currently 10 global connections are opened and remain in use till user logs out.(degrades performance drastically) regards
Anand
1) I think you should be okay if you use executesql… For instance:
exec sp_executesql N’string query created from passed arguments to SP’ I don’t think this is dynamic sql because I personally use a lot of inline SQL and when I run a trace those are the kinds of statements I see. The queries are fast and SQL chooses the correct execution plans. 2) Keeping connections open the entire time the user is active is not bad for performance. In fact it’s the best way of doing things if you DON’T have Connection Pooling available. This will only degrade performance if you have a lot of users on at the same time (hundreds of connections). Is this the case?
sp_executesql has nothing to do with how efficient dynamic sql is. It just forces it to run as a remote call. The reasons you shouldn’t use dynamic SQL is because:
1. It’s a big security risks susceptible to SQL Injections.
2. It doesn’t for efficient plans.
–If you run the exact same commands over and over, and you have enough memory, your statements execution plans will still stay cached.
–Stored procedures compile plans though and keep them as part of the compiled proc. You’ll never get this with dynamic SQL. Most of what you said can be done with CASE and OR logic instead of dynamic SQL.
WHERE
(@param IS NULL OR col1 = @param)
ORDER BY
CASE WHEN @order = 1 THEN lname END ASC,
CASE WHEN @order = 2 THEN lname END DESC,
CASE WHEN @order = 2 THEN lname END ASC etc, etc, etc. Also, if you are going to get a lot different results, you can set up a series of subqueries that will never have to recompile. This is even more effient. In your main query, you test for the paramaters. In subqueries, you execute whatever they passed to you. The subqueries will always run exactly the same and will run extremely quick even if you run them thousands of times a day. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
About question 2. The first time you open a connection it will go into the connection pool. As long as you use this connection in the application, be it fetching or sending data no one else can use it (unless you operate in a server side cursor mode). As soon as you close the connection in VB it will go back to the connection pool for other requests to use. This mean that other requests doesn’t have to do the initial handshake procedure to put the connection in the pool in the first place. Other requests can use the connection in the pool if their connection string is exactly identical. Once in the pool it will stay there for a default of 180 seconds I believe if it’s not requested again, then it will be removed. In a VB6/ADO scenario it’s generally recommended to close the connection as soon as you are done working with the data, sending it back to the pool for others to use but it depends on your cursor mode on your record sets as well. If possible try and work with disconnected record sets (depends on the application though). Do not keep too many server side cursors open for a long time or it will lower concurrency due to locks/blocks in tempDB.
Never said sp_executesql has anything to do with dynamic sql. Just reassuring him that if you concatenate strings and run a sp_executesql call SQL server will treat the string just like any other SQL query. There is no ‘dynamic’ penalty. Hope that clears it up.
]]>