CODEWITHSUNDEEP
sqlfirebird
Reynaldi
Reynaldi

Reputation: 1165

Firebird performance on where and order by clause

I have a query which basically finds lastcost of certain item on certain date range.

select first 1 lastcost from stock 
where itemid = :itemid and date > :startdate and date < :enddate 
order by date desc

This query takes seconds to complete on millions of records because ">" does not use index. Would it be faster if I split the query by year/month and iterate until it reach startdate (with assumption 1 mil records per month)?

while (endate > startdate) do
begin
  var_year = extract(year from :endate);
  var_month = extract(month from :endate);
  select first 1 lastcost from stock 
  where itemid = :itemid and year=:var_year and month=:var_month
  order by date desc
  enddate = dateadd (-1 month to enddate);
end

I don't have access to Firebird this couple of days, so I haven't been able to try this myself.

Upvotes: 3

Views: 3675

Answers (3)

jachguate
jachguate

Reputation: 17203

Firebird use an index if available for >, <, >=, <= and between operators.

I made a test before posting this with this table:

SQL> show table stock2;
ITEMID                          INTEGER Not Null
STOCKDATE                       TIMESTAMP Not Null
LASTCOST                        DOUBLE PRECISION Nullable

populated it with some data (not millions per month, but enough to test performance)

SQL> select extract(year from stockdate),
CON>        extract(month from stockdate), count(*)
CON>   from stock2
CON>  group by 1, 2
CON>  order by 1, 2;

EXTRACT EXTRACT        COUNT
======= ======= ============
   2012       1       706473
   2012       2       628924
   2012       3       670038
   2012       4       649411
   2012       5       671512
   2012       6       648878
   2012       7       671182
   2012       8       671212
   2012       9       649312
   2012      10       671881
   2012      11       648815
   2012      12       671579

I ran your query, first without any index (takes seconds), then indexing only the itemid column, proving a better plan and better performance, and finally using a index by itemid and date, where the performance is much better. The show plan allows you see the engine uses the index by default.

SQL> set plan on;
SQL>
SQL> select first 1 lastcost
CON>   from stock2
CON>  where itemid = 127
CON>    and stockdate > '2012-01-15'
CON>    and stockdate < '2012-03-27'
CON> order by stockdate desc;

PLAN SORT ((STOCK2 INDEX (IDX_STOCK2IDDATE)))

               LASTCOST
=======================
      149.7170031070709

SQL>

the index definitions I'm using are:

create index idx_stock2id on stock2 (itemid);
create index idx_stock2iddate on stock2 (itemid, stockdate);

Upvotes: 5

Harriv
Harriv

Reputation: 6137

You need probably both ascending and descending indices on date.

Also, you can use "BETWEEN" syntax: 

select first 1 lastcost from stock 
where itemid = :itemid and date between :startdate and :enddate 
order by date desc

Upvotes: 1

Renats Stozkovs
Renats Stozkovs

Reputation: 2605

No, iterations with loops will always be slower than a traditional SELECT statement.

And '<' or '>' will use an index if it could; see if adding 'itemid' to the index helps

Upvotes: 0

Related Questions