Hello all,

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".

asked 03 Dec '11, 08:11

mikron's gravatar image

mikron
913412
accept rate: 0%

edited 01 Jan '12, 11:06

Volker%20Barth's gravatar image

Volker Barth
31.5k318461676

As the OLAP query would be optimal from my point of view - can you tell us why it is not suitable for you requirements?

(06 Dec '11, 16:07) Volker Barth

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.

permanent link

answered 05 Dec '11, 09:22

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104
accept rate: 43%

2

Shouldn't there be a default index, because period_start is the primary key?

(05 Dec '11, 09:49) Martin
Replies hidden
1

Correct, Martin - from my browser I didn't see the PRIMARY KEY clause the first time. While that does create the backing index for the constraint, there are no guarantees that the optimizer is going to use it for this query, given the inequality.

In general, handling inequality join conditions really well is difficult; one should ordinarily try to avoid them. Hence my point about re-thinking the current implementation as used by Mikron.

(05 Dec '11, 15:30) Glenn Paulley

My attempt to use analytic functions work very well in this particular case but not in general case. For example if a window bound can't be defined with constant amount of rows or with a equality condition i.e. if a window bound should be defined with an inequality.

(31 Dec '11, 07:18) mikron
Replies hidden

Can you elaborate more on the restriction you are faced with?

May using EXECUTE IMMEDIATE be a solution w.r.t. to specifying range/rows bounds when the number of rows is only known at run time?

(01 Jan '12, 11:04) Volker Barth

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

permanent link

answered 08 Dec '11, 03:50

AlexeyK77's gravatar image

AlexeyK77
70761224
accept rate: 8%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×248
×16

question asked: 03 Dec '11, 08:11

question was seen: 935 times

last updated: 01 Jan '12, 11:06