Multiple Update Queries slowing entire database

[Daadian]

Okay my database is going to be very large, monthly totaling about 600,000 records. (I plan to keep seperate databases every month just for size and speed purposes).

Currently the databases imports a text file daily into a table. Using VBA, after the import is done, approx 20 update queries are run to clean and update the data in ONE of the fields. The change is based on several possible "rules" for the update.

For example:

Update column A to "xxx" if column b like "AA*" and column C = forms!form!asofdate

or

Update column A to "xxx" if column b like "BB*" and column D >="D*" and column C = forms!form!asofdate

As I said there are about 20 rules for the value of column A all handle by individual update queries. That need to be run in a specific order because some rules supercede others.

I have it working but it has slowed down the entire operation of the database. even with only 30,000 records. I don't mind the initial time it takes for the import or the initial run of updates. But once that is done I can't see the reason for the slow down of everything else.

Any suggestions to clean this update or a suggestion for a better way to run these update rules ?

Thanks

 

[HiTechCoach]

What slows down?

Is the database split?

 

[Daadian]

What slows down?

Is the database split?

EVERYTHING slows down, opening very simple forms is slow, loading drop down box values pulled from a table is slow. Opening the main table itself is slow.

And no its a single database, with for all intensive purposes a single table holding all of the data.

 

[Daadian]

Compact and Reapir fixed my slowness problem. Phew ! Any suggestions on avoiding using 20+ update queries or is that really a problem ?

 

[Daadian]

LOL compact and repair fixed it right up.

Okay another question is there an easier, or more efficient way, to handle all of these queries or is this just fine to do things this way ?

 

[HiTechCoach]

IMHO, running multiple queries is not a problem as far as I know.

 

[LPurvis]

No - but running 20 of them on one field seems unecessary.
I take it you're performing some simple logic in each one and progressively tidying up further with each subsequent update?
A single update based on logic performed in a function should be preferable.
Did you stick with so many updates because each query runs quickly alone but with the complexity of calling a function (to which you, presumably, have to pass the field value) slows the processing down so much?

That you're getting either database file size creep and certainly data and index paging well out of alignment is possibly the result of so many updates and hence the eventual performance problems. (Compacting has ordered your data and allows the Indexes to be more efficient for you).