Query slow

Database
Enthusiast

Query slow

I've a Table with 600 millons of rows (Account, Ammount, DateBegin and DateStart)

I need check what the table rows was in full sequence  ( row n ->DateEnd + 1  =  row n+1 ->DateStart )

By example.

Acc1  100.00  2012-01-01 2012-01-05

Acc2  200.00  2012-01-06 2012-01-08

My query is :

SELECT T1.Account

FROM MyDB.MyTable T1

LEFT OUTER JOIN MyDB.MyTable T2

        ON   T1.Account       = T2.Account

        AND T1.DateEnd +1 = T2.DateStart

WHERE T1.DateEnd <> 2001231

AND T2.Account IS NULL

It run very slow. Any idea for a best performance ?

3 REPLIES
Junior Contributor

Re: Query slow

You want to return the rows with gaps?

SELECT Account, DateStart, DateEnd,
min(DateStart)
over (partition by Account
order by DateStart
rows between 1 following and following) - DateEnd as Gap
FROM MyDB.MyTable
qualify Gap > 1

Dieter

Enthusiast

Re: Query slow

Hi Dieter,

In TD 13.0 i need add Unbounded in line 5

rows between 1 following and unbounded following) - DateEnd as Gap

The runtime was 20 minutes instead of 6 hours !!!

Thaks a lot.

Junior Contributor

Re: Query slow

Ops, it was supposed to be "1 following", but "unbounded will work, too (although it might be less efficient).

Dieter