Professional Web Applications Themes

Stored Procedures - extremely slow - Microsoft SQL / MS SQL Server

Mark S (com) writes:  There are plentyful of possible explanations, and with your vague description it is very difficult to say. First thing to check is run this: SELECT objectproperty(object_id('proc_name'), 'IsQuotedIdentOn'), objectproperty(object_id('proc_name'), 'IsAnsiNullsOn') Both these should return 1. If they do not, reload the stored procedure and make sure that you have SET QUOTED_IDENTIFIER ON and SET ANSI_NULLS ON when you do this. This may have the effect of a miracle - or no effect at all. This test covers the case that there is a indexed view or an index on a computed column involved. These two features requires ...

  1. #1

    Default Re: Stored Procedures - extremely slow

    Mark S (com) writes: 

    There are plentyful of possible explanations, and with your vague
    description it is very difficult to say.

    First thing to check is run this:

    SELECT objectproperty(object_id('proc_name'), 'IsQuotedIdentOn'),
    objectproperty(object_id('proc_name'), 'IsAnsiNullsOn')

    Both these should return 1. If they do not, reload the stored procedure
    and make sure that you have SET QUOTED_IDENTIFIER ON and SET ANSI_NULLS ON
    when you do this. This may have the effect of a miracle - or no effect at
    all. This test covers the case that there is a indexed view or an index
    on a computed column involved. These two features requires these two
    settings to be on - together with four more settings. What is special
    with these two settings is that they are saved with the stored procedure.

    A more trivial explanation is that the stored procedure includes a
    query which balances between a good and a bad query plan. SQL Server
    uses a cost-based optimizer which uses statistics about the data.
    Most of the time it does a good job, but every now and they it grabs
    the wrong plan.

    Use the Profiler to narrow down exactly which statement is cause the
    problem. You can also use the Profiler to catch the query plan.

    When you run a Profiler trace, include the SP:Recomile event. If you
    have plenty of these, it may be the recompiles that kills you.

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  2. #2

    Default Re: Stored Procedures - extremely slow

    Morning,

    I do not have indexed views or an index on a computed column in this situation.
    I had quoted identifiers and ansi nulls off and tried them on. I have already
    run a trace and there was only one recompile but not on the stored procedure
    that seems to be the problem. Parameters are passed to a cursor in a stored
    proc that seems to be the cause. The stored procedure now takes 10x as long as
    it took before. Most of the events returned are SP:statementcompleted.
    I also dropped and recreated the tables involved and it got worse.
    Any other suggestions?

    Thanks!

    Erland Sommarskog wrote: 
    >
    >
    > There are plentyful of possible explanations, and with your vague
    > description it is very difficult to say.
    >
    > First thing to check is run this:
    >
    > SELECT objectproperty(object_id('proc_name'), 'IsQuotedIdentOn'),
    > objectproperty(object_id('proc_name'), 'IsAnsiNullsOn')
    >
    > Both these should return 1. If they do not, reload the stored procedure
    > and make sure that you have SET QUOTED_IDENTIFIER ON and SET ANSI_NULLS ON
    > when you do this. This may have the effect of a miracle - or no effect at
    > all. This test covers the case that there is a indexed view or an index
    > on a computed column involved. These two features requires these two
    > settings to be on - together with four more settings. What is special
    > with these two settings is that they are saved with the stored procedure.
    >
    > A more trivial explanation is that the stored procedure includes a
    > query which balances between a good and a bad query plan. SQL Server
    > uses a cost-based optimizer which uses statistics about the data.
    > Most of the time it does a good job, but every now and they it grabs
    > the wrong plan.
    >
    > Use the Profiler to narrow down exactly which statement is cause the
    > problem. You can also use the Profiler to catch the query plan.
    >
    > When you run a Profiler trace, include the SP:Recomile event. If you
    > have plenty of these, it may be the recompiles that kills you.
    >[/ref]

    Mark Guest

  3. #3

    Default Re: Stored Procedures - extremely slow

    Mark S (com) writes: 

    It's not that I want to make snide remarks, but the question verges
    of the kind "why is the sky blue"? A cost-based optimizer makes
    it estimates from statistics, and sometimes it goes wrong. And this
    can happen from one day or another because you pass some threshold
    in the volumes.

    Of course, someone might also have dropped an index.

    Anyway, since there is a hierarchy involved, one single INSERT
    statement is not easilly achieved. (But Joe Celko might discuss
    possible strategies in his books.) Redeclaring a cursor again
    and again is definitely not going help you to break the speed
    limit.

    If you change the cursor declaration to

    DECLARE BOMcursor CURSOR LOCAL STATIC

    you don't have to decallocate before the recursive call, and you
    don't have to save FindNo when you comes back. Already this is likely
    to do wonders.

    Here is a outline which iterates as long as there are levels
    in the hierarchy.

    CREATE PROCEDURE ExplodeBOM2

    CREATE TABLE #temp (ident int IDENTITY,
    -- columns as target table)

    CREATE TABLE #bills (itemno varchar(20) NOT NULL,
    itemrev char(3) NOT NULL)

    INSERT #bills(itemno, itemrev)
    VALUES (InBillNo, InBillRev)

    SELECT Level = 0
    WHILE EXISTS (SELECT * FROM #bills)
    BEGIN
    -- indent the level designator by preceding it with n hyphens,
    -- where n = Level (Level starts out = 0 from ExplodeBOM SP)
    SELECT Level = Level + 1
    SELECT CLevel = TE('-', Level) + CONVERT(varchar, Level)

    DELETE #tmp

    INSERT INTO #tmp -- You should add columns list here!
    SELECT TopBillNo, TopBillRev, Seq a.ItemNo, a.ItemRev, a.Qty,
    a.FindNo, b.ItemDesc, b.StdUM, b.StdCost, b.LastCost,
    ROUND(Qty * LastCost, 2))
    FROM stageBillDetail a
    JOIN stageItemMaster b ON a.ItemNo = b.ItemNo
    JOIN #bills c ON a.BillNo = c.itemno AND
    a.BillRev = c.itemrev
    ORDER BY a.FindNo
    OPTION (MAXDOP = 1)

    INSERT INTO stageIndendedBOM -- add column list!
    SELECT TopBillNo, TopBillRev, t.ident, CLevel, t.ItemNo,
    t.ItemRev, t.ItemDesc, t.StdUM, t.Qty, t.StdCost, t.LastCost
    FROM #tmp

    DELETE #bills

    INSERT #bills (itemno, itemrev)
    SELECT ItemNo, MAX(ItemRev)
    FROM #tmp t
    JOIN stageBillMaster s ON s.ItemNo = s.BillNo
    WHERE t.ItemRev LIKE '*%'
    END


    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

Similar Threads

  1. Net::SSH::Perl extremely slow?
    By Yon in forum PERL Modules
    Replies: 1
    Last Post: July 15th, 07:11 PM
  2. DW extremely slow when added with Stored procedure
    By samkry in forum Dreamweaver AppDev
    Replies: 0
    Last Post: May 25th, 08:35 AM
  3. Extremely Slow Printing in 6.01
    By Adam_Gadsby@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 1
    Last Post: April 1st, 07:16 PM
  4. Extremely Slow WebService
    By Laurel in forum ASP.NET Web Services
    Replies: 1
    Last Post: October 21st, 02:35 PM
  5. Database is extremely slow
    By Scott Ashby in forum Oracle Server
    Replies: 4
    Last Post: June 26th, 06:43 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139