SQL script taking long time to extract data

Question

This is the script that is taking a very long time

USE [r_prod]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:   Drew Borden
-- Create date: 4/16/2009
-- Description: Procedure to populated subdivision extract table
-- =============================================
IF EXISTS(SELECT * FROM sys.procedures WHERE name='sp_extract_subdivision')
BEGIN
    DROP PROCEDURE sp_extract_subdivision
END
GO

CREATE PROCEDURE sp_extract_subdivision 
    @subdivsion_cd char(2) 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

declare @strap varchar(25)

-- Clear existing record
delete from dbo.subdivision_extract

-- Select list of straps to loop through
declare strapList cursor for
select strap from dbo.parcel where county_cd = @subdivsion_cd

--Loop through straps and populate extract table values
BEGIN TRY
 OPEN strapList
 FETCH NEXT FROM strapList INTO @strap
 WHILE @@FETCH_STATUS = 0
 BEGIN
  IF @strap IS NOT NULL
  BEGIN
   insert into dbo.subdivision_extract (acct_num) values (RTRIM(@strap))
   exec sp_extract_parcel @strap
   exec sp_extract_detail @strap
   exec sp_extract_lnd_c @strap
   exec sp_extract_parcel_flg @strap
   exec sp_extract_owner @strap
   exec sp_extract_mail @strap
   exec sp_extract_legal_ln @strap
   exec sp_extract_site @strap 
   exec sp_extract_condo_unit @strap
   exec sp_extract_personal_x @strap
   exec sp_extract_personal_x_dist @strap
   exec sp_extract_phase_in @strap
   exec sp_extract_p_tax_dist @strap
   exec sp_extract_parcel_rel @strap
   exec sp_extract_entzone @strap
   exec sp_extract_dates @strap
   exec sp_extract_sales @strap
   exec sp_extract_sale_dtl @strap
   exec sp_extract_pchar @strap
   exec sp_extract_protest @strap

  END
  FETCH NEXT FROM strapList INTO @strap
 END
 CLOSE strapList
 DEALLOCATE strapList
END TRY

BEGIN CATCH
 SELECT ERROR_NUMBER() as ErrorNumber, 
   ERROR_MESSAGE() as ErrorMessage,
   ERROR_PROCEDURE() as ExecutingProcedure,
   ERROR_LINE() as LineNumber
 CLOSE strapList
 DEALLOCATE strapList  
END CATCH
END
GO

Any way to speed this up?

Answer 1

You are calling several stored procedures for each loop in this stored. I don't know what the others do, but it seems that they are also querying/modifying few data. You should consider joining the storeds in a single one and perform the querys in blocks of several records instead of loop for every strap.

Answer 2

The best way to speed this up involves writing versions of the stored procedures that you're calling with every row so that they run against the whole set, and ditching your cursor altogether. Otherwise, you might get a small benefit from specifying the cursor as FORWARD_ONLY but I don't see much else that can be done.

Answer 3

The real problem here is the fact that you're calling 20 stored procedures sequentially via a cursor.

I hate cursors for a start, and have come up with solution for this on previous projects.

Instead of getting a variable from the cursor, are you able to run the 20 stored procedures sequentially for all of the data?

I suggest having a temporary table with the primary key of the data and a status integer which shows which have been processed and to which point. Each stored procedure can then be called in order to process all of the rows.

If you really want to do a nice job with it, have each stored proc process say 5% of the rows at a time, and then allow a small pause using WAITFOR before looping until all of the records have been processed by each stage. If the process time for each is reasonable, it will make sure that locks can still be allocated to other processes so more important processes do not time out because they can't acquire a lock.

How long does the delete from dbo.subdivision_extract take? If it takes a while and the log is not required (and you have no triggers on the table), try changing it to TRUNCATE TABLE dbo.subdivision_extract

TLDR: Redevelop the stored procs to process all of the data, then you'll only need to call 20 stored procs once each.

Answer 4

If you are extracting data to a text file, you owe it to yourself to do it in a set-based manner or at least use SSIS. A cursor running mulitple stored procs for each row is the absolute worst method you can use for this sort of thing. I'd bet you can do this in an SSIS package and take minutes instead of 9 hours.

Answer 5

Yes, actualy is extreamly easy to fix: measure what is slow, then optimize the slow part.

All you posted is a T-SQL script that is as opaque as it can get in regard to performance. A DELETE, a SELECT, a cursor iteration with an INSERT and a bunch of EXECs. The problem can be anywhere in these, so the best solution is to measure and see where the problem might be.

Take the script and add a PRINT GETDATE(); at start, after the DELETE, after the first FETCH, then after each EXEC and execute one single iteration (remove the FETCH inside the loop). Look at the PRINT output, you can deduce the time it takes to execute each step from it. Does any of them stand out?

Attach Profiler and monitor for the event SP:StmtCompleted with a filter on Duration. Run again one single iteration of the extraction loop. Which statements stand out highest in Duration?

Run the script for a single iteration in SSMS, but check the Include Actual Execution Plan button in the toolbar. In the resulted execution show, which statements stand out as high cost relative to the batch?

You must narrow down your problem, the script as it is it's impossible to diagnose, there isn't any actual work done in this script, it just calls other procedures to do the work. Once you identified the actual slow statements inside the procedures invoked by this script, then you should post again, give the exact statment that has the problem and the exact schema of your data (including all index definitions), and ask solutions for specific problems.

While in theory a set oriented processing could have better performance than a cursor, in practice it would be probably impossible to write a single statement that extracts the same information as 20 stored procedure execution invocations, unless those procedure are extremly trivial single SELECTs.