Prev: Parameter Sniffing
Next: Best Practice Error Checking
From: Fred on 27 Apr 2010 01:43
"Dan Guzman" <guzmanda(a)nospam-online.sbcglobal.net> a �crit dans le
message de groupe de discussion :
66905F97-56D2-4F3C-93E9-BB78198037C5(a)microsoft.com...
>> My solution needs a cursor. Do you think I could post it here so that
>> you can tell me your advice ?
>> (I have to translate and clean it first)
>
> Yes, I think code will help. Set-based processing can be definitely
> be used with temporal data.


Here is a working sample.
Thanks to have a look.


-- The table type
CREATE TYPE [dbo].[PERIODS_SET] AS TABLE(
[period_start] [datetime] NOT NULL DEFAULT '17530101',
[period_end] [datetime] NOT NULL DEFAULT '99991231',
PRIMARY KEY (period_start, period_end)
)
GO
-- The function
CREATE FUNCTION [dbo].[intersect_periods_sets]
(
@periods_1 AS PERIODS_SET READONLY,
@periods_2 AS PERIODS_SET READONLY
)
RETURNS @result TABLE(period_start DATETIME, period_end DATETIME)
AS
BEGIN
DECLARE @current_periods_count AS INT
DECLARE @previous_periods_count AS INT
DECLARE @bound AS DATETIME
DECLARE @period_start AS DATETIME
DECLARE @period_end AS DATETIME
DECLARE @movement as INT
SET @current_periods_count = 0
SET @previous_periods_count = 0

DECLARE curs CURSOR LOCAL STATIC
FOR SELECT period_start AS period_bound, 1 AS movement
FROM @periods_1
UNION ALL
SELECT period_start AS period_bound, 1 AS movement
FROM @periods_2
UNION ALL
SELECT period_end AS period_bound, -1 AS movement
FROM @periods_1
UNION ALL
SELECT period_end AS period_bound, -1 AS movement
FROM @periods_2
ORDER BY period_bound ASC, movement DESC

OPEN curs
FETCH NEXT FROM curs INTO @bound, @movement
WHILE @@FETCH_STATUS = 0
BEGIN
SET @previous_periods_count = @current_periods_count
SET @current_periods_count = @current_periods_count + @movement
IF @previous_periods_count = 1 AND @current_periods_count = 2
SET @period_start = @bound
IF @previous_periods_count = 2 AND @current_periods_count = 1
SET @period_end = @bound
IF @period_start IS NOT NULL AND @period_end IS NOT NULL
BEGIN
INSERT @result(period_start, period_end)
VALUES(@period_start, @period_end)
SET @period_start = NULL
SET @period_end = NULL
END
FETCH NEXT FROM curs INTO @bound, @movement
END
CLOSE curs
DEALLOCATE curs
RETURN
END
GO
-- And a simple test
DECLARE @A AS PERIODS_SET
DECLARE @B AS PERIODS_SET
INSERT @A(period_start, period_end)
VALUES ('20100427', '20100429'),
('20100430', '20100501'),
('20100503', '20100506')

INSERT @B(period_start, period_end)
VALUES ('20100428', '20100502'),
('20100504', '20100505')
SELECT *
FROM dbo.intersect_periods_sets(@A, @B)

--
Fred
foleide(a)free.fr

From: Mark on 27 Apr 2010 04:47
Try this

WITH Starts AS (
SELECT a.period_start
FROM @A a
WHERE EXISTS (SELECT * FROM @B b WHERE a.period_start BETWEEN
b.period_start AND b.period_end)

UNION ALL

SELECT b.period_start
FROM @B b
WHERE EXISTS (SELECT * FROM @A a WHERE b.period_start BETWEEN
a.period_start AND a.period_end)
),
Ends AS (
SELECT a.period_end
FROM @A a
WHERE EXISTS (SELECT * FROM @B b WHERE a.period_end BETWEEN
b.period_start AND b.period_end)

UNION ALL

SELECT b.period_end
FROM @B b
WHERE EXISTS (SELECT * FROM @A a WHERE b.period_end BETWEEN
a.period_start AND a.period_end)
)
SELECT s.period_start,
MIN(e.period_end) AS period_end
FROM Starts s
INNER JOIN Ends e ON e.period_end>=s.period_start
GROUP BY s.period_start;
From: Fred on 27 Apr 2010 08:32


"Mark" <markc600(a)hotmail.com> a �crit dans le message de groupe de
discussion :
19b5cc0a-309c-4004-92f2-4636338eb961(a)q15g2000yqj.googlegroups.com...
> Try this
>
> WITH Starts AS (
> SELECT a.period_start
> FROM @A a
> WHERE EXISTS (SELECT * FROM @B b WHERE a.period_start BETWEEN
> b.period_start AND b.period_end)
>
> UNION ALL
>
> SELECT b.period_start
> FROM @B b
> WHERE EXISTS (SELECT * FROM @A a WHERE b.period_start BETWEEN
> a.period_start AND a.period_end)
> ),
> Ends AS (
> SELECT a.period_end
> FROM @A a
> WHERE EXISTS (SELECT * FROM @B b WHERE a.period_end BETWEEN
> b.period_start AND b.period_end)
>
> UNION ALL
>
> SELECT b.period_end
> FROM @B b
> WHERE EXISTS (SELECT * FROM @A a WHERE b.period_end BETWEEN
> a.period_start AND a.period_end)
> )
> SELECT s.period_start,
> MIN(e.period_end) AS period_end
> FROM Starts s
> INNER JOIN Ends e ON e.period_end>=s.period_start
> GROUP BY s.period_start;

Thanks Mark,

I didn't think to use CTE for this.
It seems I can't use it inside an inline table function (?)
So I used a multi-statement function as before.
I tried your code on production data but it is much slower than the
version with a cursor.
(3600 ms vs 150 ms for more than 2000 periods in each set).
Do you think I can reduce the time with appropriate declaration of the
table type ?
Right now, I have just declared the two columns as primary key so I
guess the joins involving the second columns are not so easy to optimize
for the query analyzer ?

--
Fred
foleide(a)free.fr

From: Mark on 27 Apr 2010 11:23
As I understand it, table variables don't maintain statistics, such
that the optimiser
will always build a query plan assuming the table has one row. As the
number of rows increases,
the query plan can become less optimal. Have you tried this with real
tables rather than table variables?
From: Fred on 27 Apr 2010 12:26


"Mark" <markc600(a)hotmail.com> a �crit dans le message de groupe de
discussion :
b4e05da9-905b-4c14-8b33-b31b9f4e1b98(a)q15g2000yqj.googlegroups.com...
> As I understand it, table variables don't maintain statistics, such
> that the optimiser
> will always build a query plan assuming the table has one row. As the
> number of rows increases,
> the query plan can become less optimal. Have you tried this with real
> tables rather than table variables?

I just tried.
It is slower (10 seconds now) Even if I add an index on period_end
columns (8 seconds).
I don't understand.
I checked again with the table variables and it is still about 3 seconds
without the cursor and less than 0.2 seconds with the cursor.


--
Fred
foleide(a)free.fr

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Parameter Sniffing
Next: Best Practice Error Checking