lionel napoleon lionelnapol...@yahoo.com [firebird-support] skrev:
I use firebird 2.5.6.I just wanted to know if there is a method by
which i can speed
an insert query to (insert about 50000 records) into a table.Right now
it is taking about
40 minutes.My front end is VBscript and i use a prepared statement.Any
help would be
appreciated.
Hi,
In addition to Helen's advice, if it's still slow, I recommend using an
external table. Write the data to a fixed-width format text file, create
an external table referencing that file, then insert the records using a
insert into....select from statement.
I've found this approach beats any client insert iteration approach by
several orders of magnitude.
For example, if you need to insert records with a string, a number and a
date, create a text file like this (preferable with a fixed-width
character encoding like iso-8859-1 or win1252:
This is some string with trailing space padding \t0044838\t2016-05-25\r\n
Here's another string \t0000545\t2016-01-31\r\n
Save this file as e.g. C:\Dummy.txt and then create external table:
*create table* "ExDummy"*external file* 'D:\DataDIA\Data\Firebird\Dummy.txt' (
"TheString"*char*(50)*character set* win1252,-- Adjust to your max string
length.
"Tab1"*char*(1)*character set* win1252,
"TheNumber"*char*(7)*character set* win1252,-- Adjust accoring to your
required number of digits.
"Tab2"*char*(1)*character set* win1252,
"TheDate"*char*(10)*character set* win1252,-- Use a date format that
Firebird can cast from easily.
"CRLF"*char*(2)*character set* win1252
);
You can skip the \t separators and even skip line breaks if you wish. In
that case, remove them from the external table create statement accordingly.
Next, insert the data into your actual data table:
insert into "MyTable" ("S", "N", "D")
select trim(cast("TheString" as varchar(50))),
cast("TheNumber" as int),
cast("TheDate" as date)
from "ExDummy";
You can then drop the external table, or keep it permanently for
repeated batch imports. (Note: dropping tables should be done with
exclusive database access to avoid risk of corruption).
It is possible to create external file in Firebird-native format rather
than text format, but that's so complicated that it's probably not worth
the effort.
This method will normally import several thousand records per second,
but speed obviously depends on factors that Helen mentioned, e.g.
indexes, index tree depth, page size, ...
Regards,
Kjell
--
Marknadsinformation logotyp
Kjell Rilbe
Telefon: 08-761 06 55
Mobil: 0733-44 24 64
Marknadsinformation i Sverige AB
Ulvsundavägen 106C
168 67 Bromma
www.marknadsinformation.se <http://www.marknadsinformation.se>
08-514 905 90
Företagskontakt.se <http://xn--fretagskontakt-vpb.se>
Personkontakt.se <http://personkontakt.se>
