I have been struggling with a performance question in an application we have been testing for several months.

The project's data base administrator switched us to a different server (same table structure, indexes, stored procedures, etc and the same hardware platform), and the application ran much more slowly. In tracking down bottlenecks with SQL Profiler, I found that the major problems seemed to be with statements using EXEC sp_executesql.

Further, the problem only occurred in those cases where I passed the values of variables to the string to be executed. In other words, if I created a string with variable values spelled-out in the string and executed the string with sp_executesql, the query ran extremely fast.

I recoded the SELECT in my stored procedure, and that sped up the stored procedure greatly. Part of the problem remained; however, because we use Visual FoxPro parameterized views in much of the application, and the ODBC driver uses the EXEC sp_executesql syntax.

An example: Using Profiler, I captured the command sent to the back end from the ODBC driver:

exec sp_executesql N'SELECT ALL Component_2002.*
     FROM Component_2002
     WHERE yearcol = @P1
      AND tradate = @P2
      AND product_id = @P3 ',
     N'@P1 int,@P2 datetime,@P3 int',
     2002, 'Oct 30 2002 12:00AM',
     7

On our hardware, this takes over a half minute and there are in excess of 160,000 server reads. Component_2002 has a single clustered index, and the major cost in the execution plan is a clustered index scan.

As an experiment, I rewrote the T-SQL command as:

select * from component_2002
   where yearcol = 2002 and 
   tradate = '20021030' and
   product_id = 7

This returns the same 50 rows in around 60 milliseconds, fewer than 50 reads on the server, and uses a clustered index seek.

At first I thought there was a problem with the Component_yyyy tables on the new server, but running DBCC DBREINDEX and recomputing the statistics make no difference.

While the server switch brought this whole business to light, I have tried these queries against the databases on both servers, and the performance difference occurs on either server. Should I recode my parameterized views as SQL PassThrough where I have performance problems? Is sp_executesql supposed to do this? Thanks.