August 2, 2010 at 12:13 am
I have some code is called repetitively:
set nocount on
PRINT GETDATE()
DECLARE @customerID ID, @Start DateTime, @End DateTime = GETDATE(), @Cnt INT = 0
select customer_id INTO #c from customer
PRINT @@rowcount
select top 1 @customerID = customer_id from #c
DO WHILE @customerid IS NOT NULL
BEGIN
exec usp_customer @customer_id = @customerID, @no_cc = 1
SET @Cnt = @Cnt + 1
delete from #c where customer_id = @customerid
IF @Cnt % 1000 = 0 PRINT GETDATE()
select top 1 @customerID = customer_id from #c
END
PRINT GETDATE()
drop table #c
This code iterates though the customer dataset and calls a routine for each customer. I know most of the arguments about creating set based routine vs iteration routines. However, for my requirements, I found that making a set based routine work as a single iteration routine was very slow.
My issue seems to be in resource management. As shown below, each time stamp represents 1000 records. The first 10000 go very fast, then it drops off considerably. I think this is some kind of resource issue. I have also included the top part of 'ups_customer'.
Question: How do I deallocate / drop / clear declared variables?
Thank You!
Mike
USP_Customer:
SET NOCOUNT ON
DECLARE @Customer INT=NULL,
@today Date=CONVERT( date, GETDATE())
DECLARE @listatus_code_id Int,
@sch_key char(30),
@no_override_fl bit,
@rc int,
@id ID
SET @Customer= @customer_id
DECLARE
@invdate DATE,
@terms CHAR(30),
@days INT,
@order_hdr_id ID,
@current_amt MONEY,
@overdue INT,
@late_fl BIT,
@duedate DATE,
@address CHAR(30),
@address2 CHAR(30),
@po_box_fl BIT,
@cr_limit MONEY,
@Balance MONEY,
@savedRowVersion INT,
@rowversion INT,
@day30 BIT,
@day60 BIT,
@day90 BIT,
@over_fl BIT,
@company_id ID,
@customer_auto_inactive_days INT,
@inactive FLAG,
@Set bit,
@clear bit,
@action_code varchar(100),
@no_change_no_reset_fl bit=0,
@check_for_change_fl bit=0,
@status_code_Id ID,
@return FLAG,
@crdt_hold FLAG,
@credit_review_limit_fl FLAG,
@credit_review_terms_fl FLAG,
@wholesale FLAG,
@tax_id CHAR(20),
@state_requires_resale_num_fl FLAG,
@ship_address CHAR(30),
@ship_address2 CHAR(30),
@payment_plan_active_fl FLAG,
@payment_plan_period CHAR(30),
@payment_plan_frequency CHAR(30),
@payment_plan_start_dt date,
@payment_plan_end_dt date,
@last_payment_dt DATE,
@prospect FLAG
--PRINT 'Load Customer/Company data...'
SELECT@company_id= company.company_id,
@customer_auto_inactive_days= company.customer_auto_inactive_days ,
@inactive= customer.inactive,
@crdt_hold= customer.crdt_hold,
@credit_review_limit_fl= customer.credit_review_limit_fl,
@credit_review_terms_fl= customer.credit_review_terms_fl,
@wholesale= customer.wholesale,
@tax_id= customer.tax_id,
@state_requires_resale_num_fl= ch_state.state_requires_resale_num_fl,
@ship_address= sAddress.address,
@ship_address2= sAddress.address2,
@address= address.address,
@address2= address.address2,
@payment_plan_active_fl= customer.payment_plan_active_fl,
@payment_plan_period= customer.payment_plan_period,
@payment_plan_frequency= customer.payment_plan_frequency,
@payment_plan_end_dt= customer.payment_plan_end_dt,
@payment_plan_start_dt= customer.payment_plan_start_dt,
@last_payment_dt= customer.last_payment_dt ,
@prospect= customer.prospect
FROM company
inner join customer on company.company_id = customer.company_id AND customer_id = @customer
left join address ON customer.customer_id = address.customer_id
AND address.addr_type = 'MAIN' AND address.primary_fl = 1
inner join ch_state ON address.state = ch_state.ch_state
left join address sAddress ON customer.customer_id = saddress.customer_id
AND saddress.addr_type = 'SHIP' AND saddress.primary_fl = 1
Results:
Aug 1 2010 10:02PM (Start)
25964 (Record Count)
Aug 1 2010 10:03PM
Aug 1 2010 10:04PM
Aug 1 2010 10:04PM
Aug 1 2010 10:05PM
Aug 1 2010 10:06PM
Aug 1 2010 10:07PM
Aug 1 2010 10:08PM
Aug 1 2010 10:09PM
Aug 1 2010 10:15PM
Aug 1 2010 10:33PM
Aug 1 2010 10:46PM
Aug 1 2010 11:02PM
August 2, 2010 at 9:35 am
mike 57299 (8/2/2010)
...My issue seems to be in resource management. As shown below, each time stamp represents 1000 records. The first 10000 go very fast, then it drops off considerably. I think this is some kind of resource issue. I have also included the top part of 'ups_customer'.
...
Question: How do I deallocate / drop / clear declared variables?
...
1. Resource issue? May be, but it is nothing to do with variables.
2. You cannot deallocate or drop variables. You can clear them by setting them to NULL - however it is as per point 1. nothing to do with resource management and will not make your code running any faster.
Could you please supply the full code of the thing where you looping through every customer id. Do you use transaction there?
What your ups_customer does? A lot of updates/inserts/deletes? It may fill transaction log and cause checkpoint...
But what I think really causing the slowness after 10000 records is recompilation. As you changing your temp table multiple times, the SQL desides to recompile your looping query again and again. Try to use a cursor: just allocate the cursor for select from your temp table, loop through customerid's calling your usp_customer and do no deletes. In your case using cursor instead of loop will bring no difference except it should not cause multiple recompilation...
Also, are you sure that it is impossible to do what you do in usp_customer in a set-based query?
August 2, 2010 at 10:12 am
I will try the cursor. The set based option works for when I want to do all customers but is significantly slower when I need to do only one customer. Since I only do the entire customer set once a day and I do singleton hits thousands of times per day, I would rather pay for it on the group.
Mike
August 2, 2010 at 10:27 am
mike 57299 (8/2/2010)
I will try the cursor. The set based option works for when I want to do all customers but is significantly slower when I need to do only one customer. Since I only do the entire customer set once a day and I do singleton hits thousands of times per day, I would rather pay for it on the group.Mike
A cursor to update single customer record?
Cool idea :-D:w00t::-D
In your case, I would suggest to have one proc to update selected single customer (without any cursor) and another proc to update all customers in set-based manner...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply