Linking to large textfiles - can I speed up searches?

  • Thread starter Thread starter Jesper F
  • Start date Start date
J

Jesper F

I'm creating links to large textfiles (100-200mb) in my database and using
the linked files as "tables".
I'm doing this to avoid importing the large files and increasing risk of
corruption.
However, searches are very slow in this linked datasource, which is expected
since there are no indexes on such a file. But is there any way, that I
speed up the searches in these files/tables?
I'm searching on single columns that the user chooses but it's still pretty
slow.
Can I create my own index somewhere in any way that can help with the
search?
Thanks for any ideas.

Jesper
 
Hi Jesper,

In order to speed things up dramatically you need to index the tables
and use the indexes to find stuff. In principle you could build your own
index, but you'd then have to write your own text file drivers in order
for Accessto use the index data (or else abandon SQL and do it all in
VBA).

It would be *far simpler* to import the data into "proper" database
tables (e.g. in another mdb file where you could use linked tables to
access them).

Otherwise: store the text files on a local drive so you're not accessing
them across the network (or how about copying them to a RAM drive as you
load the program?); remove any fields and records you don't actually
need.
 
In order to speed things up dramatically you need to index the tables
and use the indexes to find stuff. In principle you could build your own
index, but you'd then have to write your own text file drivers in order
for Accessto use the index data (or else abandon SQL and do it all in
VBA).
Click to expand...

Is it really possible to use my own index when it's with a link to a text
file?
How could I go about writing my own index? I know VBA well.
It would be *far simpler* to import the data into "proper" database
tables (e.g. in another mdb file where you could use linked tables to
access them).
Click to expand...

Good point. Originally I just figured that when continously processing text
files of up to 200mb I'd rather avoid importing them. I don't need to modify
them, just find records in them, but I guess using a different mdb could be
a solution.
how about copying them to a RAM drive as you load the program?)
Click to expand...

This sounds very interesting. Is it possible to create a RAM drive with VBA
fx. when loading the database?


Thanks for all your help.

Jesper
 
Is it really possible to use my own index when it's with a link to a text
file?
How could I go about writing my own index? I know VBA well.
Click to expand...

There are many ways of doing it, all of them well outside the scope of
this newsgroup. I suspect that it wouldn't be worth doing in VBA,
because of the time you'd lose by having to process everything
explicitly, sequentially, in code rather than just writing a query and
handing it over to the database engine. Hence my suggestion of a custom
ISAM or ODBC driver, which is way outside my expertise and certainly
can't be done in VBA.
Good point. Originally I just figured that when continously processing text
files of up to 200mb I'd rather avoid importing them. I don't need to modify
them, just find records in them, but I guess using a different mdb could be
a solution.


This sounds very interesting. Is it possible to create a RAM drive with VBA
fx. when loading the database?
Click to expand...

You'll have to find out for yourself.

One thing I didn't mention before: have you "optimised" the text files,
e.g. by filtering out all the fields and records you don't actually
need? For that matter, have you normalised them? Most big text files
I've encountered contain lots of redundant or superfuous data. (I use
Perl and/or the Gnu text utilities - Windows versions downloadable from
http://unxutils.sourceforge.net - for the filtering, but it can be done
with VBS or VBA).


Thanks for all your help.

Jesper
Click to expand...
 
Back
Top