Skip to Content.
Sympa Menu

freetds - [freetds] Parameter binding and performance concerns (behind the scenes)

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Stephen Schadt" <sschadt AT openlinksw.com>
  • To: <freetds AT lists.ibiblio.org>
  • Subject: [freetds] Parameter binding and performance concerns (behind the scenes)
  • Date: Wed, 6 Aug 2003 17:08:44 -0400

Hi,
The goal is to bind a parameter to a prepared statement, in the
following way:

SELECT "A1"."col1" AS c0000 FROM "TAB1" A1 WHERE ("A1"."col1" = ?)

I just tested the following, using MS Access XP

Using the Microsoft SQL Server driver, here is what is executed on the
server side (seen from SQL Server Profiler):

>>
exec sp_executesql N'SELECT "dbo"."Customers"."CustomerID" FROM
"dbo"."Customers" WHERE ("CustomerID" = @P1 ) ', N'@P1 varchar(510)',
'ALFKI'
go
declare @P1 int
set @P1=-1
exec sp_prepexec @P1 output, N'@P1 nchar(5),@P2 nchar(5),@P3
nchar(5),@P4 nchar(5),@P5 nchar(5),@P6 nchar(5),@P7 nchar(5),@P8
nchar(5),@P9 nchar(5),@P10 nchar(5)', N'SELECT "CustomerID" FROM
"dbo"."Customers" WHERE "CustomerID" = @P1 OR "CustomerID" = @P2 OR
"CustomerID" = @P3 OR "CustomerID" = @P4 OR "CustomerID" = @P5 OR
"CustomerID" = @P6 OR "CustomerID" = @P7 OR "CustomerID" = @P8 OR
"CustomerID" = @P9 OR "CustomerID" = @P10', N'ALFKI', N'ALFKI',
N'ALFKI', N'ALFKI', N'ALFKI', N'ALFKI', N'ALFKI', N'ALFKI', N'ALFKI',
N'ALFKI'
select @P1
go
<<

And, the same thing, using the TDS driver:
>>
exec #OPL3GMS7M_fl5HvldUra8lp3Q;1 'ALFKI'
go
exec #OPL3GMS7M_fl5HvldUra8lp3Q;2 'ALFKI', 'ALFKI', 'ALFKI', 'ALFKI',
'ALFKI', 'ALFKI', 'ALFKI', 'ALFKI', 'ALFKI', 'ALFKI'
Go
<<

Any idea why a temporary procedure has to be created with the TDS
driver, and why sp_prepexec isn't used instead?

This is causing some performance concerns for one of my clients.

Stephen
OpenLink Software, Inc
http://www.openlinksw.com






Archive powered by MHonArc 2.6.24.

Top of Page