Huge .mdb database

[JoaoAfonso]

Ahoy.

I have put all data I had in a SQLite database in a .mdb database via MS Access. I see right now what can be a problem: SQLite DB occupied 140Kb, and this one occupies 7,1Mb. The reason is, almost for sure, some longtext variables I have. Will this delay the queries/read/write speed using EB own database library?

[Ionic Wind Support Team]

7.1 MB isn't that big in MS Access terms.   You can compact the database in Access to remove empty space and make it a bit smaller.

Paul.

[JoaoAfonso]

Wasn't aware of it, but my main preocupation is access time. Space it isn't.

I just thought was too much comparing to sql lite.

Thank you

[Ionic Wind Support Team]

Databases aren't like reading files sequentially.  The speed largely depends on the driver and how it stores indexes to the tables.  MS Access is quick enough after the first read, meaning the slowest access time will always be the first time you read a record.

An old speediup trick used to be to read the last record in the table first, and then go get the data you really want.  Probably doesn't apply anymore but if you need it, try it    The way you code your SQL statements can also have a big impact in speed. 

Paul.

[Rock Ridge Farm (Larry)]

How you index it will also affect speed. If you search on a non-indexed field it will be a serial search.
Pre-define your searches and verify that each field searched is indexed and normalize the field names across tables.

[JoaoAfonso]

Thanks for the tips.

Btw, happy new year

[JoaoAfonso]

One more question regarding DB: in order to decrease the time spent doing queries/inserts/deletes/updates, is there a way to load the entire DB into memory, work just in memory and save the file from times to times? I assume this is not what happens because if I do an insert in my EB program and I open the DB with access, it shows immediately the change. Is there a way, so, to work in a temporary DB in memory? And if yes, how to work with it?