Professional Web Applications Themes

Very slow after applying sp3! - Microsoft SQL / MS SQL Server

Albert Have you tried update statistics? "Albert Hew" <com> wrote in message news:uBQvL#phx.gbl...  procedure  from  the  7 ...

  1. #1

    Default Re: Very slow after applying sp3!

    Albert
    Have you tried update statistics?

    "Albert Hew" <com> wrote in message
    news:uBQvL#phx.gbl... 
    procedure 
    from 
    the 



    Uri Guest

  2. #2

    Default Re: Very slow after applying sp3!

    Uri
    I did. It is not showing any difference !

    In fact, when I re-installed the SQL2000 server, I 'bcp'ed in the data
    and re-built the indexes.



    Albert


    "Uri Dimant" <co.il> wrote in message
    news:phx.gbl... [/ref]
    sp2. [/ref]
    to 
    > procedure 
    > from [/ref]
    is 
    > the [/ref]
    and 
    >
    >[/ref]


    Albert Guest

  3. #3

    Default Re: Very slow after applying sp3!

    What is the difference in the execution of the 2 plans? If you can narrow
    down what part is effected and post it we might be able to suggest an
    alternative method. If you can get a repro script you may want to give ms
    pss a call.

    http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL Support
    http://www.mssqlserver.com/faq/general-pss.asp MS PSS

    --

    Andrew J. Kelly
    SQL Server MVP


    "Albert Hew" <com> wrote in message
    news:uBQvL%phx.gbl... 
    procedure 
    from 
    the 



    Andrew Guest

  4. #4

    Default Re: Very slow after applying sp3!

    Dear Andrew

    Thanks for the response.
    I tried generating the execution plan but failed to do so because the
    SQL2000 optimizer refuses to generate execution plan when it encounters a
    temp table. I may be wrong, anyone could offer some suggestion to generate
    the execution plan for code using temp table ??

    Anyway, I went a step further by diagnosting the store procedure.
    Interestingly this is what I found out.

    For the same machine running SQL 2000 with sp2, execution of the following
    code was excellent, very fast... took me about 15 minute to generate a
    monthly report. However, after applying sp3, the CPU usage spike irregulary
    and it took longer than 5 hours to complete the same job.

    SELECT a.shipment_profile_id,
    a.scan_type_c,

    a.scan_exception_c,

    a.scan_dt,

    a.track_loc_c

    INTO #asia_new_tb FROM scan a

    WHERE EXIST ..condition1

    and condition2

    and condition3

    ...

    ..

    ...

    DECLARE asia_new CURSOR for

    SELECT shipment_profile_id,

    scan_type_c,

    scan_exception_c,

    scan_dt,

    track_loc_c

    FROM #asia_new_tb

    OPEN asia_new



    FETCH asia_new into shipment_id,

    scan_type,

    scan_exce,

    scan_dt,

    track_loc



    CLOSE asia_new

    DEALLOCATE asia_new

    DELETE #asia_new_tb



    All I did was ...made minor modification to the above code by declaring the
    cursor to a user table instead of the temp table. So the code looks like the
    following (running in a machine with SQL 2000 and sp3).

    The execution of this code by SQL2000 with sp 2 was extreme slow. However,
    it works extremely well in SQL2000 with sp3.
    Appreciate you or other can help to verify this and even perhaps find an
    explanation for this.



    DECLARE asia_new CURSOR for

    select a.shipment_profile_id,

    a.scan_type_c,

    a.scan_exception_c,

    a.scan_dt,

    a.track_loc_c

    FROM scan a

    WHERE EXIST..condition1

    AND condition2

    AND condition3





    OPEN asia_new



    FETCH asia_new INTO shipment_id,

    scan_type,

    scan_exce,

    scan_dt,

    track_loc



    CLOSE asia_new

    DEALLOCATE asia_new

    DELETE #asia_new_tb



    "Andrew J. Kelly" <com> wrote in message
    news:phx.gbl... [/ref]
    sp2. [/ref]
    to 
    > procedure 
    > from [/ref]
    is 
    > the [/ref]
    and 
    >
    >[/ref]


    Albert Guest

Similar Threads

  1. Applying Patch
    By NVL-Tom in forum Coldfusion Server Administration
    Replies: 1
    Last Post: March 29th, 01:55 PM
  2. applying CSS to JS?
    By CozmoRaine in forum Macromedia Dynamic HTML
    Replies: 5
    Last Post: July 22nd, 03:45 PM
  3. GPO not applying!
    By Grant in forum Windows Server
    Replies: 1
    Last Post: June 3rd, 06:09 PM
  4. SLOW DOWN..Why is my Photoshop CS Super Slow in PANTHER?
    By Aerosyn-Lex@adobeforums.com in forum Adobe Photoshop Mac CS, CS2 & CS3
    Replies: 4
    Last Post: February 23rd, 10:42 PM
  5. need help with applying a condition
    By Mike in forum Microsoft Access
    Replies: 2
    Last Post: September 5th, 01:44 PM

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