ODBC drivers difference

Hi,

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
go
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
go



// ODBC Driver #2
SET FMTONLY ON select "x","y","z" from "table" SET FMTONLY OFF
go
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
go
exec sp_execute 11, 'T', '117', '2004'
go
exec sp_unprepare 11
go


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.
LVL 1
fischermxAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sigmaconCommented:
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).

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.