Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Optimizing SQL Server Cursors

Posted on 2009-12-20
15
Medium Priority
?
413 Views
Last Modified: 2012-05-08
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
Comment
Question by:sridhar_ravva
  • 5
  • 3
  • 2
  • +3
13 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 26092457
can you provide your sp code...
0
 
LVL 10

Expert Comment

by:lof
ID: 26092798
set based statements are probably not the best option but let us see some code first
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 26092827
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 9

Expert Comment

by:sarabhai
ID: 26093364
alternative for cursor u may use table variable or temp table and process using loop.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 26093455
alterative is just as weak as a loop.
0
 

Author Comment

by:sridhar_ravva
ID: 26102205
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
 
LVL 10

Expert Comment

by:lof
ID: 26102599
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
 

Author Comment

by:sridhar_ravva
ID: 26123036
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
 
LVL 10

Expert Comment

by:lof
ID: 26123172
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
 

Author Comment

by:sridhar_ravva
ID: 26123189
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26275922
>>i need to test this query in the sql server database on monday<<
How is it going?
0
 

Accepted Solution

by:
sridhar_ravva earned 0 total points
ID: 26468594
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
 

Author Closing Comment

by:sridhar_ravva
ID: 31668319
found the solution.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question