Hi all,

Please, can anyone help me understand this strange SQL server request problem?

I have a big stored procedure with 32 joins and it return a cursor with about 180 columns.

Caling this stores procedure from SQL Query Analyser take about 10 seconds to return 711 records (from sereval tables with thousands records)

To optimize my stored procedure, I cut and paste the code in the query analyser, and localy declare the variables used as parameters in my stored procedure.

I execute this code in the query analyser and got my result (still 711 records) in 4 seconds. I thought it could be the cache memory, and close the query analyser re-open it and execute this code sereval times... it always take 4 seconds. I called the stored procedure several times too and it take 10 seconds (the code in the stored procedure is the same tha the one in my SQL Query Analyser)

As it seems to be faster, I change my foxpro code, to assign the same code I used in SQL Query Analyser (with a TEXT ... NDTEXT command) to a variable in then SQLEXEC instead of using the stored procedure. Surprise... the SQLEXEC return no cursor and no error. I made some tests and notice that I have to call 2 times my SQLEXEC to retreive my cursor result. It take also about 4 seconds.

To test why I have to execute 2 times SQLEXEC, I copied my variable into the clipboard (with _Cliptext) and paste it into the SQL Query Analyser, and execute it... I got my result at the first execution and in 4 seconds.

Does anybody have an idea...
1. why the same code return the same result in 9 second when called in a stored procedure and 4 seconds in the Query Analyser
2. Why I have to execute the code 2 times in Foxpro (with 2 time the same SQLEXEC) to get my result cursor and 1 time in Query Analyser

Thanks