February 29, 2008 at 8:34 am
Hi !
Let me know your views and your experiences concerning this:
1-A SQL consultant told me that SQL performance was better when it connects/deconnects to every request by our application, it does not seem logical at all. Does it really give better performance than connect/disconnect once ? And why or why not ?
2-I did some tests and I recorded the tracks with the Profiler and ...surprise :w00t:! The execution of a stored procedure was less rapid than a dynamic SQL query with parameters (like 'where this= ?') . Why this could happen ?
3-What about performances between server cursors and client cursors ?
Our application is coded in C (Visual Studio ) and its access to SQL server 2005 via DSN and ODBC functions (SQLConnect, SQLExecDirect ...).
Thank you for your responses.
February 29, 2008 at 9:23 am
diallonina (2/29/2008)
Hi !Let me know your views and your experiences concerning this:
1-A SQL consultant told me that SQL performance was better when it connects/deconnects to every request by our application, it does not seem logical at all. Does it really give better performance than connect/disconnect once ? And why or why not ?
2-I did some tests and I recorded the tracks with the Profiler and ...surprise :w00t:! The execution of a stored procedure was less rapid than a dynamic SQL query with parameters (like 'where this= ?') . Why this could happen ?
3-What about performances between server cursors and client cursors ?
Our application is coded in C (Visual Studio ) and its access to SQL server 2005 via DSN and ODBC functions (SQLConnect, SQLExecDirect ...).
Thank you for your responses.
1) I haven't done tons of tests on this myself, but it is considered a best practice to only maintain the connection to the database for the amount of time that you need that connection. I think the definition of "need" in the last sentence can be debated.
2) A parameterized query is, to all intents and purposes, the same as a stored procedure for SQL Server. It's going to generate an execution plan, store it in cache and reuse it more or less the same was and as stored procedure. So for an identical query with identical parameters, you should get the same performance. If you're getting radically different performance for the same parameter & query, that's something to look into further. The strongest points I know for using procedures instead of parameterized queries are security, where you have a great deal of control in the proc and NONE in the parameterized query, and in code isolation where you can make adjustments to the internals of a query, as long as the inputs & out puts are the same without redeploying the application whereas with the parameterized query, you have to redeploy the app.
"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
February 29, 2008 at 9:41 am
1. Agree with Grant - keep the connection open only as long as is required. That being said, opening a connection, making 2 calls in a row, then closing the connection falls within those parameters. If you are really making multiple consecutive calls within the same conceptual block, opening up and tearing down multiple connections doesn't make much sense.
2. In addition to Grant's points: keeping your SQL code in SQL server allows folks who are "experts" at SQL (like say - the DBA) to be able to review/optimize your SQL code without having to go into your code. As to performance - it may be that the SP is making some bad choices on how to optimally perform your request. Work with someone who knows how to optimize Stored procedures, and you will see that they operate just as well as (sometimes better than) ad-hoc queries.
3. Cursors tend to be very inefficient compared to a lot of other ways to do things with regards to SQL server. They should be restricted in general to being the LAST resort. (again - in general) By the time you're using a cursor, IMO client vs server doesn't make all that much difference (you've already given away most of the performance by then)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 29, 2008 at 9:46 am
Sorry, missed 3) I agree. Cursors are a killer. It doesn't much matter if it's client or server side. However, if you're talking disconnected, then client side is the least bad choice. But then, you would be dropping that connection from #1.
"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
February 29, 2008 at 11:22 am
Thanks 🙂
I think, I do not have choice of using cursors when accessing SQL Server by ODBC functions.
I use SQLSetStmtAttr(Statement with SQL_ATTR_CURSOR_TYPE attribute).
Application in C-->ODBC functions (thats implements cursors)->SQL Server
Grant, is there others ways to do this without cursors?
February 29, 2008 at 11:41 am
Sorry, I'm just not that acquainted with the details of implementation any more. I wrote a whole app against direct calls to ODBC about 13 years ago, but... It's all gone now. I spend all my time on the database side of things. Others can help better than I can in this area.
"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
February 29, 2008 at 1:27 pm
Why ODBC? ODBC is on its way out - why not OLE-DB?
Besides SQL is so much more optimized to "talk" oleDB....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply