Anders
Wed Dec 05 10:50:27 PST 2007
That's 300 times better. Calculating the duration in a second subquery is of
course not necessary, it can be added with
Update durations Set duration = end - start
which cuts some 65% off the time; still way slow though.
It makes a big difference if you change the data distribution:
FOR i=1 TO 1000
FOR y = 1 TO 30
creates a table where the first type of query runs 20 times faster. 10000*3
and the timings are equal.
By the way, SQL Server has got a function the returns a rownum in a query
result; it can be use like RECNO() in VFP.
-Anders
"Leonid" <leonid at grada dot lv> wrote in message
news:OmjodSxNIHA.4688@TK2MSFTNGP06.phx.gbl...
> Hi Anders,
>
> While your solution is correct, it will be slow on sufficiently large
> tables (because of join with >). This may be done much fater.
> Try this
>
> CREATE CURSOR xx (id INT,date DATE)
>
> RAND(-1)
> FOR i=1 TO 100
> WAIT WINDOW ALLTRIM(STR(i)) NOWA
> FOR Y=1 TO 300
> INSERT INTO xx VALUES (i,DATE()-INT(RAND()*10000))
> NEXT
> NEXT
> WAIT CLEAR
>
> SET TALK ON
> m.sc=SECONDS()
> SELECT id, date AS start, NVL((SELECT MIN(x1.date) FROM xx AS x1;
> WHERE x1.id=xx.id AND x1.date>xx.date),DATE()) AS end, ;
> NVL((SELECT MIN(x1.date) FROM xx AS x1;
> WHERE x1.id=xx.id AND x1.date>xx.date),DATE())-xx.date AS duration;
> FROM xx ;
> ORDER BY 1,2 ;
> INTO CURSOR durations
> ?SECONDS()-m.sc
>
> m.sc=SECONDS()
> SELECT x1.id, x1.date AS start, NVL(x2.date,DATE()) AS end, ;
> NVL(x2.date,DATE())-x1.date AS duration ;
> FROM ;
> (SELECT RECNO() AS rn, * FROM (SELECT id, date FROM xx ORDER BY 1,2) x3)
> x1 ;
> LEFT JOIN ;
> (SELECT RECNO() AS rn, * FROM (SELECT id, date FROM xx ORDER BY 1,2) x4)
> x2 ;
> ON x1.id=x2.id AND x1.rn+1=x2.rn ;
> ORDER BY 1,2 ;
> INTO CURSOR durations2
> ?SECONDS()-m.sc
> SET TALK OFF
>
>
> Leonid
>
>
>
> "Anders Altberg" <anders.altberg> wrote in message
> news:O31tUuZNIHA.1208@TK2MSFTNGP03.phx.gbl...
>>
>> CREATE CURSOR xx (id Int,date Date)
>> INSERT INTO xx VALUES (1,DATE()-20)
>> INSERT INTO xx VALUES (1,DATE()-15)
>> INSERT INTO xx VALUES (1,DATE()-10)
>> INSERT INTO xx VALUES (2,DATE()-21)
>> INSERT INTO xx VALUES (2,DATE()-14)
>> INSERT INTO xx VALUES (2,DATE()-7)
>>
>> SELECT id, date as start, NVL((SELECT MIN(x1.date) FROM xx AS x1;
>> WHERE X1.id=xx.id AND x1.date>xx.date),DATE()) AS end, ;
>> NVL((SELECT MIN(X1.date) FROM xx AS x1;
>> WHERE X1.id=xx.id AND x1.date>xx.date),DATE())-xx.date AS duration;
>> FROM xx ;
>> INTO CURSOR durations
>> BROWSE LAST NOWAIT
>>
>> Result on todays date:
>> Id Start End Duration
>> 1 2007-11-13 2007-11-18 5
>> 1 2007-11-18 2007-11-23 5
>> 1 2007-11-23 2007-12-03 10
>> 2 2007-11-12 2007-11-19 7
>> 2 2007-11-19 2007-11-26 7
>> 2 2007-11-26 2007-12-03 7
>>
>> -Anders
>>
>>
>> "Lew" <lew@fastmail.fm> wrote in message
>> news:eGGyO6UNIHA.4480@TK2MSFTNGP06.phx.gbl...
>>> I've got a semi sql solution to this problem, but can I do it in pure
>>> sql? Here it is:
>>>
>>> Given a table in the form name, date, rate, create a result cursor in
>>> the form name, startdate, enddate, duration (it's crosstab ish). The
>>> idea is for each name, show when the rate went into effect, when it
>>> changed and how long the rate was in effect. The procedural + sql
>>> solution goes like this:
>>>
>>> index the rates table on name + dtos(date)
>>> scan
>>> dStart = Date
>>> cName = Name
>>> select min(date) as nextdate from ratestable where Name = cName and
>>> date
>>> > dStart;
>>> into cursor results
>>> insert into durationtable values (cName, dStart, results.NextDate,
>>> results.NextDate - dStart)
>>> endscan
>>>
>>> This is unbelievably fast with the help of a few add'l tags, but I
>>> wonder if there isn't a single sql statement that'll do the whole thing.
>>>
>>> --
>>> -Lew
>>> The workers took the robot for Maria.
>>>
>>
>>
>
>