Re: Update statement performance decreases in stored proc
From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 01/12/05
- Next message: David Pope: "Re: T-SQL Debugger"
- Previous message: Aaron Weiker: "Re: MAX function"
- In reply to: Jacco Schalkwijk: "Re: Update statement performance decreases in stored proc"
- Next in thread: Hugo Kornelis: "Re: Update statement performance decreases in stored proc"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 12 Jan 2005 14:24:37 +0100
On Wed, 12 Jan 2005 12:48:57 -0000, Jacco Schalkwijk wrote:
>Hugo,
>
>Have you had a look at the execution plan of the stored procedure?
I did look at it, but I didn't really understand all actions in the plan,
let alone the reason why SQL Server chose this plan.
I intended to post the execution plans in the original post, but was
distracted and then apparently hit Send instead of Save.
> It might
>be that it is not using the index you create, because it is created after
>all the inserts happen on the table. Creating an index on a table will not
>automatically mark stored procedures that use that table for recompilation.
>You can try if moving the create index statement to a place before the
>inserts will make a difference in procedure execution time.
Now that I know what to look for in the execution plan, I can confirm that
the clustered index was indeed not used. I just ran a test with the CREATE
INDEX statement moved to directly after the CREATE TABLE and before the
INSERT; now the same query plan is used as when I run it form QA.
What I still don't understand is the different behaviour when run as one
batch from QA. I always thought the optimizer compiles a whole batch at
once, but what I'm now seeing doesn't support this.
Do you know if the optimizer compiles one statement at a time or the whole
batch at once?
Best, Hugo
-- (Remove _NO_ and _SPAM_ to get my e-mail address)
- Next message: David Pope: "Re: T-SQL Debugger"
- Previous message: Aaron Weiker: "Re: MAX function"
- In reply to: Jacco Schalkwijk: "Re: Update statement performance decreases in stored proc"
- Next in thread: Hugo Kornelis: "Re: Update statement performance decreases in stored proc"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
