[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Tune SQL Query or do it in more effient way

Posted on 2012-09-17
Medium Priority
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

ID: 38407967
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

ID: 38407972
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

ID: 38408000
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!

LVL 25

Accepted Solution

lwadwell earned 1000 total points
ID: 38408299
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


Expert Comment

ID: 38408842
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

ID: 38409504
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

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

Assisted Solution

LIONKING earned 1000 total points
ID: 38409797
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

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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

834 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