Tune SQL Query or do it in more effient way

Posted on 2012-09-17
Last Modified: 2012-09-18
The query below is not performing well at all.  It has been sucking the CPU on SPID up to like 20,000,000.  Please show me a better way to Do this row by row update statement.

--Create CCN Number
Declare @Julian int --99999
Declare @Reel varchar(2) -- 99
Declare @Seq int  --0001
DECLARE @Process_Count int
DECLARE @Seq_char varchar(4)
DECLARE @Final_CCN varchar(11)

--Update RF_Claims
--set CCN = Null

Set @Reel = 50
--get Julian Date
Set @Process_Count = 0
Declare myCur CURSOR
Select Doc_ID,
Convert(varchar(5),bif.get_date_Julian(Log_Date)) + @Reel as CCN,
from bif.RF_Claims
Where isnull(CCN ,'') = ''
and isnull(Sent_Status,0) <>6
order by Doc_ID

Declare @Doc_ID varchar(50)
Declare @CCN varchar(50)
Declare @iLog_Date datetime

Open MyCur
Fetch Next From MyCur

   Set @Process_count = @Process_Count + 1
   print 'Processed: ' + convert(varchar(50),@Process_Count)
   --Work with each record to build CCN No
   Set @Seq = (Select isnull(max(Convert(int,right(CCN,4))),0) + 1
               From bif.RF_Claims
               where len(CcN) = 11
               and bif.get_shortdate(Log_Date) = @iLog_Date)
   Print @Seq  

  -- Set @Seq = @Seq + 1
   set @Seq_char = @Seq
   Print @Seq
   --Seq number update with Zeross
   IF Len(@Seq_Char) <> 4
      set @Final_CCN =  @CCN + Replicate('0',4 - len(@Seq_Char)) + @Seq_Char
      set @Final_CCN = @CCN + @Seq_Char
   --Update bif.RF_Claims
   print @Final_CCN

   Update bif.RF_Claims
   Set CCN = @Final_CCN
   Where Doc_ID = @Doc_ID

   Fetch Next From MyCur

PRINT 'CCNs Created'
Close MyCur
Deallocate myCur
Question by:Paul_Schroeder

    Expert Comment

    First off I would take the two select queries you have and check the execution plans.  If you highlight the query and right click you should have an option to view the execution plan.  If your table is in bad need of an index, it will indicate this.  Also if you see an table or index scans - this will cause speed issues and an index addition should help.

    In general, if your execution plan shows index seeks you should be okay in that area.  

    You should probably use a table variable instead of a cursor to loop through your data.  Cursors tend to be slow.  But if you see a major increase in speed after you add indexes, you may not need to worry about it.  Otherwise I can re-write using table variable.

    Author Comment

    Can you show me a easy example of how to use a table variable?  

    The table is indexed with a clustered index, and doing a Clustered Index Scan, Should I not use it as a Clustered Index(Doc_ID).  Doc_ID is set as primary key

    Author Comment

    LVL 25

    Accepted Solution

    I think it could be turned into a single UPDATE statement, but first need to prove via a SELECT that the right value can be determined (and efficiently too).  This is my attempt to turn the code into a SELECT.  I do not have your data etc. to test ... so I may have made some mistakes.
    Select rfc.Doc_ID
          , Convert(varchar(5),bif.get_date_Julian(rfc.Log_Date)) + @Reel as CCN
          , bif.get_Shortdate(rfc.Log_Date)
          , s.max_r_ccn 
             + row_number()over(partition by log_date_short order by rfc.Doc_ID) as Seq
          , Convert(varchar(5),bif.get_date_Julian(rfc.Log_Date)) 
            + @Reel
            + right('0000' + convert(varchar, s.max_r_ccn
                                              + row_number()over(partition by log_date_short order by rfc.Doc_ID)), 4) as Final_CCN
       from bif.RF_Claims rfc
       left join (Select bif.get_shortdate(Log_Date) as log_date_short, max(Convert(int,right(CCN,4))) as max_r_ccn
                    From bif.RF_Claims
                   where len(CcN) = 11
                   group by bif.get_shortdate(Log_Date)) s
              on s.log_date_short = bif.get_Shortdate(rfc.Log_Date)
      Where isnull(rfc.CCN ,'') = ''
        and isnull(rfc.Sent_Status,0) <>6
      order by rfc.Doc_ID

    Open in new window

    LVL 5

    Expert Comment

    I'd just like to add that Cursors and while loops are two of the worst things you can do to sql server (there are the odd exceptions to this but very very few and far between).

    I'd totally forget about execution plans and index seeks etc for now until you get this query into a set based operation. Those will be the icing on the cake once you have done the real work.

    As lwadwell has done, try and turn this into a single query or even a set of single queries if needs be but always thinking in a set based manner.

    Good luck!

    Author Comment

    Seeing this is in SQL 2000 how could I do the statement:  
    row_number()over(partition by log_date_short order by rfc.Doc_ID)
    That is not valid in SQL 2000
    LVL 25

    Expert Comment

    Oh bother!  Didn't know that.  Need to re-think.
    LVL 13

    Assisted Solution

    What if you use a temp table to hold the ordered values (with row_num) and add an identity column to simulate the row_number?

    Something like this

        RowNumber   = IDENTITY(INT,1,1)
    INTO #Customer_RowID
    FROM SalesLT.Customer c
    ORDER BY c.LastName ASC

    That should work in SQL 2k.
    Will it help?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now