Subject Re: [firebird-support] Bulk upload options
Author hamacker
Post date 2018-05-18T16:32:37Z
Well, I wrote that scripts by isql.exe < script.sql is more fast than transfer from machine to another because... I did and compare. 
Is more fast because dont depends of any kind of application that use data layers(ado, odbc,...) and network protocols, it´s more similar that 'bulk insert' in another RDBMSs, FB does not have this insert method or  'truncate table'.
But scripts is not easy way, huge files is complex to support.
Easy method that I choose requires backup from database production to local SSD drive and recreate .fdb 
After this, transfer from local database-backup to local destiny using parametrizes queries.
It´s fast, but you need to commit after several inserts because after some times the transaction will be slow down.

[]´s

Em qua, 16 de mai de 2018 às 20:13, 'Carlos H. Cantu' listas@... [firebird-support] <firebird-support@yahoogroups.com> escreveu:
 

Script is just a bunch of SQL statements executed in sequence. An
insert script will never be faster than using prepared parametrized
statements, since with scripts, Firebird will have to prepare/execute
statements one by one.

For specific cases, user may try to put the "inserts" inside an
execute block, respecting the limits, so the whole block will be
prepared at once.

[]s
Carlos
Migration Guide to Firebird 3 - http://www.firebirdnews.org/migration-guide-to-firebird-3/

Carlos
www.firebirdnews.org - www.FireBase.com.br

hsgcfs> I dont know bulk method in firebird.
hsgcfs> You can write scripts, scripts is more fastest than transfer
hsgcfs> from machine to another. You can run isql.exe < script.sql
hsgcfs> If is not possible to create scripts then use (hard) commit
hsgcfs> each 1000 (or more) transfers, if you not to do, transaction
hsgcfs> will be slowing at after 'n' transfers.

hsgcfs> Em ter, 15 de mai de 2018 às 23:43,
hsgcfs> fabianch@... [firebird-support]
hsgcfs> <firebird-support@yahoogroups.com> escreveu:

hsgcfs>
hsgcfs>  
hsgcfs>
hsgcfs>
hsgcfs>

hsgcfs> I am looking for the fastest way to run a bulk insert.. 

hsgcfs> I believe one way would be using isql.exe , I need to plan a
hsgcfs> solution to upload into a single table around 500 million
hsgcfs> records. Does anyone have experience with bulk updates via
hsgcfs> isql? Would it be X times faster than using a normal
hsgcfs> application inserting the records inside a begin transaction and commit at the end?

hsgcfs> The issue is I have a remote database (not a FB database) and
hsgcfs> my options are reading the remote DB and writing into FB one
hsgcfs> record at the time with a loop, or asking the remote DB to
hsgcfs> dump the table into a txt file, then compressing the file with
hsgcfs> winrar or whatever compression, and then using that file to
hsgcfs> execute a bulk upload to FB," This is an ongoing issue, is not
hsgcfs> a one of, so I am trying to design the best performing solution, not a one of trick.

hsgcfs> I know the volume of data will be around 250 Gb per run,
hsgcfs> hence that could be compressed at the server non-FB and sent
hsgcfs> to the FB server via internet, the compressed size would be
hsgcfs> probably just 5% of the uncompressed size as it is all text,
hsgcfs> so we are talking about 12 Gb aprox, the only question is
hsgcfs> "would then uploading the txt file into a table  be quicker
hsgcfs> than a line by line insert into table?

hsgcfs>
hsgcfs>
hsgcfs>
hsgcfs>
hsgcfs>
hsgcfs>

hsgcfs> Posted by: hamacker <sirhamacker@...>