Re: Update statement performance decreases in stored proc

From: Jacco Schalkwijk (jacco.please.reply_at_to.newsgroups.mvps.org.invalid)
Date: 01/12/05 

Date: Wed, 12 Jan 2005 12:48:57 -0000

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. 

-- 
Jacco Schalkwijk
SQL Server MVP
"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message 
news:gi4au0de3d9fff1u440bdjc7anij9112cu@4ax.com...
> Hi all,
>
> I'm having some trouble finding out why an update statement that runs in
> less than 2 seconds from Query Analyzer takes no less than 24 minutes when
> in a stored procedure. Here are the important parts of the stored proc:
>
> CREATE PROC MyProc
> AS
> -- Create temp table, fill it with starting data
> CREATE TABLE  #GenRglTekst
>             (sorteercode                  varchar(400)   NOT NULL
>                                                          PRIMARY KEY,
>              GenRglTekst                  varchar(1000)  NOT NULL,
> -- some other columns snipped rfom this usenet post
>             )
> INSERT        #GenRglTekst
>             (sorteercode, GenRglTekst,
> -- some other columns snipped from this usenet post
>             )
> SELECT        a.sorteercode, a.n_GenRglSjab,
> -- some other columns snipped from this usenet post
> FROM          XXX AS a  -- Table names have been changed
> INNER JOIN    YYY AS b  -- to protect the innocent
>      ON      -- Join condition snipped
> -- Create supporting index.
> CREATE INDEX  #GenRglTekst_index             ON  #GenRglTekst
>             (GenRglObject_objectsoortnaam,      GenRglObject_objectnaam)
>
> -- Snipped a lot of queries that change the value of GenRglTekst,
> -- using the other columns, plus data from lots of other tables.
> -- I have no problem with those queries.
>
> -- After all changes to GenRglTekst have been made, I run this query:
> UPDATE        dbo.GenRegel
> SET           GenRglTekst                     =
> (SELECT      GenRglTekst
>  FROM        #GenRglTekst                   AS  #
>  WHERE       #.sorteercode                   =  GenRegel.sorteercode)
> WHERE EXISTS
> (SELECT      *
>  FROM        #GenRglTekst                   AS  #
>  WHERE       #.sorteercode                   =  GenRegel.sorteercode)
> -- Clean up temp table
> DROP TABLE    #GenRglTekst
> -- End stored proc
> GO
>
> The last change I made to this stored procedure is the definition of the
> indexes. In the previous version, the primary key was nonclustered and the
> supporting index was clustered, but testing from QA showed that clustering
> the PK gave a slight performance gain. However, when I incorporated these
> changes in the stored proc, my performance suddenly plummeted. Using
> Profiler, I found that this was casued by the UPDATE statement at the end
> of the stored proc. But I also found that if I copy the entire contents of
> the stored proc to QA and run it from there, the same UPDATE statement
> runs in only 2 seconds!!
>
> You'll find the DDL for dbo.GenRegel and the execution plans for the
> queries from stored proc and from QA below, but first my questions:
>
> 1. What can be the cause of the huge performance difference between
> running from QA and running from stored proc? It can't be parameter
> sniffing, as there is no parameter involved. It also can't be the use of a
> cached plan, as I also ran tests with the WITH RECOMPILE option on the
> stored proc AND I ran a test after executing DBCC FREEPROCCACHE.
>
> 2. Is there any way to make sure that on compilation of the proc, the
> optimizer chooses the better plan (the one already chosen when running
> from QA)?
> (Note - changing the UPDATE statement to proprietary UPDATE FROM is not an
> option. GenRegel is a view with an INSTEAD OF trigger and these are not
> allowed in an UPDATE FROM statement).
>
> The DDL for the view GenRegel and the underlying base tables (snipping all
> irrelevant columns):
>
> -- Table below has 97514 rows in my test
> CREATE TABLE dbo.f_GenRegel (sorteercode   varchar(400)   NOT NULL,
>                             GenRglTekst   varchar(1000)   NULL,
>                             g_DoopAanw    char(1)         NOT NULL,
>                             g_ConvNr      int             NULL,
>                             g_Versie     char(1)         NOT NULL,
> -- snip other columns
>   CONSTRAINT    k_f_GenRegel PRIMARY KEY CLUSTERED(sorteercode,
>                                                    g_Versie))
> GO
> -- Table below has 1 row in my test
> CREATE TABLE       dbo.g_f_Conv
>                  (g_ConvNr            int                  NOT NULL
>                  ,g_Actief            varbinary(85)        NULL
> -- snip other columns
>                  ,CONSTRAINT          g_k_f_Conv           PRIMARY KEY
>                                   NONCLUSTERED(g_ConvNr))
> GO
> CREATE CLUSTERED INDEX g_x_Conv   ON dbo.g_f_Conv(g_Actief)
> GO
>
> -- The view below has 48757 rows in my test (half the rows in f_GenRegel)
> -- 41241 of these rows have a matching row in #GenRglTekst
> CREATE VIEW  dbo.GenRegel
> AS
> SELECT       f.sorteercode,
>             f.GenRglTekst,
> -- snip other columns
> FROM         dbo.f_GenRegel AS f
> LEFT JOIN    dbo.g_f_Conv AS c
>     ON      c.g_Actief   =  SUSER_SID()
> WHERE        f.g_DoopAanw = 'j'
> AND          f.g_Versie   =  CASE WHEN f.g_ConvNr = c.g_ConvNr THEN 'n'
>                                  ELSE 'o' END
>
>
>
> Best, Hugo
> -- 
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: very strange stored procedure behavior
    ... The execution plan for the sp you get with "Show Execution Plan" ... >im having trouble with a stored procedure. ... >to run the query directly (outside the stored proc) and got a much faster ... >that sql server compiles stored procedures and keeps that execution plan im ...
    (microsoft.public.sqlserver.programming)
  • Re: Gridview insert/update
    ... An approach I would use would be to bind the update statement to a ... stored procedure. ... In that stored proc you could check if the item ... I need my gridview to be able to insert a new record on ...
    (microsoft.public.dotnet.framework.aspnet.webcontrols)
  • Update statement performance decreases in stored proc
    ... less than 2 seconds from Query Analyzer takes no less than 24 minutes when ... Here are the important parts of the stored proc: ... -- Snipped a lot of queries that change the value of GenRglTekst, ... I found that this was casued by the UPDATE statement at the end ...
    (microsoft.public.sqlserver.programming)
  • Re: RETURN_VALUES ??? done... What have I gained?
    ... b (aka whipper-snapper) ... > similar questions via stored proc and can set up a function to do the work ... >> tAdoDataSet is really easy to use. ... >> dataset even though you are pointing to a stored procedure. ...
    (borland.public.delphi.database.ado)
  • TableAdapter, INNER JOINs, stored procs, and problems with Update
    ... I have a stored procedure that uses JOINs to return columns from multiple ... I also have another stored proc that that takes a series of params ... @ac2 int, ...
    (microsoft.public.dotnet.framework.aspnet)