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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-06-16 : 08:07:45
|
| Joakim writes "I have a rather long SQL query that I would like to run several times with different input parameters, so I put it in a stored procedure as I usually do. However, when I did the execution plan became a little different, and the query much slower. The result is identical (in somewhat different order).I've managed to minimize the query somewhat to find the problem, and now I'm down to a very short query that shows the same behaviour. The query is basically a join between two views, both somewhat complex, with a group by clause and some "with" statements (all comparing variables to indexes).What can this be caused by? Why is the procedure so much slower than the "free" query? In the query I use declared variables instead of the procedure parameters, and they aren't optimized away according to the execution plan. Also, when profiling the executions, the procedure made 500,000 reads in the database while the query made only 60,000.I am running this on a Windows 2003 server with SQL Server 8 with SP4 installed. Thanks in advance" |
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-06-16 : 08:58:01
|
| And what happens to the execution time when you drop the query hints in your with statements and let the query optimizer do it's job.Sean RoussyPlease backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own. |
 |
|
|
|
|
|
Stored procedure is SLOWER than running the query inside