• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 415
  • Last Modified:

Optimizing SQL Server Cursors

Hi,

I have stored procedure written in SQL Server 2000 using various CURSORS.
Because of this CURSORS, the performance of the DTS Packages and Stored Procedures is getting poor. Currently, because of CURSORS, my stored procedure execution is taking almost 2 to 3 mins to complete execution, i need to reduce it to few seconds like 5 to 10 seconds.
I have been told to optimize the SQL Server CURSORS using either select statements or set based statements or WHILE LOOPS or something else ?

Can somebody suggest me how to do the same ?
Thanks
0
sridhar_ravva
Asked:
sridhar_ravva
  • 5
  • 3
  • 2
  • +3
1 Solution
 
tigin44Commented:
can you provide your sp code...
0
 
lofCommented:
set based statements are probably not the best option but let us see some code first
0
 
chapmandewCommented:
While loops aren't faster than cursors. You can use a fast forward cursor to speed your loop up a bit...SET based is probably faster though.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
sarabhaiCommented:
alternative for cursor u may use table variable or temp table and process using loop.
0
 
chapmandewCommented:
alterative is just as weak as a loop.
0
 
sridhar_ravvaAuthor Commented:
There are almost 2 milion records in tables CO_OCRMemsPrior and CO_OCRMems..

Also, this SP is taking around 1.20 minutes for complete execution.

Please suggest me like how to optimize this SP Cursors code...I want to replace cursors/insert squeries using some efficient query...kindly suggest me on this..Thanks

Here is the code for my SP :

ALTER PROCEDURE ImportOverlaysCO_SRI AS

select      distinct
      COP.INDV_HRN as PHRN, CO.INDV_HRN as CHRN,
      COP.PHNM_DISPL_NM as PNAME, CO.PHNM_DISPL_NM as CNAME,
      COP.indv_ssn as PSSN, CO.indv_ssn as CSSN,
      COP.indv_dob as PDOB, CO.indv_dob as CDOB,
      COP.XXSEX_CD as PGNDR, CO.XXSEX_CD as CGNDR,
      COP.zip_cd as PZIP, CO.zip_cd as CZIP
into #OVLHRN
from CO_OCRMemsPrior as COP
      inner join CO_OCRMems as CO
            on
            COP.indv_hrn = CO.indv_hrn


declare @PHRN varchar(9) ,@CHRN varchar(9)
declare @PNM varchar(30) ,@CNM varchar(30)
declare @PSSN varchar(9) ,@CSSN varchar(9)
declare @PDOB varchar(8) ,@CDOB varchar(8)
declare @PGNDR varchar(1),@CGNDR varchar(1)
declare @PZIP varchar(5) ,@CZIP varchar(5)
declare @score int
declare @ovltyp varchar(4)

declare idx CURSOR READ_ONLY for
select      PHRN,CHRN,
      PNAME, CNAME,
      PSSN,CSSN,
      PDOB,CDOB,
      PGNDR,CGNDR,
      PZIP,CZIP
From #OVLHRN

select @score as score, @ovltyp as ovltyp, #OVLHRN.* into #HRNOLV From #OVLHRN WHERE 1=2

open idx

fetch next from idx into
      @PHRN,@CHRN,
      @PNM,@CNM,
      @PSSN,@CSSN,
      @PDOB,@CDOB,
      @PGNDR,@CGNDR,
      @PZIP,@CZIP
while @@FETCH_STATUS = 0
begin
      set @score = 0
      set @ovltyp = ''

      if @PSSN <> @CSSN
      begin
            set @score = @score + 1
            set @ovltyp = @ovltyp + 'S'
      end

      if @PDOB <> @CDOB
      begin
            set @score = @score + 2
            set @ovltyp = @ovltyp + 'D'
      end

      
      if @PGNDR <> @CGNDR
      begin
            set @score = @score + 1
            set @ovltyp = @ovltyp + 'G'
      end


      if @PZIP <> @CZIP
      begin
            set @score = @score + 1
            set @ovltyp = @ovltyp + 'Z'
      end
      
      if @score > 2
      begin

            insert into       #HRNOLV(score,
                              ovltyp,                              
                              PHRN,CHRN,
                              PNAME,CNAME,
                              PSSN,CSSN,
                              PDOB,CDOB,
                              PGNDR,CGNDR,
                              PZIP,CZIP)
                         values(@score,
                              @ovltyp,
                              @PHRN,@CHRN,
                              @PNM,@CNM,
                              @PSSN,@CSSN,
                              @PDOB,@CDOB,
                              @PGNDR,@CGNDR,
                              @PZIP,@CZIP)
      end
      
      fetch next from idx into
            @PHRN,@CHRN,
            @PNM,@CNM,
            @PSSN,@CSSN,
            @PDOB,@CDOB,
            @PGNDR,@CGNDR,
            @PZIP,@CZIP
end

close idx
deallocate idx

drop table #OVLHRN

insert into HRN_Overlay(XXRGN_ID,
                    score,
                    ovltyp,
                      PHRN,CHRN,
                    PNAME,CNAME,
                    PSSN,CSSN,
                    PDOB,CDOB,
                    PGNDR,CGNDR,
                    PZIP,CZIP) select '16', #HRNOLV.* from #HRNOLV         
drop table #HRNOLV

select distinct GROUP_NBR,HRN into #GRPHRNS FROM Regional_HRN WHERE REGION = '16'

Update HRN_Overlay
set HRN_Overlay.GRP_ID = #GRPHRNS.GROUP_NBR
from HRN_Overlay inner join #GRPHRNS on HRN_Overlay.CHRN = #GRPHRNS.HRN

drop table #GRPHRNS
0
 
lofCommented:
In general it should be easy to do by removing temporary table, cursor and big number of sets.

Unfortunately I wrote the code directly here on the website so I have no control of the syntax and there may by a type or two. I won't be able to test it or post more complex example until about 13 hours from now but I hope the short example is useful anyway as it shows the way.

insert into HRN_Overlay(XXRGN_ID,
                    score,
                    ovltyp,
                      PHRN,CHRN,
                    PNAME,CNAME,
                    PSSN,CSSN,
                    PDOB,CDOB,
                    PGNDR,CGNDR,
                    PZIP,CZIP)
select      distinct
      case when COP.indv_ssn <> CO.indv_ssn  
             or COP.XXSEX_CD <> CO.XXSEX_CD
             or COP.zip_cd <> CO.zip_cd
           then 1
           when COP.indv_dob <> CO.indv_dob then 2
           else 0
      end as Score,
      case when COP.indv_ssn <> CO.indv_ssn then 'S'
           when COP.XXSEX_CD <> CO.XXSEX_CD then 'D'
           when COP.zip_cd <> CO.zip_cd then 'Z'
           when COP.indv_dob <> CO.indv_dob then 'G'
           else ''
      end as OvlType,
      COP.INDV_HRN as PHRN, CO.INDV_HRN as CHRN,
      COP.PHNM_DISPL_NM as PNAME, CO.PHNM_DISPL_NM as CNAME,
      COP.indv_ssn as PSSN, CO.indv_ssn as CSSN, 
      COP.indv_dob as PDOB, CO.indv_dob as CDOB,
      COP.XXSEX_CD as PGNDR, CO.XXSEX_CD as CGNDR,
      COP.zip_cd as PZIP, CO.zip_cd as CZIP
from CO_OCRMemsPrior as COP 
      inner join CO_OCRMems as CO
            on
            COP.indv_hrn = CO.indv_hrn

Open in new window

0
 
sridhar_ravvaAuthor Commented:
I need to test this sql query on monday...thanks for this.

One more thing..you are missing one field in that insert query...i.e HRN_Overlay.GRP_ID ...how do we include this field in the same insert query...we need to optimize the below query to include it in the same above insert query since the below query was part of the stored procedure...your thoughts on this indeed appreciated...thanks

select distinct GROUP_NBR,HRN into #GRPHRNS FROM Regional_HRN WHERE REGION = '16'

Update HRN_Overlay
set HRN_Overlay.GRP_ID = #GRPHRNS.GROUP_NBR
from HRN_Overlay inner join #GRPHRNS on HRN_Overlay.CHRN = #GRPHRNS.HRN

drop table #GRPHRNS
0
 
lofCommented:
Ok, I've updated the sample again to include the last part into the same query.
the following code should work as long as in Regional_HRN the pair REGION and Regional_HRN is unique. if it is not you will end up with duplicated entries in HRN_Overlay.


insert into HRN_Overlay(XXRGN_ID,
                    score,
                    ovltyp,
                      PHRN,CHRN,
                    PNAME,CNAME,
                    PSSN,CSSN,
                    PDOB,CDOB,
                    PGNDR,CGNDR,
                    PZIP,CZIP,
					GRP_ID
					)
select      distinct
      case when COP.indv_ssn <> CO.indv_ssn  
             or COP.XXSEX_CD <> CO.XXSEX_CD
             or COP.zip_cd <> CO.zip_cd
           then 1
           when COP.indv_dob <> CO.indv_dob then 2
           else 0
      end as Score,
      case when COP.indv_ssn <> CO.indv_ssn then 'S'
           when COP.XXSEX_CD <> CO.XXSEX_CD then 'D'
           when COP.zip_cd <> CO.zip_cd then 'Z'
           when COP.indv_dob <> CO.indv_dob then 'G'
           else ''
      end as OvlType,
      COP.INDV_HRN as PHRN, CO.INDV_HRN as CHRN,
      COP.PHNM_DISPL_NM as PNAME, CO.PHNM_DISPL_NM as CNAME,
      COP.indv_ssn as PSSN, CO.indv_ssn as CSSN, 
      COP.indv_dob as PDOB, CO.indv_dob as CDOB,
      COP.XXSEX_CD as PGNDR, CO.XXSEX_CD as CGNDR,
      COP.zip_cd as PZIP, CO.zip_cd as CZIP,
      Regional_HRN.GROUP_NBR
from CO_OCRMemsPrior as COP 
  inner join CO_OCRMems as CO
        on COP.indv_hrn = CO.indv_hrn
  left outer join Regional_HRN  
        on REGION = '16' and CO.CHRN = Regional_HRN 

Open in new window

0
 
sridhar_ravvaAuthor Commented:
thanks for the updated query...appreciated very much...i need to test this query in the sql server database on monday...Merry christmas...and wish you all a very happy new year...
0
 
Anthony PerkinsCommented:
>>i need to test this query in the sql server database on monday<<
How is it going?
0
 
sridhar_ravvaAuthor Commented:
I really appreciate all the responses to my questions...thanks so much.

i will get back to this question little bit later since i am working on some other things in my project..so when i get to that part...i can add new question for this but for now...you can very well close this question.
Thanks
0
 
sridhar_ravvaAuthor Commented:
found the solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 5
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now