Hi Karol,
44. This option turn off individual savepoints. Our tests proof that
gives slight increase in case of massive inserts or updates. Use it
with caution, since the rollback will be forced (through TIP), so if you
insert less than 50k records, there could be difference with and without
NO_AUTO_UNDO - Firebird will be not able to transform rollback into
commit with savepoints. If you insert more than 50k records (actually
50-60k, it depends on memory, not exact ##, if I remember correctly),
rollback will be done through TIP anyway.
45. Try it with table like
CREATE TABLE T1(i1 integer not null primary key, iSort integer,
BIGSTARTING VarchaR(2000));
insert there 10 mln records and do 2 queries as in the example. It will
take 10 mins, and and you'll see the difference by yourself.
Regards,
Alexey Kovyazin
IBSurgeon
Hi,
https://ib-aid.com/en/articles/45-ways-to-speed-up-firebird-database/
i have few questions:
44. Use NO_AUTO_UNDO - what happend when i do rollback? I do not
understand what really do this option..
30. Use derived tables to optimize SELECT with ORDER BY/GROUP BY -
SELECT T.FIELD_KEY, T.FIELD1, T.FIELD2, ... T.FIELD_N
FROM (SELECT FIELD_KEY FROM T ORDER BY FIELD2) T2
JOIN T ON T.FIELD_KEY = T2.FIELD_KEY
why this can be faster then?
SELECT FIELD_KEY, FIELD1, FIELD2, ... FIELD_N
FROM T
ORDER BY FIELD2
Firebird sort whole resultset of "sorted key - Field2" or do different
work?
I see in FB3 plan sort width and it looks like not true but?
regards,
Karol Bieniaszewski