Re: Very slow after applying sp3!
From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 08/05/03
- Next message: Dennis Redfield: "Re: SQL DMO from java"
- Previous message: drynrotting: "maximum number of accounts....."
- In reply to: Albert Hew: "Re: Very slow after applying sp3!"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Tue, 5 Aug 2003 07:24:54 -0400
Since you didn't supply all the code I can't see exactly what your doing but
you would be best served to get rid of the cursor altogether and use set
based commands where possible. You could also have tried using a STATIC
cursor and that may have helped and will reduce the number of locks held
while processing.
-- Andrew J. Kelly SQL Server MVP "Albert Hew" <voonkong.hew@fedex.com> wrote in message news:OvaoZIyWDHA.1620@TK2MSFTNGP12.phx.gbl... > 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" <sqlmvpnooospam@shadhawk.com> wrote in message > news:eDRGOecWDHA.2276@TK2MSFTNGP10.phx.gbl... > > 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" <voonkong.hew@fedex.com> wrote in message > > news:uBQvL%23WWDHA.2352@TK2MSFTNGP12.phx.gbl... > > > > > > > > > I encountered a weird problem. > > > > > > I coded a stored procedure which is used to generate the monthly report, > > > usually takes about 15 minutes to complete in the SQL2000 server with > sp2. > > > However, after applying the SQL 2000 sp3a, it takes longer than 5 hours > to > > > complete! The CPU usage has been approaching 100% when the store > > procedure > > > is being executed. > > > > > > To confirm this is resulted from the sp3a installation. I actually > > > re-install the Windows 2000 server (including sp4) and SQL 2000 server > > from > > > scratch. I also applied the SQL 2000 service pack 2. When I re-run the > > > store procedure, the execution time needed by the same store procedure > is > > > only about 15 minutes and CPU usage is back to normal, about 40% during > > the > > > execution time. > > > > > > > > > > > > The SQL 2000 server is running on HP TC4100 machine with 2G RAM, 2CPUs > and > > 7 > > > SCSI hard disk. > > > > > > Greatly appreciate if anyone could offer some advice on how to approach > > > this. > > > > > > > > > > > > Albert > > > > > > > > > > > >
- Next message: Dennis Redfield: "Re: SQL DMO from java"
- Previous message: drynrotting: "maximum number of accounts....."
- In reply to: Albert Hew: "Re: Very slow after applying sp3!"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
