Re: faster way to write tables?

On Thu, 21 Feb 2008 02:36:38 -0000, Allen <anugent@xxxxxxxxxxxxxx> wrote:

I need to use this technique in 2 situations: (1) when the data must be constructed in code; (2) when I can't get SQL Server to connect to the source table (I have to use MSDASQL.1 on the input table and SQLOLEDB.1 on the output table).

Ok, I see that you are well beyond beginner level :)

What I've found to be the fastest way of inserting hundreds/thousands of records into a table which might already contain millions of rows, using ADO in Access/SQLserver is to use RecordSets and insert via a temporary table

(Warning: These examples probably aren't complete but should get you started!)

1) Create a new temp table which exactly matches the structure of the target table...

select * into #<temporary> from [<permanent>] where (0=1)

(Inserting into a temp table is a *lot* faster than inserting into the real table - and it is much easier to rollback!)


2) Access the temporary table (to create an empty recordset in your AdoQuery)

select <field1>, <field2>, ... <fieldN>
from #<temporary> as acv where (0=1)


3) Build a variant array of the Field Indexes for each field you are inserting:

var
FieldIndexArray : OleVariant ;

FieldIndexArray := VarArrayOf ( [ fACS_AssetId .Get_FieldIndex,
fACS_CalcId .Get_FieldIndex,
fACS_FrequencyId .Get_FieldIndex,
...

(although this will usually reduce to "VarArrayOf [ 0, 1, 2, 3, ... ]" )


4) For each row, build a variant array of the Values to be inserted...

var
FieldValueArray : OleVariant ;

FieldValueArray := VarArrayOf ( [ aAssetId, aCalcId, aFrequencyId, .... ] ) ;


5) Add each row to the record set

AdoQuery.RecordSet.AddNew ( aFieldIndexArray, aFieldValueArray )


6) When you've come to the end (or after every 1,000 rows or so)...

AdoQuery.RecordSet.UpdateBatch ( adAffectAll )


7) Finally, when you've added everything you want to the temp table...

insert into [<permanent>] select * from #[<temporary>]


(or, if you want to be really clever (although this is probably much *too* clever)...

try
// Try to insert everything
insert into [<permanent>] select * from #<temporary>

except
// Delete all the rows which might cause a conflict
delete from [<permanent>] as perm
where exists
( select 1 from #<temporary> as temp
where ( temp.Key1=perm.Key2 and temp.Key2=perm.Key2 and... )

// Insert all the rows which won't cause a conflict
insert into [<permanent>] as perm
select * from #<temporary> as temp
where not exists
(select 1 from <permanent> as perm
where ( temp.Key1=perm.Key2 and temp.Key2=perm.Key2 and... )



For inserting millions of rows (into an Access database from SQLserver) I still haven't found anything which performs as well as...
1) Read the rows from SQLserver into an AqoQuery
2) Write the row values (as text) to a line of a CSV/TSV file
3) Create a Schema.ini which matches the table structure
4) insert into [<AccessTable>]
select * from [Text;HDR=Yes;Database=<Path>\;].[<CsvFileName>#<extension>];

According to a random database creation log that I'm looking at, Access can insert 3 million rows in just over 70 seconds (albeit that's into a table which was empty to start with)


HTH
Paul

(P.S. I love the Macc Lads!)

Never heard them myself - I just work here - but I gather they've got an enthusiastic following :)

--
Paul Scott
Information Management Systems
Macclesfield,
.

Relevant Pages

  • Re: Row Order
    ... but that doesn't guarantee they will be located in the ... IDENTITY property, then can I assume that the IDENTITY property will ... Remember this is a TEMP table I'm ... >> I'm inserting rows into a temp table via a SELECT statement that has an ...
    (microsoft.public.sqlserver.programming)
  • Help with a list variable
    ... then looping through the list and inserting the values into ... the temp table, then selecting all the records from teh temp table. ... DECLARE @startingPosition int ...
    (comp.databases.ms-sqlserver)
  • Re: Row Order
    ... Server will insert the rows wherever it can. ... the rows back out of the table, SQL Server can access them in whatever order ... > I'm inserting rows into a temp table via a SELECT statement that has an ... > ORDER BY clause because I need for the rows to exist in the temp table in ...
    (microsoft.public.sqlserver.programming)
  • Re: Help with a list variable
    ... >I have made the following test stored proc that all it does right now ... then looping through the list and inserting the values into ... >the temp table, then selecting all the records from teh temp table. ... Prev by Date: ...
    (comp.databases.ms-sqlserver)
  • Re: c# oledb - multiple inserts to access (mdb) VERY slow - help!
    ... it out with SqlServer instead of Access to do a performance comparison ... Are you inserting straight values or are you ... Run a .NET performance profiler to see exactly ... call ExecuteNonQuery on command obj associated to connection. ...
    (microsoft.public.dotnet.languages.csharp)