I use a temporary table to store a time interval like (start_timestamp, length in second), and becuase some periods can be overlaped, I need to truncate interval lengths. It seems to be very easy, but I don't get an acceptable performance. So, I hope, some have some tip for me.
drop table if exists tt / create local temporary table tt (period_start timestamp not null, period_len numeric(10,0), primary key (period_start)) not transactional / begin declare @vi integer; declare @vt timestamp; delete from tt; set @vi = 20000; while @vi > 0 loop set @vt = dateadd(second, truncnum(365*24*60*60 *rand(), 0), '2010-01-01' ); insert into tt (period_start, period_len) on existing skip values (@vt, truncnum(60*60*rand(), 0)); set @vi = @vi - 1; end loop; insert into tt (period_start, period_len) on existing skip select dateadd(second, truncnum(60*rand(), 0), period_start), period_len - 60 from tt where period_len > 60 order by period_start, period_len; commit; end; / select count(*) from tt / -- bad plan, because s2 not limited to a first row -- select s1.period_start, coalesce(min(s2.period_start), s1.period_start) as next_start from tt s1, tt s2 where s2.period_start > s1.period_start group by s1.period_start / -- that statement is equal to the line above, but is not acceptable in my case -- select period_start, last_value(period_start) over (order by period_start rows between current row and 1 following) as next_start from tt /
This question is marked "community wiki".
Since the query's WHERE clause contains an inequality:
where s2.period_start > s1.period_start
then the server doesn't have many options with which to evaluate the join; it's going to use nested loop join as a last resort. However, you don't define any indexes on the temporary table, so this is unsurprisingly inefficient.
I would take a step back from trying to resolve the performance issues of the query, and instead determine if there is another way to conjure a solution to the larger problem you're trying to solve.
answered 05 Dec '11, 09:22
mikron, try to use cursors. (see my comment at http://www.sql.ru/forum/actualutils.aspx?action=gotomsg&tid=899496&msg=11726107 ) AlexeyK77 aka ggg_old
answered 08 Dec '11, 03:50