Prev: Form Open Event crashes Access 2007 after close/compact
Next: access97, citrix, slow form refresh
From: Bob Barker on 9 Mar 2010 14:30
I am attempting to reduce the number of harddisk writes I make into a
mdb file. Currently, I insert roughly 10,000 rows of data on a daily
basis into this networked mdb-file, but it takes 1 hour to do so which
is now becoming cumbersome to our business processes. I have
identified the time bottleneck is caused by the number of harddisk
writes necessary to get 10,000 rows of data.

Currently, I am inserting 1 row at a time because I am parsing a text
file to extract data from it. I am attempting to change the 1-row-
insert into a MASS INSERT or BULK INSERT into the table to reduce the
number of INSERT queries I run. Something to the the tune of

INSERT INTO tblTest ( Field1, Field 2, Field3, Field4) VALUES
( (1,2,3,4) , (4,3,2,1), (5,5,5,5));

Apparantly, the above statement is NOT supported by MS Access! How
can I do a mass insert? After googling endlessly, I now know MS
Access can do a mass insert using the SELECT keyword such as

INSERT INTO tblTest (Field 1, Field2, Field3, Field4)
SELECT Field1, Field2, Field3, Field4 FROM tblWTF

However, because I am parsing a text file and storing the individual
rows in memory, there is no tblWTF. There is no point in creating
this table either since now I'll be doing the same number of writes to
the database + 1. In this case, I have to individually place the
parsed rows into tblWTF then move it to tblTest using that "INSERT-
SELECT" method. This isn't what I want to do--my objective is to
reduce the # of writes I make to the HD because it's causing lots of
time problems. Any suggestions?
From: Marshall Barton on 9 Mar 2010 15:22
Bob Barker wrote:

>I am attempting to reduce the number of harddisk writes I make into a
>mdb file. Currently, I insert roughly 10,000 rows of data on a daily
>basis into this networked mdb-file, but it takes 1 hour to do so which
>is now becoming cumbersome to our business processes. I have
>identified the time bottleneck is caused by the number of harddisk
>writes necessary to get 10,000 rows of data.
>
>Currently, I am inserting 1 row at a time because I am parsing a text
>file to extract data from it. I am attempting to change the 1-row-
>insert into a MASS INSERT or BULK INSERT into the table to reduce the
>number of INSERT queries I run. Something to the the tune of
>
>INSERT INTO tblTest ( Field1, Field 2, Field3, Field4) VALUES
>( (1,2,3,4) , (4,3,2,1), (5,5,5,5));
>
>Apparantly, the above statement is NOT supported by MS Access! How
>can I do a mass insert? After googling endlessly, I now know MS
>Access can do a mass insert using the SELECT keyword such as
>
>INSERT INTO tblTest (Field 1, Field2, Field3, Field4)
>SELECT Field1, Field2, Field3, Field4 FROM tblWTF
>
>However, because I am parsing a text file and storing the individual
>rows in memory, there is no tblWTF. There is no point in creating
>this table either since now I'll be doing the same number of writes to
>the database + 1. In this case, I have to individually place the
>parsed rows into tblWTF then move it to tblTest using that "INSERT-
>SELECT" method. This isn't what I want to do--my objective is to
>reduce the # of writes I make to the HD because it's causing lots of
>time problems. Any suggestions?


Have you tested using a recordset with the AddNew/Update
methods to add the records. I suspect that it will cache
the records and write them in batches or at least give you
more control of the writes (by closing and reopening the
recordset. Just make sure that you open the recordset using
Where False so you don't waste time retrieving existing
records.

That may also help because it keeps the connection to the
back end open. Using separate append queries one at a time
waste a significant amount of time re-establishing the
connection for each append.

Another thing that may(?) help is to remove all indexes on
the table, append all the records and then recreate the
indexes.

--
Marsh
From: paii, Ron on 9 Mar 2010 15:37

"Marshall Barton" <marshbarton(a)wowway.com> wrote in message
news:gtadp5p4d4d6bm76p51sqmbdq3e6n7gcaj(a)4ax.com...
> Bob Barker wrote:
>
> >I am attempting to reduce the number of harddisk writes I make into a
> >mdb file. Currently, I insert roughly 10,000 rows of data on a daily
> >basis into this networked mdb-file, but it takes 1 hour to do so which
> >is now becoming cumbersome to our business processes. I have
> >identified the time bottleneck is caused by the number of harddisk
> >writes necessary to get 10,000 rows of data.
> >
> >Currently, I am inserting 1 row at a time because I am parsing a text
> >file to extract data from it. I am attempting to change the 1-row-
> >insert into a MASS INSERT or BULK INSERT into the table to reduce the
> >number of INSERT queries I run. Something to the the tune of
> >
> >INSERT INTO tblTest ( Field1, Field 2, Field3, Field4) VALUES
> >( (1,2,3,4) , (4,3,2,1), (5,5,5,5));
> >
> >Apparantly, the above statement is NOT supported by MS Access! How
> >can I do a mass insert? After googling endlessly, I now know MS
> >Access can do a mass insert using the SELECT keyword such as
> >
> >INSERT INTO tblTest (Field 1, Field2, Field3, Field4)
> >SELECT Field1, Field2, Field3, Field4 FROM tblWTF
> >
> >However, because I am parsing a text file and storing the individual
> >rows in memory, there is no tblWTF. There is no point in creating
> >this table either since now I'll be doing the same number of writes to
> >the database + 1. In this case, I have to individually place the
> >parsed rows into tblWTF then move it to tblTest using that "INSERT-
> >SELECT" method. This isn't what I want to do--my objective is to
> >reduce the # of writes I make to the HD because it's causing lots of
> >time problems. Any suggestions?
>
>
> Have you tested using a recordset with the AddNew/Update
> methods to add the records. I suspect that it will cache
> the records and write them in batches or at least give you
> more control of the writes (by closing and reopening the
> recordset. Just make sure that you open the recordset using
> Where False so you don't waste time retrieving existing
> records.
>
> That may also help because it keeps the connection to the
> back end open. Using separate append queries one at a time
> waste a significant amount of time re-establishing the
> connection for each append.
>
> Another thing that may(?) help is to remove all indexes on
> the table, append all the records and then recreate the
> indexes.
>
> --
> Marsh

Doing the inserts within a transaction may also help.


From: Albert D. Kallal on 9 Mar 2010 18:02
I don't think inserting 10,000 rows should take more then about 1 or 2
minutes.

Open a reocrdset....don't use separate sql insert commands. Using inserts
will likely be 100, if not a 1000 times slower then a reocrdset...

So, try a reordset...open the reocrdset once..and use add new....

Also, test your processing loop without actually doing ANY inserts to find
out if you have any bottle necks in the code that processes or prepares the
data before you attempt to insert into the table.

Post back here...the insert process will likely only take a minute or two as
10,000 records is not much at all..

So open a reocrdset....keep it open, and use addnew for each row...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal(a)msn.com


From: Salad on 9 Mar 2010 18:12
Bob Barker wrote:
> I am attempting to reduce the number of harddisk writes I make into a
> mdb file. Currently, I insert roughly 10,000 rows of data on a daily
> basis into this networked mdb-file, but it takes 1 hour to do so which
> is now becoming cumbersome to our business processes. I have
> identified the time bottleneck is caused by the number of harddisk
> writes necessary to get 10,000 rows of data.
>
> Currently, I am inserting 1 row at a time because I am parsing a text
> file to extract data from it. I am attempting to change the 1-row-
> insert into a MASS INSERT or BULK INSERT into the table to reduce the
> number of INSERT queries I run. Something to the the tune of
>
> INSERT INTO tblTest ( Field1, Field 2, Field3, Field4) VALUES
> ( (1,2,3,4) , (4,3,2,1), (5,5,5,5));
>
> Apparantly, the above statement is NOT supported by MS Access! How
> can I do a mass insert? After googling endlessly, I now know MS
> Access can do a mass insert using the SELECT keyword such as
>
> INSERT INTO tblTest (Field 1, Field2, Field3, Field4)
> SELECT Field1, Field2, Field3, Field4 FROM tblWTF
>
> However, because I am parsing a text file and storing the individual
> rows in memory, there is no tblWTF. There is no point in creating
> this table either since now I'll be doing the same number of writes to
> the database + 1. In this case, I have to individually place the
> parsed rows into tblWTF then move it to tblTest using that "INSERT-
> SELECT" method. This isn't what I want to do--my objective is to
> reduce the # of writes I make to the HD because it's causing lots of
> time problems. Any suggestions?

10,000 records is a few records to be sure but taking 1 hour to process?
I suppose you can blame it on harddisk writes. It sounds like a good
enough explanation to give to someone in management.

We'll assume you can't link to text file to the mdb and append from
that. Maybe its a report from a mainframe and you need to parse out the
page breaks etc to get the data first.

Maybe you could
Open "OUTFILE" For Output As #2 ' Open file for output.
Open "INFILE" For Input As #1 ' Open file for output.
and use Input, Input #, or Line Input# to read the data and use Print #,
Write #, Write, or WriteLine to create a the parsed text file. Then
write a SQL statement to append the data from the Outfile text file or
use TransferText to populate the table.

 |  Next  |  Last
Pages: 1 2
Prev: Form Open Event crashes Access 2007 after close/compact
Next: access97, citrix, slow form refresh