Stored Procedure Optimization

Multiple transactions

[woop01]

Hi, I've got three transactions that are always run together so I decided to put them into one stored procedure. However, for some reason, when SQL Server runs them together in a stored procedure they require more resources than when they are run individually.

Is there a common reason it would choose a different execution plan for three transactions in a stored procedure than when they transactions are run individually?

(if more details are needed, let me know, I put them up but I'm not sure they are necessary)

Thanks,

[syber]

Inside the Stored Procedure - do you now have one big transaction, or the three original transactions?

[woop01]

The three transactions. For example, when I edit it....

----------------

ALTER PROCEDURE [dbo].[procedure_name]

@variable1 nvarchar(30),
@variable2 nvarchar(30)

AS

[insert transaction]

[update transaction]

[update transaction]

----------------

[syber]

You might try:

AS

BEGIN TRANSACTION
[insert statement]

[update statement]

[update statement]
COMMIT TRANSACTION

Also, are the same number of rows affected each time you run this procedure - or can it vary widely depending on what parameters are passed?

[arran]

Hi woop01,

Have you compared the execution plans?

arran.

[woop01]

syber, it's a user rating system. The insert inserts only one row and the updates each update only one row.

arran, yes.

The insert portion of the stored procudure costs the same as running it as a transactin but the updates cost significantly more (1.04 vs .0365). For some reason the when these transactions are done from within stored procedure they use an index scan instead of index seek. Here's a basic run down...

When run as a transaction

Update <- Clustered Index Update <- Compute Scalar <- Compute Scalar <- Table Spool <- Top <- Index Seek

When run from within the stored procedure

Update <- Clustered Index Update <- Compute Scalar <- Compute Scalar <- Compute Scalar <- Top <- Nested Loops/InnerJoin <- (two forks - Index Scan and Clustered Index Seek)

I understand if this is a bit too specific to be solved on a message board. Thanks for any help you may be able to provide.

[aspdaddy]

Stored procs are compilied so my guess is its having to recompile it every time because of the code you are using.

Are you using dynaimic SQL or a where clause for the update on a field that isnt indexed?

Can you post us the actual code you are running inside the transactions?