Re: What is the fastest way to add a large number of records ?
From: Viatcheslav V. Vassiliev (support_at_oledbdirect.com)
Date: 01/29/04
- Next message: Viatcheslav V. Vassiliev: "Re: Access user count"
- Previous message: Arnie Mauer: "Re: sql statement question"
- In reply to: Andrew Cutforth: "What is the fastest way to add a large number of records ?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Thu, 29 Jan 2004 21:29:51 +0300
Try open recordset with server-side cursor and append rows without SQL - on
both MS SQL Server and MS Access this should be faster. This will not slow
down even on large datasets - server-side cursor does not cache rows. Do not
forget to call DisableControls()\EnableControls() even if you have no
controls attached. For MS Access doing inserts inside transaction could give
you additional speed up.
If you still need speed improvement, on MS SQL Server the fastest inserts
are done with bulk-copy rowsets. This feature is available only in MS SQL
Server OLEDB provider and is not accessible from ADO. With OLEDB Direct
(http://www.oledbdirect.com) you will be able to use this interface and
insert up to 100 000 records per sec.
//------------------------------------------
Regards,
Vassiliev V. V.
http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
ADO.Net
http://www.oledbdirect.com - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)
"Andrew Cutforth" <ajcs@ukgo.com> сообщил/сообщила в новостях следующее:
news:401934b4$1@newsgroups.borland.com...
> When importing data I am adding a large number of records (many thousands)
> into a database via ADO. The database could be Access or MS SQL Server.
>
> Using a TADOQuery there are two methods I know to add a record:
> 1. Send a SQL insert command. I speeded this up by doing: ExecuteOptions
:=
> ExecuteOptions + [eoExecuteNoRecords];
> 2. Open a live empty dataset and append to it. This method I found to be
> the quickest on a small set of data but my guess is that with large data
it
> would slow down and use huge resources due to keeping a large dataset
open.
>
> Does anyone have any advice on the fastest way to do this ? I appritiate
I
> might not be able to do it the same way on Access and SQL Server.
>
> Thanks
>
> --
> Andrew Cutforth
> Author of AJC Directory Synchronizer: Directory Compare & Synchronize
> www.ajcsoft.com
>
>
- Next message: Viatcheslav V. Vassiliev: "Re: Access user count"
- Previous message: Arnie Mauer: "Re: sql statement question"
- In reply to: Andrew Cutforth: "What is the fastest way to add a large number of records ?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
