ODBC drivers difference

Posted on 2004-11-03
Last Modified: 2008-01-09

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.
Question by:fischermx
    1 Comment
    LVL 8

    Accepted 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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now