Hello, I have a question on a performance analysis I just did and I don't
understand.
I have a query on a table with many records and the performance depends on
something, I cannot understand.
The table has an integer field and a timestamp field. Now I want to select the
first record, with a specific value in the integer field ordered by the
timestamp field.
There are indices on both fields and a combined index of both fields.
When I do it that way:
select first 1 timestampfield
from mytable
where integerfield = 1
order by timestampfield
it is much slower (up to 7 times slower, depending on how many records I
filled into the table) than this one
select first 1 timestampfield
from mytable
where integerfield = 1
order by integerfield, timestampfield
even though the result is the same and the performance analyser shows 1
indexed read on both.
Can anyone help me and explain that?
Normally it should be the same. I do not see any sense in doing an ordering on
a field, that is the same in every single record in the result, because it is
filtered on this field.
I created an example where it is reproducable.
Here is the script for creating the table and filling it with records.
set term ^;
create table test
(test_id integer primary key not null,
field1 integer,
field2 timestamp)^
create index test_field1 on test (field1)^
create index test_field2 on test (field2)^
create index test_field12 on test (field1, field2)^
create or alter procedure fill_test as
declare variable i integer;
declare variable n integer;
declare variable x integer;
begin
n = 100000;
x = 0;
while (:x < 10) do
begin
i = 1;
while (:i < :n) do
begin
insert into test (test_id, field1, field2)
values (:x * :n + :i, :x, (select current_timestamp from rdb$database));
i = :i + 1;
end
x = :x + 1;
end
end^
execute procedure fill_test^
commit^
And here is the quick query:
select first 1 field2 from test
where field1 = 5
order by field1, field2
And here is the slow query:
select first 1 field2 from test
where field1 = 5
order by field2
