Re: Update statement performance decreases in stored proc

From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 01/12/05 

Date: Wed, 12 Jan 2005 08:40:04 -0500

Do you have SET NOCOUNT ON? Have you tried using table variables instead
of temp tables? 

-- 
Andrew J. Kelly  SQL 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

  • 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: Brackets and special characters -- Brain Teaser
    ... The system is designed to use temp tables. ... I am guessing this design was for a stateless system ... > In answer to your question, the best thing you can do is modify your SQL ... >> Sorry for not begin clear, I'm talking about the code within the stored proc. ...
    (microsoft.public.sqlserver.programming)
  • Re: Update statement performance decreases in stored proc
    ... Have you had a look at the execution plan of the stored procedure? ... Here are the important parts of the stored proc: ... I found that this was casued by the UPDATE statement at the end ...
    (microsoft.public.sqlserver.programming)
  • Re: loooking for temp tables suggestions
    ... stored proc used in another stored proc. ... Then you can just reference the #temp table to access the results. ... > Create procedure Proc1 ... >> transaction). ...
    (microsoft.public.sqlserver.programming)
  • Re: Excel ADO recordset
    ... I can now bring the data from SQL to Excel. ... stored proc. ... I removed the temp table and all is well. ... >Hmm...You shouldn't have to modify any of the recordset ...
    (microsoft.public.excel.programming)
Loading