Eric,

Thanks for your note. Reading your comment makes me realize how hard it is to explain these problems clearly.

I definitely see what you are saying about not calling a stored procedure using sp_ExecuteSQL. In my case, that is not anything I ever did. (I have a couple of large stored procedures on the back end -- I always call them using SPT.)

My understanding about using sp_ExecuteSQL (with values passed as parameters) is that it allows the back end to cache an execution plan. When you next call your T-SQL instruction (with different parameter values) the cached execution plan is re-used only with new parameter values. So the goal is to save time on subsequent executions.

But what I found was the following: The ODBC driver, (using remote parameterized views) passed the back end the following command:

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

SQL Server created an execution plan that used an INDEX SCAN, resulting in many reads. Very slow.

When I submitted the (I thought) same command as "raw T-SQL"....

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

the back end did an INDEX SEEK as the principal part of the execution plan, and the results were returned very, very much faster.

[BTW, the two code samples shown above were submitted to the back end using Query Analyzer. After seeing the results, I dropped the parameterized view, created the T-SQL string in VFP, and submitted it using SPT.]

And what really confounds me is that this parameterized view seemed to work fine for some months, and when my DBA switched me to another server (virtually identical hardware and software environment) I started seeing the slowdown.

For now, I've got everything back to its original performance using SQL PassThrough, but I am sure I am missing something about using sp_executesql.

Thanks for mentioning the issue about the three execution plans. Steve


>I don't know how to solve your performace problem, but I can shed some light on the sp_executesql procedure. It might help explain some stuff.
>
>My experience with views and sql pass-though is similar. This is what I figured out. As you know, some queries are passed from foxpro to the sql server exactly as they are entered and some are wraped in a call to sp_executesql. The sp_executesql stored procedure is used by Foxpro to gain a performance incerease. It uses a stored procedure to call ad-hoc sql to cause the execution plan to be cached. The contents of parameter to sp_executesql are compiled and executed as an execution plan separate from the execution plan of the batch that called sp_executesql. That way Fox pro is able to gain the performance benefit of using stored procedures even though you passing raw sql to the server.
>
>But what if you are already calling a stored procedure? Whenever you use variable subistution in a sqlexec() call, which your remote view does, it will wrap the stored procedure call or the raw sql in a call to sp_executesql, even if your already calling stored procedure.
>
>For example..
>In Foxpro:
>

liListID = 159
>sqlexec("execute EmployeeAuthorizationList @liListID")

>will get passed to the sql server as:
>

exec sp_executesql N'execute EmployeeAuthorizationList @P1 ', N'@P1 float', 2.130000000000000e+002

>But if in FP, if you acutally concatenated the value of the variable into the string like:
>

liListID = 159
>sqlexec("execute EmployeeAuthorizationList " + allt(str(liListID)))

>will get passed to the server as:
>

execute EmployeeAuthorizationList 159

>
>The latter is what I want. Why would I want the sql server to make 3 calls to 3 execution plans.
>The first execution plan gets formed for the batch containing the sp_executesql statement.
>The second is formed for the statement passed into the sp_executesql
>The third is the execution plan already stored for the stored procedure.
>
>I may not be 100% right on this but it only makes sense to me. This is why I use SPT and manually form the sql string and then send it to the sql server via sqlexec.
>
>More info is avalable here; http://fox.wikis.com/wc.dll?Wiki~Client/ServerDataAccessTechniques~VFP and here; http://fox.wikis.com/wc.dll?Wiki~CategoryClient/Server
>
>HTH
>Eric