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 |
|
chadbryant5
Starting Member
32 Posts |
 Posted - 2007-01-19 : 10:39:17
|
| HELP!!! This is very very weird to me and I don't know why this is happening. I have a C# application that runs and executes a query against a SQL Server 2000 DB. The application was getting exceptions due to a SQL Server timeout executing the Stored Proc. So I opened the Stored Proc and copied and pasted the query into Query Analyzer. The query ran in about 20 seconds in QA. So I started a new query window against the same DB and instead of executing the query, I did an EXEC against the SP (ie EXEC dbo.SP_Name parm1, parm2). Doing it this way took 4 minutes!!!! So I went back to my other window and ran the query that was copied from the SP and it ran in about 20 secs again. So I went back to the other window again to run the EXEC of the SP one more time and again it took well over 3 minutes. This makes no sense to me since the SP is running the same query, but with the call to the SP it is taking so much longer.My boss tried it (to ensure I wasn't going insane b/c this made no sense to him either as I was describing it) and he tried using SQL Mgt Studio and also APEX SQL edit and had the same results and really has no explanation, so I'm hoping you all can help me figure out what is going on or possible theories to check as to why this strange behavior would happen.Thanks very much!!Chad |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-01-19 : 11:18:51
|
| I take it you've recompiled the sp?There are occasions where a query in an sp gets a different query plan to that outside. Look at the query plan to see what's happening and try recoding in a different way.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
chadbryant5
Starting Member
32 Posts |
Posted - 2007-01-19 : 13:26:39
|
| Well, I went into enterprise mgr and changed the query to add another needed join, so I assume that may have recompiled the SP? Sorry, I'm a C# developer and not a SQL Server guru. I can write some good queries, but I don't know the DBA level stuff.Anyway, what I don't understand is why the query would have a different execution plan in QA vs the SP. How can I make it run good in the SP like QA? This doesn't make sense to me because I write queries in QA and when they run good, I put them into an SP. This worries me now that our application may be not performing as good as it could b/c SPs might be slower... |
|
|
|
|
|
|
exec SP performance differs from QA