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 |
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
 Posted - 2006-01-06 : 09:29:07
|
| I have an issue with a basic stored procedure. When executed as a stored proc, it can take a minute or more to return. If I take the sql out and run it as a script, it returns instantly.SELECT XId,StatusCode AS [Status], (ISNULL(FirstName,'') + ' ' + ISNULL(MiddleName,'') + ' ' + ISNULL(LastName,'') + ' ' + ISNULL(NameSuffix,'')) AS XName, CONVERT(varchar,DOB, 101) AS [DOB], DriversLicenseNumber FROM X_Master WHERE StatusCode = 'A' AND ((DriversLicenseNumber = @p_DriversLicenseNumber) OR (LastName = UPPER(@p_LastName) AND (FirstName = UPPER(@p_FirstName)) AND DOB = CONVERT(varchar, @p_DOB, 101))) AND (XId != @XID OR @XID IS NULL)The proc is very basic, the execution plans shows nothing out of the ordinary.Any thoughts or pointers on a different way to troubleshoot?________________________________________________The only cure for thinking is picking rocks, or drinking beer. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-01-06 : 09:33:20
|
| Did you execute the sp from Query Analyser or from Client Application?MadhivananFailing to plan is Planning to fail |
 |
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2006-01-06 : 09:38:21
|
quote:
Originally posted by madhivanan
Did you execute the sp from Query Analyser or from Client Application?MadhivananFailing to plan is Planning to fail
The sql and the proc are run from qa both. (obviously, the proc is in the application as well, which is where the problem was noticed) but for testing, both have been run in QA.________________________________________________The only cure for thinking is picking rocks, or drinking beer. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-06 : 09:42:46
|
| "When executed as a stored proc, it can take a minute or more to return. If I take the sql out and run it as a script, it returns instantly"Does it run quickly after it has been recompiled? i.e. the first time it runs it has to build a query plan, which can take a while. If over time its performance deteriorates then try RECOMPILE and see if it speeds up again.If so:Are statistics updated regularly?If so:You may to manually sp_recompile it because the query plan is not getting recalculated when the "shape" of some indexes changes.We have some Sprocs in this category. They are doing a really boring job pulling data from an essentially-static table. Something awful happens, dunno what, and the performance deteriorates. sp_recompile and we're away again.I reckon that the "essentially static" table gets flushed, and then rebuilt just-in-time. It will wind up with exactly the same data as before, but created in a different order, and something about it must be different, but I'm blowed if I can find what!Kristen |
|
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2006-01-06 : 09:53:34
|
quote:
Originally posted by Kristen
"When executed as a stored proc, it can take a minute or more to return. If I take the sql out and run it as a script, it returns instantly"Does it run quickly after it has been recompiled? i.e. the first time it runs it has to build a query plan, which can take a while. If over time its performance deteriorates then try RECOMPILE and see if it speeds up again.If so:Are statistics updated regularly?If so:You may to manually sp_recompile it because the query plan is not getting recalculated when the "shape" of some indexes changes.We have some Sprocs in this category. They are doing a really boring job pulling data from an essentially-static table. Something awful happens, dunno what, and the performance deteriorates. sp_recompile and we're away again.I reckon that the "essentially static" table gets flushed, and then rebuilt just-in-time. It will wind up with exactly the same data as before, but created in a different order, and something about it must be different, but I'm blowed if I can find what!Kristen
Interesting. The version we are having trouble with is on our development copy. I don't update stats to regularly , but I may want to consider that. The table it hits is indexed to the hilt (its hit often and not written to much. about 5 million rows) We haven't been able to recreate the problem on the Testing copies, which are updated every week or so.Thanks.________________________________________________The only cure for thinking is picking rocks, or drinking beer. |
|
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2006-01-06 : 10:12:27
|
| it seems the != was the problem, switching to a XID IN (select..)helped it out.learn something new every day.________________________________________________The only cure for thinking is picking rocks, or drinking beer. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-01-06 : 10:20:39
|
| "OR (LastName = UPPER(@p_LastName)AND (FirstName = UPPER(@p_FirstName))"???Is your database set to be case sensitive? That's not the default... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-06 : 15:29:01
|
| "That's not the default"Hehehehe ... </sorry!> |
|
|
|
|
|
|
|
|
Query vs Proc