Access INSERT performance - other ways? 1

[RustyAfro]

Access 97

I have a text file that cannot be imported using Access built in import wizard (or linked to) so I wrote my own classes to parse the file manually using an importspec and append the data to a new table.

It works but is slow. I am using docmd.runsql and passing an INSERT statement. Access only lets me pass one Insert at a time. Setting the UseTransaction to false doesn't help.

I have also tried currentdb.execute with similar slow performance.

I've tried to figure out a way to use acCmdPasteAppend as it is very fast, but no luck.

I haven't yet tried putting the text data into a recordset and then appending it as I have read that process is even slower...

Does anyone have any progmatic techniques that can insert records reasonably fast into access? If I could duplicate the code behind the acCmdPasteAppend function I would be ok I think, but I cannot find anything that explains what that is really doing.


Thanks for any insights,

Joel

 

[lameid]

It is true that Jet SQL is faster than a recordset for set processing (doing the same thing to a bunch of records). However, I suspect doing the same thing iteratively (like inserting) would be faster using a recordset because a good part of the connection is already open (obviously don't close the recordset after each insert).

I could be wrong about this... If you try it let me know as I am curious.

On the other hand, Access is very good at processing Import Specs and doing Inserts in bulk. That said, it may be more efficient to fix the data for import and then import it.


A common problem I have run into is text files that separate rows with just a Line Feed and not a Carriage Return AND Line Feed. I have a function (from an MS KB I think) that reads the file in Access, writes it back out (fast) and then it is imported. Fixing the file and then importing it is likely the best option. If you tell me more about the scenario I may be able to help more.

 

[RustyAfro]

Lameid,

Thank you for the quick reply. Given your response, I think you'll feel my pain.

Text file is 250mb with over 265 columns of data (I know - insane) - but that's what was sent to me. Opening it in notepad makes my PC run out of memory. Access has a 255 column limit which doesn't help things. In the importspec, I just unchecked enough columns to get it under 255.

Each row is >2mg due to a lot of white space. Access has a 2mg limit per row. During each row parse, I kill the whitespace.

Almost every other row is completely blank (literally an extra carriage retrun).

I am using the "Binary Access Read" method to read in the file - and that actually performs reasonably fast without overloading my memory.

The code I wrote to parse the data based on the import spec is fast as well - when I run the app without appending it, it's quick. But when I turn on the append (runsql) in the loop, is when it goes slow, about 10 records a second.

Anywho, this is the scenario. I've put enough time into this little app to sell the thing - hah. I might as well try the recordset/append method as you might be right about JET and not closing the connection. Reexporting to text after my cleanup might work too, but I've written some nice code into this thing that only scrubs a row if an error is trapped, and then attempts to append it again, flagging custom column to true to indicate it was scrubbed. If that fails, it appends the raw row into an error table with the true row number. I kinda like it so may end up dealing with the slowness.

If you have any other advice though I'm all ears ;-)

 

[lameid]

The only thought I have is to break up the data into 2 files (read a line and write a line to file a and file b). Grab the primary key (or increment an integer if there is not one) and make it the first column for every record in each written file. It will be easy to join the information once the tables are imported. That said, now might be the time to consider if you want to be able to pick and choose which fields go in which file for your commercial product. If just for you, I would just cludge it. I am thinking put similar information in each file... I.E. all addresses in one file (table) and credit information in the other. Obviously, your data is more comlicated than that but you get the idea. If you leave out the Primary Key... your name will live in infamy

 

[RustyAfro]

Lol, infamy....good idea on splitting the file based on PK. Luckily enough columns were completely blank to eliminate them under the 255. If I had to keep em though, the split file would be good.

I'm at 40,000 of 150,000 rows, so it'll be a bit longer till I can try boosting the performance more.

Thanks for your thoughts I

 

[RustyAfro]

Just an update - I tried using .addnew with an open recordset to the table - it was a bit slower after all, about 8 records a second.

I found this link:

http://support.microsoft.com/kb/126131#appliesto

It seems to be what I'm looking for, but it's beyond me how to do it within the VBA environment (probably not possible). I tried linking to the same access database via ODBC but received an error that ODBC access table linking is not allowed.

If you have any tips I'm still searching for a better way ;-)

 

[lameid]

Luckily enough columns were completely blank to eliminate them under the 255.

I thought you had it to the point where you could write a file with fewer than 255 columns so that you should be able to import directly into Access... Or are you not writing to the file for some error handling? If so, you could write the errors to a separate file for later review.

 

[RustyAfro]

It is true, I could export the scrubbed file and then do a transfertext, but again, I like the functionality I have.

I do have another update though, I was able to get it to about 100 records a second by just doing some cleanup in my code (getting rid of redundant code and not calling functions repeatedly by storing them locally helped quite a bit).

After searching high and low however, I did find ways I could have done this OUTSIDE of access VBA. If I ever rewrite it in VB, I could create a DAO or ADO connection to the database, create a VB recordset, .addnew, and every thousand records or so do a .update to the table I'm connected to in Access. I've read that is pretty fast as it's basically batch updating.

Also, when connecting to the access tables outside of Access VBA, a lot of optimizing can be done with the connection to handle multiple inserts.

This thread had a couple of good techniques:

http://groups.google.com/group/comp...+insert+addnew&rnum=13&hl=en#9a2f55117974b0fa

Hope this helps someone in similar straights ;-)

 

[lameid]

Without reading the thread (as it is quarter till 7 and dinner is waiting)... in DAO you could do a transaction within access and commit it every thousand records or so... It may have a similar effect. If I looked, I could probably remember an equivalent ADO way but several people in this forum have said that DAO code is always faster than ADO. I never did any real testing but I am inclined to believe them.

 

[PHV]

DAO code is always faster than ADO
For a Jet database.

 

[twlewis]

I am using VBScript to insert records into an Access database for a webpage. With ADO, it takes 16.65 seconds to complete. With DAO, it dropped to 9.125 seconds.

 

[MichaelRed]

if the need is apeed, do a search in these fora for basGrabFile (thread222-535247) et al. if you are just 'happy' with whatever forget it.



MichaelRed

 

[RustyAfro]

That link to the thread was useful, thank you