fast inserts on MSSQL without using Bulk Insert and textfiles

Atle Smelv?r

Delphi Developer

Thu, 22 Sep 2005 07:50:35 GMT

fast inserts on MSSQL without using Bulk Insert and textfiles

Is there a way to make MSSQL faster when executing multiple inserts
(100000+). It's very slow, and I really want an alternative to writing a
file and do a bulk insert.

I want some commands to lock table and disable indexes while doing the
inserts. Then enable the indexes after I'm done (Not with drop index etc).

bulk start sql stuff. inserts... bulk end sql stuff.

Can anyone help me?

thanx

-Atle

Viatcheslav V. Vassilie

Delphi Developer

Thu, 22 Sep 2005 17:44:38 GMT

Re:fast inserts on MSSQL without using Bulk Insert and textfiles

You can use bulk-copy rowsets that are available in MS SQL OLEDB provider
but can not be accessed from ADO. Try OLEDB Direct from
http://www.oledbdirect.com. Look $(OLEDB Direct)\Examples\SQL Server\1 000
000 rows for a sample that inserts 1 000 000 rows into MS SQL Server table -
this takes less than 1 minute on most computers and up to 10 sec. on modern
computers. OLEDB Direct is not so easy as ADO but is much faster - it works
with OLEDB provider without ADO layer.

----------------------------------------------------------------
Regards,
Viatcheslav V. Vassiliev
http://www.oledbdirect.com
The fastest way to access MS SQL Server, MS Jet (MS Access)
and Interbase (through OLEDB) databases.

"Atle Smelv?r" <a...@datagrafikk.no> ???Y/???Y ????? ???Y??:
news:3e8f6c96$1@newsgroups.borland.com...

Quote

> Is there a way to make MSSQL faster when executing multiple inserts
> (100000+). It's very slow, and I really want an alternative to writing a
> file and do a bulk insert.

> I want some commands to lock table and disable indexes while doing the
> inserts. Then enable the indexes after I'm done (Not with drop index etc).

> bulk start sql stuff. inserts... bulk end sql stuff.

> Can anyone help me?

> thanx

> -Atle

Atle Smelv?

Delphi Developer

Fri, 23 Sep 2005 05:58:11 GMT

Re:fast inserts on MSSQL without using Bulk Insert and textfiles

I'm using CoreLab's MSSQL driver with DB Express, so if this is not
available here, I don't want to mix several components with the same
functionality. So I only want the stripped functionality from the MS OLE DB
Service Type Library.

Hope you can give me an answer on that.

I'm gonna test your component's to see how they do against those I use now.
If they do better, I might switch components. Thanx for your info.

-Atle

Viatcheslav V. Vassilie

Delphi Developer

Fri, 23 Sep 2005 14:50:35 GMT

Re:fast inserts on MSSQL without using Bulk Insert and textfiles

dbExpress (any driver) makes updates, inserts, deletes by executing SQL
commands - it is slower than doing inserts with server-side cursor in ADO
and much slower than bulk-copy rowsets (they are not available in both ADO
and dbExpress). You may try to create SQL command with parameters, prepare
it and execute multiple times (once for every row).

"Atle Smelv?r" <a...@datagrafikk.no> ???Y/???Y ????? ???Y??:
news:3e90a3c0@newsgroups.borland.com...

Quote

> I'm using CoreLab's MSSQL driver with DB Express, so if this is not
> available here, I don't want to mix several components with the same
> functionality. So I only want the stripped functionality from the MS OLE
DB
> Service Type Library.

> Hope you can give me an answer on that.

> I'm gonna test your component's to see how they do against those I use
now.
> If they do better, I might switch components. Thanx for your info.

> -Atle

Other Threads

1. Help with fast inserts into a SQL Table without using the DB-Library API

2. Using BULK INSERT on ClientDataset?

3. Fastest way to insert to Access using ADO

4. insert using ado and sp on MSSQL

5. INSERT to MSSQL Server using ADO

6. Error trying to insert record using BDE on MSSQL 6.5

7. Problem inserting records into MSSQL 7 using D4

8. Copying Query Data Into a Table (without using insert - into)

9. bulk insert

10. Access 97 ADO bulk insert