ODBC Perf. Problem and Comparision with OLEDB | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

ODBC Perf. Problem and Comparision with OLEDB

We access sqlserver thru ODBC functions in Borland C++ codes.
In some queries (a very little percentage) we encounter a performance problem compared with the same query running in QueryAnalyzer. In debug it showed that SqlFetch() function is the reason for slowness. But as I said , we have so many applications containing similar queries and this problem appears rarely. But anyway it is a problem.
Is there anyone who faced the same problem and found a solution? We started to consider using OLEDB just because this problem. Any comments switching to OLEDB? Thanks in advance
There are few counters you must take into consideration for this slow performance:
– Network communication is slow
– Evaluation of complex SQL queries on the database server is slow and can reduce concurrency
– Excessive calls from the application to the driver slow performance
– Disk input/output is slow Compared to other ODBC functions, catalog functions are relatively slow. By caching information, applications can avoid multiple executions. Although it is almost impossible to write an ODBC application without catalog functions, you can improve system performance by minimizing their use. Unless it is necessary, applications should not request long data and use a method that does not retrieve all columns of the table. To reduce network traffic and improve performance, you can reduce the size of any data being retrieved to a manageable limit by calling SQLSetStmtOption with the SQL_MAX_LENGTH option. Avoid using cursors in this case as they have negative affect on the side of performance. Distributed transactions are at least four times slower than normal transactions due to the logging and network input/output necessary to communicate between all the components involved in the distributed transaction. Unless distributed transactions are required, avoid using them. Instead, use local transactions when possible. Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
All transactions are of the same type by the way. I want to repeat that , the same query runs very much faster in QueryAnalyzer or WinSQL. If this slowness exists in each application or query , it means that out method is wrong, but it occurs rarely and the queries do not show any similarity in terms of technic.
For ex. We extract "order by" portion from the query and it starts running faster. But the same query runs fast in QueryAnalyzer or WinSql with order by too.
I think there is a trick or bug that appears in processing some queries but could not find it. What is your comment on using OLEDB instead of ODBC in terms of performance?
Depends on the location of your queries: does the client app execute the query based on data retrieved from SQL Server (client side) or does SQL Server perform the whole query and then send the data to the client app (server side)? Especially with ORDER BY statements the server side activity is usually much quicker.
So have you countered the points refered above when using ODBC. OLEDB has definelty upper hand in terms of performance when compard to ODBC, referhttp://www.4guysfromrolla.com/webtech/063099-1.shtml#postadlink link for your information. Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
I set SQL_MAX_LENGTH by SQLSetStmtOption funciton, but fetch time is still slow. SQLExecute funciton runs query faster with order by or without order by. But SQLFetch function runs slowly with order by. Execution times are the same.
How can effect order by option fetch time? Total record and record lenght are the same for two queries. The only different is "order by". And also each queries runs quickly in QueryAnalyzer. Thanks in advance.
QA means the query runs server-side, against "local" data, and only the results are returned. Looks like your app is running the query client-side, so it is going through the indexes and reading from the table and sorting the data, all remotely across the network. The network probably has lots of other traffic going on as well, so a longer response time is unavoidable.