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

--

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
08-514 905 90