Re: [firebird-support] Question about few Firebird details
Hi, thanks you and others for correction now “44” i am near to understand. 45. i must test – but how look sort for your sample? ORDER BY T1.iSort and difference is because of existence of big varchar - BIGSTARTING ? If yes then something must be wrong – this sould only sort pointers and inrelevant data should not affect speed. But i do not know how this work internally – did you compare this with others RDBMS? regards, Karol Bieniaszewski From: mailto:firebird-support@yahoogroups.com Sent: Monday, May 23, 2016 9:32 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Question about few Firebird details 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_KEYwhy this can be faster then? SELECT FIELD_KEY, FIELD1, FIELD2, ... FIELD_N FROM T ORDER BY FIELD2Firebird 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
Re: [firebird-support] Question about few Firebird details
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
Re: [firebird-support] Question about few Firebird details
23.05.2016 21:05, liviuslivius liviusliv...@poczta.onet.pl [firebird-support] wrote: > 44. Use NO_AUTO_UNDO - what happend when i do rollback? You will get hard rollback, frozen OAT counter and unavoidable run of sweep. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Question about few Firebird details
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
