[firebird-support] Speedup big table after delete
Hello, I have a table with 1´000´000 entries. As a cleanup I now delete all except for the most recent ones. Do I need to do anything else to improve performance fo the table? Rebuilding indices is not possible if the primary key is a autogenernarted int, right? Any other suggestions? BR MAtthias
Re: [firebird-support] Speedup big table after delete
30.01.2020 12:48, Matthias Winkler spmm...@gmail.com [firebird-support] wrote: > Do I need to do anything else to improve performance fo the table? Analyse queries' plan and stats. Simple delete is not a solution. -- 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/
Re: [firebird-support] Speedup big table after delete
Have you checked transaction statistics when things are slow? The typical reason for gradual slowdown is that there is one or more transactions that started quite a long time ago and hasn't finished yet. This prevents garbage collection and may make Firebird very slow. It is easily visible by subtracting the value reported for the oldest (active) transaction from the next transaction. If that gap is too large, you probably have one or more programs that doesn't commit their transactions frequently enough, something that can lead to there being many versions of each record being around and a lot of work for the Firebird engine (in Firebird, each record can exist in multiple versions although each transaction only sees one version of the record). Deleting records will not help in such circumstances, restarting Firebird will quickly see a speed increase, but often followed by a new slowdown. The real way to fix this problem is to rewrite the programs accessing Firebird so that they handle transactions properly. If you delete lots of records, some cleanup has to be done. Typically, this is done the next time something accesses the table. You can prevent users from seeing this kind of slowdown by e.g. doing a SELECT COUNT(*) from the table after the deletions (but deletions will not fix situations like the one mentioned in the first paragraph). Another thing that sometimes give the perception of a slow database are queries written in ways that Firebird dislikes (e.g. SELECT ... FROM ... WHERE NOT IN ) or indices being not appropriate for a particular query. 1.000.000 records is not too much, there are Firebird databases containing many times as many records that have no speed issues. The biggest tables I normally work with probably has 20 or 30 million records and some would even consider that a fairly small Firebird table. HTH, Set tor. 30. jan. 2020 kl. 12:48 skrev Matthias Winkler spmm...@gmail.com [firebird-support] : > > > Hello, > > I have a table with 1´000´000 entries. > As a cleanup I now delete all except for the most recent ones. > > Do I need to do anything else to improve performance fo the table? > > Rebuilding indices is not possible if the primary key is a autogenernarted > int, right? > > Any other suggestions? > > BR > > MAtthias > > >
Re: [firebird-support] ODBC driver with SQLLEN equal to 8
27.01.2020 19:23, Daniel Wenzel danielwenz...@hotmail.com [firebird-support] wrote: > So my question is, there's a way of modifying SQLLEN of Firebird ODBC driver > to match > unixODBC configuration? I would suggest to build it from sources in your environment. It looks like distributed binary was built with older ODBC headers and follow older specifications. -- 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/
Re: [firebird-support] ODBC driver with SQLLEN equal to 8
On 2020-01-30 16:02, Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] wrote: > 27.01.2020 19:23, Daniel Wenzel danielwenz...@hotmail.com > [firebird-support] wrote: >> So my question is, there's a way of modifying SQLLEN of Firebird ODBC >> driver to match >> unixODBC configuration? > >I would suggest to build it from sources in your environment. It > looks like distributed > binary was built with older ODBC headers and follow older > specifications. Or possibly with wrong definitions for types. But I think this a question for the firebird-odbc mailing list. Mark
