| Subject | Re: [firebird-support] Importing via external table |
|---|---|
| Author | Dan Wilson |
| Post date | 2004-05-26T16:29:37Z |
On 5/26/2004 at 4:33 PM Milan Babuskov wrote:
Thanks for the information. However, my full application is a bit more complicated than the small piece with which I am experiencing a problem. I have a program which is receiving a full data feed from the US stock and equity options markets. It is analyzing that data, displaying information on the screen for the user, and also saving a portion of the information to a disk file, which is cycled hourly (the file is closed and a new file is created). This disk file receives between 4 and 12 GB per hour during market hours. Another program takes this hourly data, massages it, and prepares a subset of that data for permanent storage within a Firebird database. This amounts to several million row insertions across 6 tables over roughly a 7 hour period. (The database currently, after 7 months of operation, is up to 50 GB.) My current code uses IBPP to insert each row, committing after around 10,000 insertions. I am trying the external table/SP route in an attempt to speed up the rate of insertions, as it is currently slower than I'd like to see it. Because the database is in use throughout the trading day, and the data for the current day needs to be available for use as soon as possible after trading ceases for the day, I can't simply turn off all indices during the bulk insert without destroying performance for other applications that make use of this data.
Thanks for your help,
Dan.
>Milan,
> Try my fbexport tool and save yourself a headache. More info here:
>
> http://fbexport.sourceforge.net
>
>
Thanks for the information. However, my full application is a bit more complicated than the small piece with which I am experiencing a problem. I have a program which is receiving a full data feed from the US stock and equity options markets. It is analyzing that data, displaying information on the screen for the user, and also saving a portion of the information to a disk file, which is cycled hourly (the file is closed and a new file is created). This disk file receives between 4 and 12 GB per hour during market hours. Another program takes this hourly data, massages it, and prepares a subset of that data for permanent storage within a Firebird database. This amounts to several million row insertions across 6 tables over roughly a 7 hour period. (The database currently, after 7 months of operation, is up to 50 GB.) My current code uses IBPP to insert each row, committing after around 10,000 insertions. I am trying the external table/SP route in an attempt to speed up the rate of insertions, as it is currently slower than I'd like to see it. Because the database is in use throughout the trading day, and the data for the current day needs to be available for use as soon as possible after trading ceases for the day, I can't simply turn off all indices during the bulk insert without destroying performance for other applications that make use of this data.
>From looking at your site, it did not appear to me that fbexport would be a good fit for this specific bulk insert operation, but I would be greatly interested if you feel I am in error.Incidentally, since posting my first request for help I have discovered that the problem seems to not be with the existence of a second record in my external table, but rather with the contents of that second record. If I modify my code to skip the real first record, and write the second record to the external table as the first record in that table, then I fail on that first record. I'm looking now to try to understand what is unique about this particular record, especially since it works just fine when I insert that row via IBPP without using the external table and SP.
Thanks for your help,
Dan.