Re: [firebird-support] What makes a database file size so big?
On 29/03/16 11:44, Małgorzata Barchańska go...@fastdata.com.pl [firebird-support] wrote: > I'm very curious - so what makes our database file so big? Sum of clear > metadata > and raw data is about 2,4GB, and what with other 12GB? Is anything we > can do to shrink our big database file (drop useless indexes or > something like that)? You could try running your database through gstat.exe (in your bin-directory of Filebird, if you installed the development tools with it). You can learn a lot about the way the database-pages are filled per table. You can find a manual for it here: http://www.firebirdsql.org/pdfmanual/Firebird-gstat.pdf Grtz, Rik
Re: [firebird-support] What makes a database file size so big?
Hi, download HQbird Standard trial (http://ib-aid.com/en/hqbird/) and analyze your database statistics with Database Analyst and database file DBInfo. Database Analyst will give you an exact information of tables and indices sizes tab Tables -> sort on Size), their fragmentation due to versioning and blobs. DBinfo will give you information about distribution of pages with numbers and percentage of page types. Regards, Alexey Kovyazin IBSurgeon www.ib-aid.com Hi, I use firebird 2.5 classic server on windows operating system. Because our database file is quite big (18GB) and it starts to cause (because of its size) some maintenance inconvenience we decided to try to shrink it. After checking database statistics we deleted "useless" records in tables that took the most space, we updated old useless blob fields with null values. After backup and restore it turned out that new database is only 3GB smaller than original. We checked the statistics and according to results we have 2,3GB raw data in database file. Restore only metadata creates database of 70MB size, restoring without active indexes results with file 500MB smaller than original. I'm very curious - so what makes our database file so big? Sum of clear metadata and raw data is about 2,4GB, and what with other 12GB? Is anything we can do to shrink our big database file (drop useless indexes or something like that)? Thank you in advance for your answer. Best regards, Małgorzata Barchańska -- Pozdrawiam Małgorzata Barchańska Fast Data Sp. z o.o. Aleja Zwycięstwa 96/98, 81-451 Gdynia Tel.: (58) 732 72 01 http://www.fastdata.com.pl/ NIP : 5862214954,REGON : 220595046 KRS : 304310, Kapitał zakładowy 50 000 zł
Re: [firebird-support] What makes a database file size so big?
What page size do you use? Do you have a lot of blobs? If so what is the average size of those blobs? Mark - Bericht beantwoorden - Van: "Małgorzata Barchańska go...@fastdata.com.pl [firebird-support]" Aan: Onderwerp: [firebird-support] What makes a database file size so big? Datum: di, mrt. 29, 2016 11:44 Hi, I use firebird 2.5 classic server on windows operating system. Because our database file is quite big (18GB) and it starts to cause (because of its size) some maintenance inconvenience we decided to try to shrink it. After checking database statistics we deleted "useless" records in tables that took the most space, we updated old useless blob fields with null values. After backup and restore it turned out that new database is only 3GB smaller than original. We checked the statistics and according to results we have 2,3GB raw data in database file. Restore only metadata creates database of 70MB size, restoring without active indexes results with file 500MB smaller than original. I'm very curious - so what makes our database file so big? Sum of clear metadata and raw data is about 2,4GB, and what with other 12GB? Is anything we can do to shrink our big database file (drop useless indexes or something like that)? Thank you in advance for your answer. Best regards, Małgorzata Barchańska -- Pozdrawiam Małgorzata Barchańska Fast Data Sp. z o.o. Aleja Zwycięstwa 96/98, 81-451 Gdynia Tel.: (58) 732 72 01 http://www.fastdata.com.pl/ NIP : 5862214954,REGON : 220595046 KRS : 304310, Kapitał zakładowy 50 000 zł Posted by: =?UTF-8?Q?Ma=c5=82gorzata_Barcha=c5=84ska?= ++ 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 https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] What makes a database file size so big?
Hi, I use firebird 2.5 classic server on windows operating system. Because our database file is quite big (18GB) and it starts to cause (because of its size) some maintenance inconvenience we decided to try to shrink it. After checking database statistics we deleted "useless" records in tables that took the most space, we updated old useless blob fields with null values. After backup and restore it turned out that new database is only 3GB smaller than original. We checked the statistics and according to results we have 2,3GB raw data in database file. Restore only metadata creates database of 70MB size, restoring without active indexes results with file 500MB smaller than original. I'm very curious - so what makes our database file so big? Sum of clear metadata and raw data is about 2,4GB, and what with other 12GB? Is anything we can do to shrink our big database file (drop useless indexes or something like that)? Thank you in advance for your answer. Best regards, Małgorzata Barchańska -- Pozdrawiam Małgorzata Barchańska Fast Data Sp. z o.o. Aleja Zwycięstwa 96/98, 81-451 Gdynia Tel.: (58) 732 72 01 http://www.fastdata.com.pl/ NIP : 5862214954,REGON : 220595046 KRS : 304310, Kapitał zakładowy 50 000 zł
Re: [firebird-support] How to add previous column value to current value?
>I need something like this > >Name Date Days betwen Previous date >-- >Name1 date10 >Name2 date2date2-date1 >Name3 date3date3-date2 I agree with Thomas in that EXECUTE BLOCK is probably the best solution, but an (inferior) alternative is: SELECT T1.Name, T1.MyDate, coalesce(T1.MyDate - (SELECT MAX(T2.MyDate) FROM MyTable T2 WHERE T1.MyDate > T2.MyDate), 0) FROM MyTable T1 The main reason for me calling it inferior, is that it will be slow for large tables without additional selective WHERE criteria for T2. HTH, Set ++ 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] Listing Table of Database in Order of Dependency
On Mon, Mar 28, 2016 at 11:21 PM, setysvar setys...@gmail.com [firebird-support] wrote: > > > >Hi, > > Hi Rajiv! > > >How can i list tables of a database in order of dependency ie > > > >TABLE_1 (no foreign key references) > >TABLE_2 (foreign key references, if any, in TABLE_1 ) > >TABLE_3 (foreign key references, if any, in TABLE_1 / TABLE_2 ) > >TABLE_4 (foreign key references, if any, in TABLE_1 / TABLE_2 / TABLE_3) > > Ideally, this would be how you designed your database, i.e. have a > forced order of your tables and refuse foreign keys pointing to any > tables further down the list. I'm not certain whether or not things will > want to line up as nicely as you want them in an existing database. > > Nevertheless, I suggest you create a new table: > > CREATE TABLE TABLES_ORDERED > ( > ID INTEGER NOT NULL, > RDB$RELATION_NAME CHAR( 31 ), > CONSTRAINT PK_TABLES_ORDERED PRIMARY KEY ( ID ) > ); > > CREATE GENERATOR TABLES_ORDERED_GEN; > SET TERM ^^ ; > > CREATE TRIGGER TABLES_ORDERED_ID FOR TABLES_ORDERED ACTIVE BEFORE INSERT > POSITION 0 AS > begin > if ( ( new.ID is null ) or ( new.ID = 0 ) ) then > new.ID = gen_id( TABLES_ORDERED_GEN, 1 ); > end > ^^ > SET TERM ; ^^ > > commit; > > fill it with those tables that have no foreign key (start by running > DELETE FROM TABLES_ORDERED if the table is not empty): > > INSERT INTO TABLES_ORDERED(RDB$RELATION_NAME) > SELECT RDB$RELATION_NAME FROM RDB$RELATIONS r > WHERE RDB$SYSTEM_FLAG=0 > AND NOT EXISTS(SELECT * FROM RDB$INDICES i > WHERE r.RDB$RELATION_NAME=i.RDB$RELATION_NAME > AND i.RDB$FOREIGN_KEY IS NOT NULL); > > and then, repeatedly, try: > > INSERT INTO TABLES_ORDERED(RDB$RELATION_NAME) > SELECT RDB$RELATION_NAME FROM RDB$RELATIONS r > WHERE RDB$SYSTEM_FLAG=0 > AND NOT EXISTS(SELECT * FROM TABLES_ORDERED o /*Ignore tables already > inserted*/ > WHERE r.RDB$RELATION_NAME = o.RDB$RELATION_NAME) > AND NOT EXISTS(SELECT * FROM RDB$INDICES i /*Only insert tables whose > foreign key tables are inserted already*/ > JOIN RDB$INDICES i2 ON i.RDB$FOREIGN_KEY = > i2.RDB$INDEX_NAME > AND r.RDB$RELATION_NAME <> > i2.RDB$RELATION_NAME /*Omit this line if you don't want to include > tables pointing to themselves*/ > LEFT JOIN TABLES_ORDERED o ON i2.RDB$RELATION_NAME = > o.RDB$RELATION_NAME > WHERE r.RDB$RELATION_NAME=i.RDB$RELATION_NAME > AND i.RDB$FOREIGN_KEY IS NOT NULL > AND o.ID IS NULL); > > When you've done this enough times for no further table to be inserted, > you can get your ordered list by simply running: > > SELECT RDB$RELATION_NAME > FROM TABLES_ORDERED > ORDER BY ID > > If you find that the list lacks two or more tables, investigate those > remaining tables - maybe you have some circular dependencies? > All Tables got added to TABLES_ORDERED > > Note that I've never tried doing anything similar myself (I don't even > think I've written a query that uses the RDB$FOREIGN_KEY before), and > that there may well be errors in what I've written above. > > There were no errors, Set. Thanks a lot. This helped ! Regards, Rajiv > HTH, > Set >
Re: [firebird-support] How to add previous column value to current value?
With pre V3, PSQL (stored procedure or EXECUTE BLOCK) comes to my mind. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. 'Piotr Olszewski' piotr.olszew...@eskrzynka.pl [firebird-support] schrieb am 29.03.2016 11:53: > let's assume that I have 2.5.3 > > Pozdrawiam > > Piotr Olszewski > > -Original Message- > From: firebird-support@yahoogroups.com > [mailto:firebird-support@yahoogroups.com] > Sent: Tuesday, March 29, 2016 11:45 AM > To: firebird-support@yahoogroups.com > Subject: Re: [firebird-support] How to add previous column value to current > value? > > 29.03.2016 11:40, 'Piotr Olszewski' piotr.olszew...@eskrzynka.pl > [firebird-support] wrote: >> I need to get a date difference (days between) between rows. > > With Firebird 3.0 you can use window function LAG. > > -- > 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 > > > > > > > Posted by: "Piotr Olszewski" > > > ++ > > 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 > > >
RE: [firebird-support] How to add previous column value to current value?
let's assume that I have 2.5.3 Pozdrawiam Piotr Olszewski -Original Message- From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Tuesday, March 29, 2016 11:45 AM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] How to add previous column value to current value? 29.03.2016 11:40, 'Piotr Olszewski' piotr.olszew...@eskrzynka.pl [firebird-support] wrote: > I need to get a date difference (days between) between rows. With Firebird 3.0 you can use window function LAG. -- 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
Re: [firebird-support] How to add previous column value to current value?
29.03.2016 11:40, 'Piotr Olszewski' piotr.olszew...@eskrzynka.pl [firebird-support] wrote: > I need to get a date difference (days between) between rows. With Firebird 3.0 you can use window function LAG. -- 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] How to add previous column value to current value?
Hi I need to get a date difference (days between) between rows. Let say i have: Name Date --- Name1 date1 Name2 date2 Name3 date3 Order by date I need something like this Name Date Days betwen Previous date -- Name1 date10 Name2 date2date2-date1 Name3 date3date3-date2 sql is generated dynamically and it's quite complicated so i can't use procedure to do this. Regards Piotr Olszewski
Re: [firebird-support] Firebird database keeps randomly locking up.
On 24.03.2016 12:36, Richard Cooke rich...@alcoeng.co.uk [firebird-support] wrote: > > > > I have a problem with a Firebird database randomly locking up completely > for a period of time sometimes only a 30 seconds, sometimes 10 or 15 > minutes. I would suspect either a virus scanner or the Windows System Protect "Feature". Which file extension are you using for the datebase? See: http://www.firebirdfaq.org/faq353/ > Version is 2.1.7 > Server is new running Windows Server 2012 R2. > Only a smallish database with about 6 users. > > We never had any problems when running on an old windows 2000 server. > > firebird log file shows various errors, see below: > > > ALCOSERVERThu Mar 24 11:14:17 2016 > INET/inet_error: send errno = 10054 > > > ALCOSERVERThu Mar 24 11:14:17 2016 > INET/inet_error: send errno = 10054 > > > ALCOSERVERThu Mar 24 11:14:17 2016 > INET/inet_error: send errno = 10054 > > > ALCOSERVERThu Mar 24 11:14:17 2016 > SERVER/process_packet: broken port, server exiting > > > ALCOSERVERThu Mar 24 11:14:17 2016 > SERVER/process_packet: broken port, server exiting > > > ALCOSERVERThu Mar 24 11:14:17 2016 > SERVER/process_packet: broken port, server exiting > > > ALCOSERVERThu Mar 24 11:14:17 2016 > INET/inet_error: send errno = 10054 > > > ALCOSERVERThu Mar 24 11:14:17 2016 > SERVER/process_packet: broken port, server exiting > > > ALCOSERVERThu Mar 24 11:14:36 2016 > INET/inet_error: read errno = 10054 > > Hope someone can help. > thanks, > Richard. > > > >