• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1088
  • Last Modified:

ODBC drivers difference


I'm testing two ODBC providers that are needed to perform big batches of insertions to an SQL Server database.
Each of them, behave different.
The most important thing I'm testing is if they are preparing the queries and using parameters, since this is a long batch with same statement thousands times, I expect this should give a lot of gain in performance.
I'm posting this information I got from SQL profiler :

// ODBC Driver #1
SET FMTONLY ON select x, y, z from Table SET FMTONLY OFF
declare @P1 int
set @P1=50
exec sp_prepexec @P1 output, N'@P1 varchar(10),@P2 varchar(10),@P3 varchar(4)', N'INSERT INTO TABLE(X, Y, Z ) VALUES(@P1, @P2, @P3 )', 'X', '118', '2004'
select @P1

// ODBC Driver #2
SET FMTONLY ON select "x","y","z" from "table" SET FMTONLY OFF
declare @P1 int
set @P1=11
exec sp_prepare @P1 output, N'@P1 varchar(10),@P2 varchar(10),@P3 varchar(4), N'INSERT INTO  "Table" ("X","Y","Z")  VALUES (@P1,@P2,@P3)', 1
select @P1
exec sp_execute 11, 'T', '117', '2004'
exec sp_unprepare 11

I don't know which of this two methods could be more efficient. We were already using Driver #2 in production, but I think, it is not doing things very efficiently.
Now, I'm testing Driver #1 and see it behaves different, but I'm not sure how efficient is this two-in-one sp_prepexec call.
I've looking in google but I don't find many information about how exactly sp_prepexec works.

Thank in advance.
1 Solution
One of the major reasons why Driver #2 may be performing badly is because it's unpreparing the statement after it executed - essentially losing all efficiency gains of prepared statements. Driver #1 is using the 'merged' form of sp_prepare / sp_execute, which is sp_prepexec (functionality is the same). Although there is no 'public' documentation for sp_prepexec, some people have reported performance problems with it. So here the best solution is testing.

If you can configure your ODBC driver at all regarding what type of statement to actually issue to the server, try getting it to use sp_executesql ... for this case I know the query plan is cached in performance in my apps has been better then with any other statement type (for non-OLE DB providers).


Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now