Cursor Problem

[ScrewBalll]

Hi All,
I've got a problem in that I want to carry out a procedure without using a cursor.

The problem I have is that
I'm importing a price and a validity period into a table. If the validity period falls within another period then I want other records created to explain the behaviour.

Its probably best if I show you an example:

Data already in the table:
[Price1] [Price2] [ValidFrom] [ValidTo]
0 10 01/01/05 31/12/05

Incoming Data:-
[Price1] [Price2] [ValidFrom] [ValidTo]
20 0 01/02/05 15/02/05

I want this to map to:
[Price1] [Price2] [ValidFrom] [ValidTo]
0 £10 01/01/05 31/01/05
£20 £10 01/02/05 15/02/05
0 £10 16/02/05 31/12/05

I've found a solution involving a cursor, but it takes forever to run (Its meant to be a daily update but i found that using the cursor would make it take more than 24hrs).

Here's a snippet of my code:

--Create temp table to hold final dates and prices
CREATE TABLE #cursor_price
	(
	DateFrom datetime,
	DateTo datetime,
	Price1 decimal(5,2),
	Price2 decimal(5,2)
	)

--Temp table to hold individual dates
CREATE TABLE #test
(	
	Type char(1),
	Date Datetime
)

--Populate date table from old and new results
INSERT INTO #test
Select 
	's',		--start
	ValidFrom 

From tblCurrentData

INSERT INTO #test
Select  'F', ValidTO	--finish 
from tblCurrentData

--Insert from new data
INSERT INTO #test
Select 
	's',
	ValidFrom
From NewData

INSERT INTO #test
Select  'F',
         ValidTo
From NewData
-----------------------------------------------


-- Use cursor to loop through date results in order
DECLARE cursor1 SCROLL CURSOR
FOR
Select  type, date from #test  group by date, type order by [date] /*ARRANGES ALL DATES IN ORDER*/

DECLARE  @Type1 char(1), @Date1 datetime, @Type2 char(1), @Date2 datetime

OPEN cursor1
FETCH cursor1 into  @Type1, @Date1 --InitialValues for loop

-- Carry out loop to iterate through dates setting up periods
WHILE @@FETCH_STATUS = 0
BEGIN
	
	FETCH cursor1 into  @Type2, @Date2 	/*each row selected into these variables */
	
	INSERT INTO #cursor_price(datefrom,dateto)
	SELECT					/*Previous Row Values*/
		CASE				
			WHEN @Type1 = 's' then @DATE1
			WHEN @Type1 ='F' then @Date1 +1
		END, 
		CASE				/*Current row values*/
			WHEN @Type2 = 's' then @DATE2 -1
			WHEN @Type2 ='F' then @Date2
		END
	
	WHERE 	CASE				--Where start date is before end date
			WHEN @Type1 = 's' then @DATE1 
			WHEN @Type1 ='F' then @Date1 +1
		END <= CASE
			WHEN @Type2 = 's' then @DATE2 -1
			WHEN @Type2 ='F' then @Date2
		END
	
	SET @TYPE1=@TYPE2
	SET @DATE1= @DATE2
END

DEALLOCATE cursor1

--update prices values for individual periods

UPDATE #cursor_price	-- from old values
SET	TradePrice =	pr.TradePrice,
	RetailPrice =	pr.RetailPrice,
	DirectPrice =	pr.directPrice,
	FullRetailPrice = pr.FullretailPrice		
FROM  CURRENTDATA pr, #cursor_price cp
WHERE pr.ValidFrom <=cp.DateFrom  AND pr.ValidTo >= cp.DateTo
--logical condition to load in values


UPDATE #cursor_price	-- from new values
SET	TradePrice =	ISNULL(ps.TradePrice,cp.TradePrice),
	RetailPrice =	ISNULL(ps.RetailPrice,cp.RetailPrice),
	DirectPrice =	ISNULL(ps.directPrice,cp.directPrice),
	FullRetailPrice = ISNULL(ps.FullretailPrice,cp.FullretailPrice)		
FROM  NEWDATA ps, #cursor_price cp
WHERE ps.ValidFrom <=cp.DateFrom  AND ps.validto >= cp.DateTo

The basic idea is I take all the individual dates and put them in order in a table. Then I use a cursor to iterate through them and sort out intervals into another table. Finally I update the interval table with valid prices. But this takes so very long when dealing with lots of data.

Any experts out there with some ideas ad I really need help on this one.

 

[tc3596]

Wow,
How much data are you working with? If you have indexes defined. It may be more advantageous to drop them in the beginning and recreate them at the end to speed things up. Not sure if this will speed things up, but you could say thie following statement vs what you wrote above...


--Insert from new data
INSERT INTO #test
Select
's',
ValidFrom
From NewData

Union All

Select 'F',
ValidTo
From NewData



What is this?
FETCH cursor1 into @Type1, @Date1 --InitialValues for loop

-- Carry out loop to iterate through dates setting up periods
WHILE @@FETCH_STATUS = 0
BEGIN

FETCH cursor1 into @Type2, @Date2

Can you do that? You are fetching values into cursor1 twice using different values (FETCH cursor1 into @Type1, @Date1 followed by FETCH cursor1 into @Type2, @Date2)

 

[ScrewBalll]

Cheers for your advice, I just used the
'FETCH cursor1 into @Type1, @Date1 --InitialValues for loop'
statement to produce the initial values (As I need a starting date to calculate my first period from), thinking about it i could use it differently, but I dont think It wuld speed things up majorly.

Ideally I'd like to remove the cursor loop all together but I can't see a way to do this.

Good idea on the indexes, that should speed things up a bit. If I can get it to run in under 1hr I'll be happy.

 

[vongrunt]

You said 24 hours ATM... how many rows in both tables?

Also: is it possible that two or more date intervals from incoming data overlap (intersect) each other?

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[ScrewBalll]

Hi there,
There will be approx 1 million values in either table. So you see with the cursor iterating through them it takes a long time.
Also this example is much simpler than the actual problem as this is an example for 1 product. There will be lots and lots of product prices coming through into the system and they have to be looked up against their own price.

The data intervals can overlap one another, which my code does take into consideration. Updating the prices to the new data.