Best Searching Method for LARGE File?

[Blake]

Hi,
What I have my program do is compare our databases that are 24 hrs apart.
Between those 24 hours we delete and add new entries to the new database.
Then when we are ready to compare our databases the program will take entry
number 1 in the old database and search for it in the new database if its
found it is deleted from the new database(this keeps our speed at a constant
speed)but if the entry is not found right away the program "freezes" because
it is searching through all 2.6 million entries, it searches until it finds
the entry or until it doesnt find the entry. Is there anway I can optimize
my searching to prevent the "freezing"? Thanks!

[John Knoop]

This is actually SQL, not VB, but try this:

SELECT * FROM table_in_new_db WHERE match_column = 'something' AND your_date
BETWEEN #yesterday# AND #today#

If you replace my words (yesterday, match_column and so on) with your
values, this will limit the search for the past 24h hours, which probably
will prevent your app from "freezing".

"Blake" <Flitcher@aol.com> wrote in message news:3aa2b3f2$1@news.devx.com...
>
> Hi,
> What I have my program do is compare our databases that are 24 hrs apart.
> Between those 24 hours we delete and add new entries to the new database.
> Then when we are ready to compare our databases the program will take
entry
> number 1 in the old database and search for it in the new database if its
> found it is deleted from the new database(this keeps our speed at a
constant
> speed)but if the entry is not found right away the program "freezes"
because
> it is searching through all 2.6 million entries, it searches until it
finds
> the entry or until it doesnt find the entry. Is there anway I can optimize
> my searching to prevent the "freezing"? Thanks!
>

[Joe \Nuke Me Xemu\ Foster]

"Blake" <Flitcher@aol.com> wrote in message <news:3aa2b3f2$1@news.devx.com>...

> Hi,
> What I have my program do is compare our databases that are 24 hrs apart.

What exactly do you mean by "databases"? Are they .MDBs, .DBFs, delimited
text? If they are actual database tables, are they indexed?

> Between those 24 hours we delete and add new entries to the new database.
> Then when we are ready to compare our databases the program will take entry
> number 1 in the old database and search for it in the new database if its
> found it is deleted from the new database(this keeps our speed at a constant
> speed)but if the entry is not found right away the program "freezes" because
> it is searching through all 2.6 million entries, it searches until it finds
> the entry or until it doesnt find the entry. Is there anway I can optimize
> my searching to prevent the "freezing"? Thanks!

2.6 megarecords, eh? You'd better be using a database indexing scheme of
some sort, even if it's just some sort of Berkeley DBish open hashing.

Now let's smash stuff. I'll assume you're using a Jet MDB, since you can
link to anything with an ODBC driver and some things without, and there's
a third-party ODBC driver around (or it used to be) that can talk to OLEDB
data sources. Check out DAO's CreateTableDef method, it rocks.

Air code, YMMV:

dim ws as dao.workspace: set ws = dao.createworkspace("", "admin", "")
dim db as dao.database: set db = ws.opendatabase("new stuff.mdb", true)

' create a link to the old database table
dim oldstuff as dao.tabledef: set oldstuff = db.createtabledef("olddata", _
dbattachedtable or dbattachexclusive, "the mother lode", _
";DATABASE=\\monsolympus\library\monster.mdb")
db.tabledefs.append oldstuff
db.tabledefs.refresh
set oldstuff = nothing

' purge data which already exists in the master database
db.execute "delete from newdata where exists (select * from olddata" _
& " where olddata.id = newdata.id)", dbfailonerror or dbseechanges

--
Joe Foster <mailto:jfoster@ricochet.net> Got Thetans? <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!