May 30, 2003 at 10:24 am
I have copied some SQL queries from a client VB program into a stored procedure. The client executes all statements in 30 seconds. The same code in the stored procedure, when executed through Query Analyzer, takes 82 seconds to complete. I'm having trouble figuring out why. Any ideas?
May 30, 2003 at 10:59 am
Check the Execution plan in QA.
See if the stored procedure is using indexes, etc. Is this a select, update or delete statement? What are the indexes on the table?
This also might be a locking issue.
Patrick
Quand on parle du loup, on en voit la queue
May 30, 2003 at 11:00 am
Is the SQL built dynamically?
May 30, 2003 at 11:04 am
I pass the procedure 3 global temp table names that are populated for the client to use after execution. Everything else is static.
May 30, 2003 at 12:20 pm
quote:
Is the SQL built dynamically?
I miss spoke above. In addition to passing the procedure dynamic global temp table names, I am also passing it various parameters for WHERE clauses.
I replaced the dynamic global temp tables with static temp tables. did not help.
June 2, 2003 at 2:12 pm
please do not use temp tables.They are not efficient and you must clean the tempdb after use.Especially if you use global temp tables.
Better use a function that returns a table datatype.It is very neat. and the @table datatype does not require tempdb ;it uses mor of a CPU that anything else.
Then , another problem with temp table is that it forces the store procedure to recompile EVERY time it is executed.Bad habit. Run profiler to check the recompile ststus.
that is just my opinion
lmt
June 2, 2003 at 2:54 pm
How large are these temp tables? It sounds as if QA is selecting a table scan execution plan.
I recently used the table variable in a function and found it to be very handy. Cleaner than temp tables.
Patrick
Quand on parle du loup, on en voit la queue
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply