Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
TheBailster
Starting Member
2 Posts |
 Posted - 2005-06-27 : 05:25:25
|
| I having been writing a Stored Procedure to return data for a report, when running the various commands (insert, updates and select) in Query Analyzer it takes 2 seconds to return 5000 rows, running the Stored Procedure it takes 30 seconds to retrun the same 5000 rows.Having put some timeings in the Stored Procedure the following update statement is taking the time.update #invoiceOrder set io_status_id = ts_status_flow_id from work_task_statuseswhere ts_id in ( select max(ts_id) from work_task_statuses, invoices where ts_work_task_ID = io_wt_id and ts_job_ID <> 0 and ts_date < in_end_date + 1 and in_id = @intInvoiceID)and io_job_count = 1In Query Analyzer it's almost instantaneous yet takes 25 seconds in the stored procedure.Any help would be greatly appreciated. |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2005-06-27 : 09:36:51
|
| So you run the code directly in QA and it is instant, but you do this in QA : Your_Proc_Name 'YourParameters' and it takes 25 seconds?*need more coffee*SELECT * FROM Users WHERE CLUE > 0(0 row(s) affected) |
 |
|
|
TheBailster
Starting Member
2 Posts |
Posted - 2005-06-28 : 03:47:06
|
| That's the exact natural of the problem. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-06-28 : 07:27:24
|
| Can you display the execution plan of the "Your_Proc_Name 'YourParameters' execution"?and compare against the alternative.also...which do you run first?is CACHE coming into play? |
|
|
|
|
|
|
T-SQL running in a stored procedure