Re: Parameter binding and performance concerns (behind the scenes)

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