Tune SQL Query or do it in more effient way

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
READ_ONLY KEYSET FOR
Select Doc_ID,
Convert(varchar(5),bif.get_date_Julian(Log_Date)) + @Reel as CCN,
bif.get_Shortdate(Log_Date)
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
INTO
@Doc_ID,
@CCN,
@iLog_Date

WHILE @@FETCH_STATUS = 0
   BEGIN
   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
   BEGIN
      set @Final_CCN =  @CCN + Replicate('0',4 - len(@Seq_Char)) + @Seq_Char
   END
   ELSE
   BEGIN
      set @Final_CCN = @CCN + @Seq_Char
   END
   
   --Update bif.RF_Claims
   print @Final_CCN

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

   Fetch Next From MyCur
   INTO
   @Doc_ID,
   @CCN,
   @iLog_Date
END

PRINT 'CCNs Created'
Close MyCur
Deallocate myCur
Paul_SchroederAsked:
Who is Participating?
 
lwadwellConnect With a Mentor Commented:
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

0
 
Jamieo1Commented:
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.
0
 
Paul_SchroederAuthor Commented:
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
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Paul_SchroederAuthor Commented:
0
 
DavidMorrisonCommented:
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!
0
 
Paul_SchroederAuthor Commented:
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
0
 
lwadwellCommented:
Oh bother!  Didn't know that.  Need to re-think.
0
 
LIONKINGConnect With a Mentor Commented:
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


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

That should work in SQL 2k.
Will it help?
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.

All Courses

From novice to tech pro — start learning today.