Split Database Processing Speed 1

[RacerGirl117]

Hello All,

I just split a database that, unsplit, is about 12MB. I created a test folder and copied the database into it, you know, just in case. Once I figured out that Access's splitter wizard doesn't like linked tables, I removed the linked tables, split the database, and then put the linked tables back in.

The problem I'm seeing now is that processing speed is way down. I even copied the front end database to my hard drive and ran it from there, but it's still very slow. There are a few key forms that people use and I know they will complain if they take too long (10-15 seconds compared to miliseconds now) to open.

Is there any other way to speed up processing in a split database?

Thanks in advance,

Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

http://www.firefighter-pgh.com

 

[psemianonymous]

Access takes some time to get "warmed up", specifically it needs to make a connection to the back-end database. If no connection is "on", it has to establish a network connection, open (a possibly new) LDB file for the database, and THEN it can get to the back-end.

There are ways of keeping the connection open; the easiest way is to keep a bound form open at all times, i.e. on your main menu. It doesn't have to be doing anything, it just has to be open.

After the first "connection", there should be no speed hitches.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[ReluctantDataGuy]

Try this... Can't hurt.

 

[RacerGirl117]

That does help some, but it's still not as fast as the "original" database.

A friend of mine gave me the below information, but I haven't the slightest idea where to go to do this stuff. Any help would be appreciated.

Create a global variable.
Open a recordset to the data.mdb

In any Module put:
dim grs as DAO.Recordset

Run this next piece one time when the app opens:
set grs = currentdb.Openrecordset("Tablename&quot

Somewhere, before the user exits:
grs.close
set grs = nothing


Thanks in advance,

Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

http://www.firefighter-pgh.com

 

[JeremyNYC]

Jessica,

There's no need to do that--having the bound form open will do the same thing.

However, if you make extensive use of database objects, it definitely makes sense to set a database object when the database opens and kill it when the database closes--it will speed every process that uses a database object.

To do this, put "public db as dao.database" right under the "option explicit" line of any normal module. In the form that's going to stay open all the time, put this line in the Open event:
set db = currentdb

In the Close event of that same form, put this line:
db.close
set db = nothing
(OK, it's two lines<g>)

Then you can get rid of every other declaration and setting of a database you do in your code.

All of that said, yes, a split database will be slower than an unsplit database. A properly designed split database, however, will be quite fast, none-the-less.

The front end should absolutely be on the user's PC. Keep a copy on the server and have the user grab it every time there's a new version. Code to do this is on my site, under Deploying Databases.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[RacerGirl117]

Thanks Jeremy. I'll give that a shot and let you know.

Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

http://www.firefighter-pgh.com

 

[throwdini]

I have a similar problem with an application I made. It works on several different back ends. With smaller back ends (40,000 kb) there is no real lag time, but on a larger back end (300,000 kb), or when it links to a MySQL data source there is a significant lag time. Will the open bound form help this too? (Also what do you mean by a bound form?)

I have the back end data split into 2 main tables, one contains basic info (name, address) the second contains the info that is updated by users. There is a 1-to-1 relationship b/w these tables. Is it necessary to have these as two tables or will a flat file system work better?

Any help is appreciated.
Thanks,
Jay

 

[psemianonymous]

With huge tables such as yours, the split structure might be most beneficial. Is it 1:1 required on both sides, or is one side optional? If there is the optional info, leave it as is.

You might also have such a huge db because it hasn't been compacted recently. Check into that.

Indexes speed up SELECT queries by an order of magnitude, so if you're doing searches inside a table, or sorting on a field, or something to that effect, then index that field. (Indexing a huge table will take a while). Indexes slow down INSERT/UPDATEs though (by a noticeable but not incredible factor), so be warned.

A bound form is just a form with the &quot;data source&quot; set. Keeping it open will prevent the necessity of creating/deleting the LDB locking file repeatedly; the file is created when you enter the db and closed when you leave the db, not (hundreds?) of times in between. This is especially noticeable on network servers.

MySQL backends are not file-based, and so the &quot;warm connection&quot; fix for Access backends does not apply at all to MySQL. I can't give you more details because I do not know them.