November 25, 2008 at 6:34 am
I use a query to update about 2500 rows. when i execute the update in sql server management studio express it takes about 2 seconds. Same update, in a stored procedure, executes in 2 minutes.I execute the procedure with the same parameters. That's a lot of extra time. what's the explanation?
I have this indexes:
-column debit in sahnote
-column credit in sahnote
-column cont,cap,art in sahbalanta
this is the update :
update sahbalanta
set rlunad=(select ISNULL(sum(suma),0) from sahnote -- incarcam rulajele lunare in balanta pe debit
where month(data)=@luna
and year(data)=@an
and rtrim(capDebit)=s.capitol
and rtrim(artDebit)=s.articol
and Debit=s.cont
and uid=@uid),
rlunac=(select ISNULL(sum(suma),0) from sahnote -- incarcam rulajele lunare in balanta pe credit
where month(data)=@luna
and year(data)=@an
and rtrim(capCredit)=s.capitol
and rtrim(artCredit)=s.articol
and Credit=s.cont
and uid=@uid)
from sahbalanta as s
where luna=@luna
and an=@an
and uid=@uid
November 25, 2008 at 7:30 am
I'm not sure why you would see such a difference when putting in a stored procedure although the most likely issue is parameter sniffing. You can look that up on-line.
Some other issues that are affecting your performance is the use of functions (month, year, rtrim) in your where clauses. This eliminates the possibility of getting an Index Seek as each row must be evaluated by the functions. Instead of using month and year you should use start and end dates like:
Where data >= @start_date and data < @end_date
For the rtrim you should be using varchar/nvarchar for your variable length strings an SQL Server will ignore trailing spaces or have your parameters be the same length so SQL Server will pad them for you. Or ideally you eliminate the trailing spaces when creating the data.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 25, 2008 at 11:16 pm
why is the code evaluated differently in the stored procedure and in query window ?
if it was slow it should be slow in both cases.
November 26, 2008 at 7:20 am
When you put it in the stored procedure that is when you can have an issue with parameter sniffing. Which is basically means the optimizer "guesses" the parameters and creates a query plan that may not be optimal for the parameters you actually pass in. While this does not happen when you run a query directly. You can get past this by executing or creating the procedure with the WITH RECOMPILE option or you can assign the parameters to local variables within the procedure and use the local variables in the where clause.
You should check the execution plans in both instances and you should see different plans.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply