CODEWITHSUNDEEP
sqloracle-databasesqlperformance
Luixv
Luixv

Reputation: 8710

SQL Performance when quering for a time interval

I have one table of tickets containing three relevant columns: id, start and finish where start and finish are timestamps.

I have a second table (intervals) with only one relevant column which is time point. time_point is also a timestamp. time_point is always every 15 minutes. That is content of this second table is:

8:00
8:15
8:30
...

The first table (ticket) has 4 millions of records. The second has only 96 records (24 * 4).

I have to select how many tickets are open at any time_point

I wrote the following query: (simplified version)

select * 
from interval, ticket 
where (finish is null or finish > time_point)
      and start < time_point

which works but it is too slow. The problem is that there is no real join between both tables and a I presume that a full table scan is performed for every row.

How can I get better performance here?

Thanks!

EDIT: This is an Oracle DB.

Upvotes: 2

Views: 246

Answers (2)

Ramiro Kollmannsperger
Ramiro Kollmannsperger

Reputation: 104

i believe you dont need to cross join or create an interval table. Instead try following:

> select count(*), tsd from (
>         select 
>         /****************************************************************
>         Now 
>         1- bring your finish column into the format you need: HH24:MI
>         2- truncate its content down to the interval the row belongs to
>         ****************************************************************/
>         to_char(dt,'HH24')|| decode(trunc(to_char(dt,'MI')/15) * 15,0,'00',trunc(to_char(dt,'MI')/15)*15)
> tsd
>         from (
>             select  nvl( finish  ,to_date('31.12.2999', 'dd.mm.yyyy'))   dt      -- 
>               from tickets 
>               /****************************************************************
>               Now Filter out your tickets(before truncate), to find the relevant 
>               tickets for your period use a Parameter date and compare it to the
>               start and end columns nvl( finish  ,to_date('31.12.2999', dd.mm.yyyy'))
>               ****************************************************************/
>               where P_YOUR_PARAM_DATE between start 
>                                       and nvl( finish  ,to_date('31.12.2999', 'dd.mm.yyyy'))
>             ) dat  
>      )  group by tsd order by tsd ;

Upvotes: 1

Tim
Tim

Reputation: 8919

One way to speed this up is to include the finish column in a composite index so there's no need to read from the table to fetch that value:

                create index IX_Tickets on Tickets(start,finish)

P.S. Drop any simple index on Tickets.start as well.

P.P.S. Please clarify: 8:00, 8:15 in your intervals table are not timestamp data type. Did you elminate the date element in your question for the sake of simplicity?

Upvotes: 0

Related Questions