Link or Import cvs files - What about the speed ?

N

Nicodemus

Hello all,

I've got 2 large csv files (Around 1 million records, 50 fields) which will
be joined in the queries I will create later on.

What would be the best approach in terms of query speed processing, Linking
or Importing them ?

Thanks for any advice,
Nicodemus
 
J

Jeff Boyce

That could depend on network and other factors. Have you experimented and
tried both ways?

Note that Access queries can take advantage of indexing. Since your raw CSV
files won't have indexing, importing and indexing could result in faster
queries... but risk the possibility that the data copied into (i.e.,
imported) Access is out of sync with the raw CSV file data.

There's a risk either way, that the CSV data is not particularly
well-normalized. Access is a relational database, and its features and
functions work best on data that is well-normalized.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
N

Nicodemus

Hi Jeff,

thanks for your fast response.
I did link the files, and though the queries respond quite fast (some
seconds), I will now try importing the files.
I wish though to import each file in its own DB, and then build my queries
in a third DB. Do you think it may slow down the process if I proceed that
way ?

Nicodemus
 
J

Jeff Boyce

Why would you use a separate DB for each import? Remember, we aren't there,
we can't see your data, we don't know your situation as you do.

A common design in Access is a "split" database -- the data (tables) all
live in a "back-end" (tables only), while everything else (forms, queries,
reports, ...) all live in a "front-end", and use links to the back-end
tables.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
N

Nicodemus

Surely I could import both files in 1 dB, but as I stated in my first post,
they are quite large, and I'm afraid to overstep Access size limit, which I
guess is 2gb.
Both files together are close to this size ! This explains why I want to
import them in a separate DB.
 
J

Jeff Boyce

Yes it does.

You could import each into a separate "back-end", then link to both from a
single "front-end".

Note... Access works better with normalized data. A "straight" import is
not necessarily likely to be well-normalized.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
N

Nicodemus

"...a separate 'back-end', then link to both from a single 'front-end'..."
Yes, that was in my initial question : will this affect the processing speed ?

What do you mean with "normalized data" ?
 
N

Nicodemus

"...a separate 'back-end', then link to both from a single 'front-end'..."
Yes, that was in my initial question : will this affect the processing speed ?

What do you mean with "normalized data" ?
 
J

Jeff Boyce

Access, like any other tool, works better if you understand how to use it.
"Normalization" is a process by which data is organized by applying a series
of rules. Check Access HELP (and search on-line) for more information about
normalizing.

As to performance, it would really depend on what you are comparing it to.
Can you test your alternate approaches?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
Top