[firebird-support] view and computation
Hello, in a view, the following expression is possible : .(a.eingangsdatum+coalesce(c.zahldauer,0)) as FAE,. But how can I take the current date in relation? For example: cast((current_date - (a.eingangsdatum+coalesce(c.zahldauer,0))) as integer) as FIN I would like to calculate when the payment will be due. Thank you. Best regards. Olaf [Non-text portions of this message have been removed]
Re: [firebird-support] Historic tables design
Aldo, I don't know if my approach is a good approach, but it is good enough for me: I keep a separate database for that purpose, to make use of the different access patterns. Like you say, in comparison with the current main database, there is much less access to these tables. And what is left usually is a read-only access as well. Records never get deleted, never get updated, only inserted, and I can't remember even one transaction that needed to be rolled back. I have more information for each record, though (WHEN was it replaced by a new version, WHO replaced it, and WHY has it been replaced) I have different strategies for moving the records from CURRENT to HISTORY. If the overall process does not take too long, I move them at once. If history records must be created because of batch updates, I only tag them, and the moving is done as part of a scheduled housekeeping. Hi, it is common in some business rules that a table has a companion table of historic records. The historic table has the same structure than the active records table, but is less accessed than the active table. On the other hand, the historic table grows steadly, ending up with far more records than the active one ( records deleted from the active table go to the historic one ). My question is the following: should both tables be merged into a single table, with an additional field marking historic records ? Will the queries on this table be slower than having them separated ? I know that an index taking into account that additional field would speed up the queries but, on the other hand, such an index would be a very poorly selective one, because the additional field would only have two values ( 0 and 1 ) like any boolean field. Thanks for any advice. Aldo ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links mit freundlichen Grüßen, André Knappstein EDV und Controlling ~~ beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH Hafenweg 4 59192 Bergkamen-Rünthe Telefon: +49 2389 9240 140 Telefax: +49 2389 9240 150 e-mail: knappst...@beta-eigenheim.de Amtsgericht Hamm Nr. B 420 Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus USt-IDNr.: DE 125215402
Re: [firebird-support] Historic tables design
Aldo Caruso wrote: My question is the following: should both tables be merged into a single table, with an additional field marking historic records ? Will the queries on this table be slower than having them separated ? I have a number of systems running which record 'footfall' so all the new records are purely today and views on the database using today's date provide the 'write' access. We have approaching a million records on some of the sites, and only yesterday a customer commented on just how fast they can run reports even covering a year at a time. Your archive process may be a little more complex, needing to identify records from different days as historic? But I'd still include a date with the historic flag to make that index a little more selective. That said - nowadays I could probably do a UNION on two tables and still be able to provide the historic activity view for a caller with today's activity - didn't have UNION when we started gathering data :) -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk
Re: [firebird-support] Historic tables design
I'm planning to do historical data archive too, and still searching what the best strategies to use. For me, it is best to separate the historical table into it's own table, because the historical table should not have any index. If i put the historical table into separate database, that will make it harder for me to handle two database update that almost happen on every transaction. That what i think of course On Wed, Jul 31, 2013 at 3:43 PM, Lester Caine les...@lsces.co.uk wrote: ** Aldo Caruso wrote: My question is the following: should both tables be merged into a single table, with an additional field marking historic records ? Will the queries on this table be slower than having them separated ? I have a number of systems running which record 'footfall' so all the new records are purely today and views on the database using today's date provide the 'write' access. We have approaching a million records on some of the sites, and only yesterday a customer commented on just how fast they can run reports even covering a year at a time. Your archive process may be a little more complex, needing to identify records from different days as historic? But I'd still include a date with the historic flag to make that index a little more selective. That said - nowadays I could probably do a UNION on two tables and still be able to provide the historic activity view for a caller with today's activity - didn't have UNION when we started gathering data :) -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- Iwan Cahyadi Sugeng Interaktif Cipta Lestari [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ 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: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Historic tables design
Iwan Cahyadi Sugeng wrote: I'm planning to do historical data archive too, and still searching what the best strategies to use. For me, it is best to separate the historical table into it's own table, because the historical table should not have any index. If i put the historical table into separate database, that will make it harder for me to handle two database update that almost happen on every transaction. That what i think of course You NEED an index of some sort on every table even if it is just a generator key, but yes at present Firebird does not support cross database queries, so separate tables need to be in the same database. ( pet hate - if you MUST top post PLEASE switch off quoting altogether! It's even or annoying when your email client does not respect the sig tag and re-quotes the sig as well - I think it's about time I put this as MY sig :) ) -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk
Re: [firebird-support] Historic tables design
On Wed, Jul 31, 2013 at 4:28 PM, Lester Caine les...@lsces.co.uk wrote: ** Iwan Cahyadi Sugeng wrote: I'm planning to do historical data archive too, and still searching what the best strategies to use. For me, it is best to separate the historical table into it's own table, because the historical table should not have any index. If i put the historical table into separate database, that will make it harder for me to handle two database update that almost happen on every transaction. That what i think of course You NEED an index of some sort on every table even if it is just a generator key, but yes at present Firebird does not support cross database queries, so separate tables need to be in the same database. ( pet hate - if you MUST top post PLEASE switch off quoting altogether! It's even or annoying when your email client does not respect the sig tag and re-quotes the sig as well - I think it's about time I put this as MY sig :) ) Sorry about the top post, it's the default in gmail :D I'm thinking to put and a field on every historic table so the table can still have an index -- Iwan Cahyadi Sugeng Interaktif Cipta Lestari [Non-text portions of this message have been removed]
AW: [firebird-support] view and computation
Hello Markus, Thank you, exactly what I'm looking for! Best regards. Olaf ** Hello, in a view, the following expression is possible : .(a.eingangsdatum+coalesce(c.zahldauer,0)) as FAE,. But how can I take the current date in relation? For example: cast((current_date - (a.eingangsdatum+coalesce(c.zahldauer,0))) as integer) as FIN I would like to calculate when the payment will be due. You are trying to subtract a date from another date. Not sure if they like that :) Have a look at DATEDIFF function: http://www.firebirdsql.org/refdocs/langrefupd25-intfunc-datediff.html There's also DATEADD if you want to add some other unit than day to a date: http://www.firebirdsql.org/refdocs/langrefupd21-intfunc-dateadd.html HTH, Markus [Non-text portions of this message have been removed] [Non-text portions of this message have been removed]
[firebird-support] Re: IPv6
--- In firebird-support@yahoogroups.com, Roberto Carlos rc.1990@... wrote: Does Firebird 2.5 work on IPv6? If so, does it need any special setting on firebird.conf? I have asked on devel list if the ipv6 patches were merged already in 2.5.x or 3.0
Re: [firebird-support] Historic tables design
André, Your strategy is interesting. Keeping the historic records in a table in a different database sounds smart, as it helps keeping the main database away from growing indefinitely. Nevertheless, troubles arise if you have to query the historic table, making joins with look up tables wich reside in the main database. Firebird doesn't allow queries between tables belonging to different database, and so you'll be forced to replicate lookup tables in the historic database as well in order to display them accordingly. Keeping the historic table in another database may be a good idea, but it depends on what you have to do with the historic records. It is fine if you only have to keep them for backup or legal concern and don't have to show them in you application joined with current look up tables. Aldo Caruso
Re: [firebird-support] Historic tables design
Lester, your approach is correct provided the fact that in your case you criteria to define a record as historic is only its timestamp ( be it limited to the current day or any other date time criteria ). Unfortunately that is not my case: records contain real estate data and they become historic when properties are sold or the bid is withdrawn, a manual criteria which is reflected by a boolean flag in a field (or by moving the record into another table) Aldo Caruso
Re: [firebird-support] insert from select in psql
Thank you very much for the info, Ann. Greetings. Walter. On Tue, Jul 30, 2013 at 4:06 PM, Ann Harrison aharri...@ibphoenix.comwrote: ** W O sistemas2000profesio...@gmail.com wrote: If the tables have always the same name you don't need the EXECUTE STATEMENT I found a simple solution: EXECUTE STATEMENT 'insert into A select * from B'; The execute statement form works better if the format of the tables is volatile. You avoid the object in use error if the procedure has been compiled. Good luck, Ann [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ 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: http://docs.yahoo.com/info/terms/
[firebird-support] FB 1.5 Query Optimiser Question
Hi, My organisation is currently using FB 1.5 and I wondered if anyone would be explain some unexpected behaviour from the optimizer. I have two tables. TABLE1 contains about 21k records and TABLE2 contains around 2.5m records. There are indexes on the joined fields, and both where clause fields. I've recalculated statistics on each. The query below takes around 10 minutes to return with 500 records. A) SELECT A.F1 FROM TABLE1 A INNER JOIN TABLE2 B ON A.F1=B.F1 WHERE A.CLAUSE1=0 AND B.CLAUSE2=100 (FB Suggested plan: PLAN JOIN (A INDEX (IDX_TABLE1),B INDEX (IDX_TABLE2,PK_TABLE2)):) I've split this up and run the individual parts of the query with the following results. B) SELECT * FROM TABLE1 WHERE CLAUSE1=0 (FB Suggested Plan: PLAN (TABLE1 INDEX (IDX_TABLE1)):) Returns in 1 second with 3800 records. C) SELECT * FROM TABLE2 WHERE CLAUSE2=100 (FB Suggested Plan: PLAN TABLE2 INDEX (IDX_TABLE2)):) Returns in 1 second with 800k records. I've also tried removing parts of the where clause: D) SELECT A.F1 FROM TABLE1 A INNER JOIN TABLE2 B ON A.F1=B.F1 (FB Suggested Plan: PLAN JOIN (A NATURAL,B INDEX (PK_TABLE2));) Returns in 1 second with 21k records. E) SELECT A.F1 FROM TABLE1 A INNER JOIN TABLE2 B ON A.F1=B.F1 WHERE A.CLAUSE1=0 (FB Suggested Plan: PLAN JOIN (A INDEX (IDX_NL_TABLE1),B INDEX (PK_TABLE2));) Returns in 1 second with 500 records. Forcing the initial query to use the suggested plan from query E) (Even though it's not now using the index for CLAUSE2 on the TABLE2 table): F) SELECT A.F1 FROM TABLE1 A INNER JOIN TABLE2 B ON A.F1=B.F1 WHERE A.CLAUSE1=0 AND B.CLAUSE2=100 (Manually forced plan: PLAN JOIN (A INDEX (IDX_NL_TABLE1),B INDEX (PK_TABLE2));) Returns in 1 second with 500 records. Any possible explanation would be very welcome. My guess is that there's so many records (Around a third) on TABLE2 with a CLAUSE2 value of 100 that using the index on this field as part of the select is somehow slowing things down to a crawl. The selectivity of this index is 0.2. However, using the index on the table alone is still relatively fast, hence my confusion. Thanks.
RE: [firebird-support] FB 1.5 Query Optimiser Question
From: firebird-support@yahoogroups.com [mailto:firebird- supp...@yahoogroups.com] On Behalf Of vendee928 Sent: Wednesday, July 31, 2013 6:40 AM To: firebird-support@yahoogroups.com Subject: [firebird-support] FB 1.5 Query Optimiser Question I have two tables. TABLE1 contains about 21k records and TABLE2 contains around 2.5m records. There are indexes on the joined fields, and both where clause fields. I've recalculated statistics on each. The query below takes around 10 minutes to return with 500 records. A) SELECT A.F1 FROM TABLE1 A INNER JOIN TABLE2 B ON A.F1=B.F1 WHERE A.CLAUSE1=0 AND B.CLAUSE2=100 (FB Suggested plan: PLAN JOIN (A INDEX (IDX_TABLE1),B INDEX (IDX_TABLE2,PK_TABLE2)):) What fields make up the indexes? Do you have compound indexes defined for Table1 F1 and Clause1 and Table2 F1 and Clause2? Any possible explanation would be very welcome. My guess is that there's so many records (Around a third) on TABLE2 with a CLAUSE2 value of 100 that using the index on this field as part of the select is somehow slowing things down to a crawl. The selectivity of this index is 0.2. However, using the index on the table alone is still relatively fast, hence my confusion. Even though you have indexes defined, as the PLAN shows the system is having to AND 2 of the indexes together, which is a slow process. The query optimizer in FB v1.5 was not the sharpest knife. Sean
Re: [firebird-support] Historic tables design
Lester, Thanks for your advice. I'll take it into account, because also I think that having two tables is a duplication of work ( not only for moving records between them but also for maintaining DDL changes synchronized ) Aldo Caruso El 31/07/13 15:12, Lester Caine escribió: Aldo Caruso wrote: your approach is correct provided the fact that in your case you criteria to define a record as historic is only its timestamp ( be it limited to the current day or any other date time criteria ). Unfortunately that is not my case: records contain real estate data and they become historic when properties are sold or the bid is withdrawn, a manual criteria which is reflected by a boolean flag in a field (or by moving the record into another table) The point is still valid. An index which combines your 'state' flag with a timestamp provides a filter so you can look for 'all withdrawn bids between ...' for example, and the like. I have caller, telephone, emails, correspondence and the like for each day which can then be filtered in a similar way. Moving records between tables is a little pointless if they form a part of a bigger 'query' such as 'all activity in March' ? -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk [Non-text portions of this message have been removed]
[firebird-support] Re: Historic tables design
--- In firebird-support@yahoogroups.com, Aldo Caruso aldo.caruso@... wrote: Lester, Thanks for your advice. I'll take it into account, because also I think that having two tables is a duplication of work ( not only for moving records between them but also for maintaining DDL changes synchronized ) Aldo Caruso I've been working with both approaches (history in the same table as regular data and in a separate table). Currently in my company we are in process of transition from history in one table approach. It gets ugly.. especially if you need to have an UNIQUE index on some columns. It's also requires from you to remember that in almost each query you want to show only the current data. DDL changes which you are afraid of are not that scary if you have written automated tests. Our tests are checking if everytings is copied correctly to the history data. So for example if we add a column in the main table or modify its size and forgot to mirror those changes in history table, our tests will tell us that right away. Copying is done by the trigger and if you think good on your design you can write one test which will be able to check all your tables and history triggers. Regards.
[firebird-support] Re: Help, tunning database
Today, I faced same problem again, which is Firebird server became not responsive. Here are the latest settings : - win 7 64 bit - Firebirds 2.5.1 super server - page size 8192 - page buffer 8192 - no long transactions - temp cache limit set to 128MB - there are a few numbers errors in insert operations but all already rollback. I suspect that the problem is the noted problem with Windows File Cache on 64bit Windows systems -- the OS is loading too much into file cache which is causing a huge/excessive number of cache page writes, which results in the system grinding to a halt. So, the problem is not with FB but with Windows. Please google/yahoo this subject (it has also been mentioned in this list several times). http://dyemanov.blogspot.fr/2012/03/firebird-vs-windows-file-system-caching.html http://tracker.firebirdsql.org/browse/CORE-3791 Fixed Firebird 2.5.2 Paul
[firebird-support] Error finding .Net provider but only in unit tests
We are using Firebird 2.0.1 in embedded mode and everything works fine in the application but whenever I try to run unit tests against Firebird I get either: System.ArgumentException: Unable to find the requested .Net Framework Data Provider. It may not be installed. at System.Data.Common.DbProviderFactories.GetFactory(String providerInvariantName) or System.Configuration.ConfigurationErrorsException: Failed to find or load the registered .Net Framework Data Provider. at System.Data.Common.DbProviderFactories.GetFactory(DataRow providerRow) The former if have an entry in machine.config for FirebirdSql.Data.FirebirdClient and the latter if I remove it. I originally posted about this issue back in September 2009 and because of other priorities put it to bed for a while. http://tech.groups.yahoo.com/group/firebird-support/message/104705 I have since took it up again and have spent days trying to diagnose this. I have verified that all the required dlls and supporting files for Firebird embedded get copied to the TestResults\Out folder. I had this issue in VS 2010 and am still having the issue after migrating my projects to VS 2012 and .Net 4.5. I tried upgrading Firebird to 2.5.2 and the Firebird Client to 3.0.2 and had the exact same results. Thanks in advance for any help you can offer on this. Regards, Greg B.
Re: [firebird-support] Unable to complete network request to host 192.168.3.25
This looks like a network issue to me, perhaps a failing NIC at one end or the other On 26 July 2013 10:26, Michael Vilhelmsen michael.vilhelm...@microcom.dkwrote: ** Hi We have a setup with two terminal servers and 1 database server running Firebird 2.1.5.18497 (both server and klient). This has been up and running for years. Just a few days ago, this error starts coming on variuos machines, and I was woundering what you would look after, check and whatever to find the cause. The error is: Unable to complete network request to host 192.168.3.25 Error reading data from the connection. Last nigth I updated firebird from 2.1.4 to current version and I made a backup restore cycle. This morning the error was present again. Mivi -- Nick Upson, Telensa Ltd [Non-text portions of this message have been removed]
Re: [firebird-support] Error finding .Net provider but only in unit tests
At 06:48 a.m. 1/08/2013, GregB wrote: [... describes .NET Provider problem...] I originally posted about this issue back in September 2009 and because of other priorities put it to bed for a while. http://tech.groups.yahoo.com/group/firebird-support/message/104705 Try asking in the correct list for .NET Provider support - firebird-net-provi...@lists.sourceforge.net. You can subscribe directly from this page: http://www.firebirdsql.org/en/mailing-lists/ ^heLen^ (Moderator)
Re: [firebird-support] Re: Historic tables design
On 1 Aug 2013 04:12, un_spoken brucedickin...@wp.pl wrote: --- In firebird-support@yahoogroups.com, Aldo Caruso aldo.caruso@... wrote: Lester, Thanks for your advice. I'll take it into account, because also I think that having two tables is a duplication of work ( not only for moving records between them but also for maintaining DDL changes synchronized ) Aldo Caruso I've been working with both approaches (history in the same table as regular data and in a separate table). Currently in my company we are in process of transition from history in one table approach. It gets ugly.. especially if you need to have an UNIQUE index on some columns. It's also requires from you to remember that in almost each query you want to show only the current data. DDL changes which you are afraid of are not that scary if you have written automated tests. Our tests are checking if everytings is copied correctly to the history data. So for example if we add a column in the main table or modify its size and forgot to mirror those changes in history table, our tests will tell us that right away. Copying is done by the trigger and if you think good on your design you can write one test which will be able to check all your tables and history triggers. Regards. I plan to check the historical table on system start and update the metadata. I'm using n-tier solution, so my server application will do the metadata update [Non-text portions of this message have been removed]
Re: [firebird-support] Re: Historic tables design
Em 31/7/2013 21:38, Iwan Cahyadi Sugeng escreveu: I plan to check the historical table on system start and update the metadata. I'm using n-tier solution, so my server application will do the metadata update I don't know your logic and perhaps this comment doesn't apply to your case, but anyway... I think it's better to check the metadata and stop/report about some misleading table information, it's not recommended to update metadata with the database in use. see you !
Re: [firebird-support] Re: Historic tables design
I think that both approaches have pros and cons and so the decission must be taken depending on the bussiness rules. In favor of the two tables approach may be the case of a UNIQUE constraint limited to current records: There can't be two current records with the same value in a column, but the same value is allowed if one is current and the other is historic or if both are historic. In favor of the all in one table approach may be the case of a UNIQUE constraint encompassing both kind of records: There can't be two records with the same value in a column, regardless if their status (current or historic). Aldo Caruso El 31/07/13 17:56, un_spoken escribió: --- In firebird-support@yahoogroups.com mailto:firebird-support%40yahoogroups.com, Aldo Caruso aldo.caruso@... wrote: Lester, Thanks for your advice. I'll take it into account, because also I think that having two tables is a duplication of work ( not only for moving records between them but also for maintaining DDL changes synchronized ) Aldo Caruso I've been working with both approaches (history in the same table as regular data and in a separate table). Currently in my company we are in process of transition from history in one table approach. It gets ugly.. especially if you need to have an UNIQUE index on some columns. It's also requires from you to remember that in almost each query you want to show only the current data. DDL changes which you are afraid of are not that scary if you have written automated tests. Our tests are checking if everytings is copied correctly to the history data. So for example if we add a column in the main table or modify its size and forgot to mirror those changes in history table, our tests will tell us that right away. Copying is done by the trigger and if you think good on your design you can write one test which will be able to check all your tables and history triggers. Regards. [Non-text portions of this message have been removed]
