Skip to Content.
Sympa Menu

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

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Frediano Ziglio <freddyz77 AT tin.it>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Parameter binding and performance concerns (behind the scenes)
  • Date: Thu, 07 Aug 2003 06:01:23 -0000

Il mer, 2003-08-06 alle 23:08, Stephen Schadt ha scritto:
> 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?
>

It's very strange. I don't remember our driver create temporary store
procedures... Are you using FreeTDS ?? Which version ? Version 0.61
should create sql command while 0.62 (still not released) should use
sp_prepare/sp_execute/sp_upprepare...
I think also you are using ODBC to connect to mssql.

bye
freddy77






Archive powered by MHonArc 2.6.24.

Top of Page