Fast Inserts / Batch Inserts

[barbaro]

Is there anyway to speedup inserts?

[Ikar]

If you need to insert great number of records into database the most quickest way is packet insertion of records. Set a query

Code: Select all

SQL.Text := 
  'INSERT ...;'#$D#$A + 
  'INSERT ...;'#$D#$A + 
  ...

On execution of this query it will be passed to the server by a single packet.

Also it would help to increase MSConnection.Options.PacketSize.

[barbaro]

Any measurements of how fast can it be?

If you need to insert great number of records into database the most quickest way is packet insertion of records. Set a query

Code: Select all

SQL.Text := 
  'INSERT ...;'#$D#$A + 
  'INSERT ...;'#$D#$A + 
  ...

On execution of this query it will be passed to the server by a single packet.

Also it would help to increase MSConnection.Options.PacketSize.

[Ikar]

It greatly depends on a lot of parameters. Speed-up can reach tens of times. Speed/Performance will increase with lessening data in the single row.

[barbaro]

Thanks for your replies,
Another related question:
What happens if the sql.text size is larger than the packet size?
How can i calculate the actual size of the data being sent to the server?

[Ikar]

> What happens if the sql.text size is larger than the packet size?

The query will be divided into several packets.

> How can i calculate the actual size of the data being sent to the server?[/

OLE DB and SDAC don't provide this functionality.