June 16, 2003 at 12:42 am
I am updating a table based on the values of another table, i.e. there is a join in the update statement. Parameters are passed to the stored proc to limit which records get updated.
The procedure was taking 11 seconds to run. When I tried the same SQL, substituting variables for parameters, the update was almost instant. I altered the procedure to copy the parameters into variables and the update to use these variables. This fixed the speed of the procedure.
Now I know that SQL 6.5 had problems using variables in some queries and was much happier with parameters, but I am seeing the opposite in SQL 2000. Has any one else seen this?
I checked some other update statements with joins and the results were the same.
Cheers,
John
June 16, 2003 at 4:12 am
If your update takes 11 seconds consistently, but selects instantly then it sounds like the query is having to do a lot of locking before it can commit.
But without knowing your full situation I would suggest:
Seeing if you could replace the join with input variables?
Turn on execution plan on query analyzer and look for the bottleneck.
June 16, 2003 at 6:12 pm
Here's an example update statement:
update dbo.tMappingRule
set ratio = r.ratio
from dbo.tMappingRule mr
inner join tMappingTemplateRule r
on r.AccountID = mr.AccountID
and r.LevelID = mr.levelID
and mr.DataCycleID = @DataCycleID
and r.MappingTemplateID = @MappingTemplateID
What I found was if @DataCycleID and @MappingTemplateID were parameters to a stored procedure the update would take significantly longer than if they were variables. The execution are different between the two options, so SQL Server is obviously choosing the wrong plan when parameters are used. I wasn't doing any performance checking against selects, just the two updates.
I have been changing all the update statements to use variables instead of parameters and have been getting significant and consistent performance gains. I was simply wondering if anyone else has seen this as I have only tried it on one server at the moment.
June 16, 2003 at 8:06 pm
Hi there
Ive had widely varying results as well between query analyser and code in stored procs and also views. Its all to do with parameter substitituon and meta data related to the other two objects vs the query in raw query analyser. Ive been bantering on for a few months on the microsoft sql server list re the issues and whild differences in speeds etc to no avail 🙂
Anyhow, enough of that!
I would
a) check indexing, lots of indexing being altered/managed with the update? size of them? type is? see explain plan results via profiler as well
b) if in a stored proc, assign the incoming parameters to local procedure variables and use these instead to get around issues of parameter substitution in sql server.
c) as mentioned, check locking and blocking issues, is running through the app and using com+ you will have a serilisation isolation model and therefore completely different locking senarios when run via query analyser for example.
d) triggers a problem?
Cheers
Ck
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
June 17, 2003 at 7:35 am
Perhaps if you substitute the stored procedure it runs many times while evaluating the query, but if you put the values into variables, it only runs once? Just a thought...
June 17, 2003 at 9:19 am
Try moving the variables/parameters to the WHERE clause. Having them as part of the join condition may be causing the stored procedure to recompile each time it is called.
update dbo.tMappingRule
set ratio = r.ratio
from dbo.tMappingRule mr
inner join tMappingTemplateRule r
on r.AccountID = mr.AccountID
and r.LevelID = mr.levelID
WHERE mr.DataCycleID = @DataCycleID
and r.MappingTemplateID = @MappingTemplateID
June 18, 2003 at 9:40 am
quote:
(...)I altered the procedure to copy the parameters into variables and the update to use these variables. This fixed the speed of the procedure.
(...)
Has any one else seen this?
(...)
I have (unfortunatly) encountered the same problem, posted my results in http://qa.sqlservercentral.com/forum/link.asp?TOPIC_ID=12360 (the last post) which led me no-where...
Seems might it could be a problem of statistics but to me I still have no clue why one solution works and the other one not...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply