How long does it take to load 800,000 text rows

[JaxtheDog]

I am new to Access although I have used Oracle and Sybase. I need to use it to import a text file containing a list of IDs with some other descriptive information. The text file will have about 800,000 rows. The import will be to a shared network drive tuned for database performance. Can anyone give me a feel for how long Access takes to load this type of text data. And does it take longer if there is a primary key/index? If importing with an index slows things down, can the index be built later?

Thanks, I apprecite any help anyone can give me
Jax

 

[lameid]

Because you are running over the network, the biggest limiting factor is how fast is the network? This would probably go a lot faster at a 1Gps link vs say 100 mbps.

Also, the size of each record matters, as well as whether the file is on the local machine or the network. My gut is telling me it will go much faster if the text file is on the client (local) machine. But at full duplex, it might not mak much of a difference.

The key thing to note is that Access is a File Server and not a database server. This means that the datbase engine (the client) has to manage all the data in the file (across the network). This is why Access slows down over a network.

Because you have experience with Oracle, I would suggest you build your index with the Jet SQL Create Index statement. An alternative would be to add an index to the indexes collection in VBA using DAO. I am sure there is an ADO solution, but I rarely have to do such a thing and don't know what to do. However, I don't think it makes any difference to perfomance if it is created first. I am pretty sure that a Jet Index is more of an array of pointers than physical data sort. It would probably resort the data by the primary key if you were to compact the database after you were done.

Unfortunately, the best advice I can give you to see how long it is going to take is to test it. I can't imagine it taking more than 30 minutes and probably at least a couple.

 

[MichaelRed]

there are some threads here (in Tek-Tips) which illustrate a MUCH more efficient way of processing text files. Importing the raw text file will (as already noted) be somewhat (painfully?) Soooooooooooooooooooolw - particularly over a network. search these (Tek-Tips) fora for basGrabFile and find one with the sample usage and test code. it can (WILL) speed up loading the text file a lot, and illustrate (in SOME threads) how to then parse the input into an array / table / recordset.




MichaelRed

 

[JaxtheDog]

Thank you for the suggestions. I will try a load and timeit--and I will review the FAQs