Re: Update statement performance decreases in stored proc

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 01/14/05 

Date: Fri, 14 Jan 2005 16:28:15 +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? 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.

Hi Jacco,

I've spent over a day testing various combinations of indexes, created at
different moments in the stored procedure. My findings are:

- If a nonclustered index is created after the data is entered in the
table, it is not used in any queries. Apparently, no recompile is
triggered. This is only true in stored procedures; if executed as a single
batch from QA, the index will get used.

- If a clustered index is created after the data is entered in the table,
it will be used in queries. Apparently, the building of a clustered index
does trigger a recompile of the stored procedure.

- If a nonclustered index is created after the data is entered in the
table, but the queries that might benefit from this index are moved to a
new stored procedure that's called from this procedure, they will use the
new index.

To my surprise, I also found that the supporting index didn't help much at
all. Of all variations I tested with all code in one procedure, the
quickest was the one using only the PK (clustered) and no supporting
index. The only variant that was slightly faster than when I started with
no PK and only the supporting index (clustered), then just before the
update to the main table (GenRegel), create a unique clustered index on
the column sorteercode WITH DROP_EXISTING. This version was (on average
after 3 executions) 0.37 seconds faster than the version with only the PK,
on a total execution time of almost 30 seconds for this procedure (which
is part of a larger process, that takes about 4 1/2 minutes). I decided
that this gain isn't worth the effort, so I'll just remove the supporting
index from my code and stick to using the primary key only.

Thanks for the help!

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: chooses not to generate code at all
    ... >>>DBMS is so that the DBMS engine can execute it when triggered by some ... If there is no trigger, ... Stored procedure is one thing. ... > If the procedure execution is not triggered by DBMS ...
    (comp.object)
  • Re: Stored Procedures vs DTS vs Jobs
    ... A stored procedure is complied code meaning that SQL Server has already ... > execution time, ...
    (microsoft.public.sqlserver.dts)
  • Re: SQL DBA Consultants
    ... When I mentioned "loops" it's just ... > because that's the way it appears if you trace the execution. ... > SQL statement is constructed. ... >> sql within a stored procedure I am guessing that you are building and ...
    (microsoft.public.sqlserver.programming)
  • Re: Deadlock between Distribution Agent and Distribution Agent Cle
    ... stored procedure at the subscriber will have. ... replication to the subscriber and the possibility of moving the ... > obtains will be released at the end of its execution. ... >> Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • Stored Procedure occasionally running 10x slower???
    ... But there are some "anomalies" happening when this stored procedure is ... Using SQL Profiler, ... anomalies and normal execution). ... I tried to "capture" the execution plan for that particular query, ...
    (microsoft.public.sqlserver.programming)