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/ServerHTH
Eric
