Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    database splitter (Access 2000)

    I am just wondering if there is really any performance gain if I split the database to backend and have the program as front end. I have a database (23MB) and I created a simple unbound form to access the data. I notice that if I access the data using the frontend, it takes a while to load the form through the network. Once the info is cached, everything works quickly. But the initial loading takes a long time.

    I tried the same database with no backend/frontend, just a simple mdb file and put it on a shared folder. When multiple machine load up the database and open the form, it is really quick, almost instant. I wonder why is that the case.

    Thanks

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: database splitter (Access 2000)

    The proper situation is to place the backend on your network file server, and copy of the frontend is place on each workstation's local drive. The enables forms to load faster, since it is loading from the local drive, and only getting data from the network.

    However, performance is only one of the reasons to split a database. Split databases tend to have fewer corruption problems. It is also easier to maintain forms/reports on a split database, as you can make your changes, test, and then give everyone a new copy of the frontend; all without touching the data itself.

    I always use a split database schema, even for databases that will be used on a single, stand-alone PC.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: database splitter (Access 2000)

    You have to be sure you're comparing apples to apples. Whe you split the database, where did you put the front and back ends? With a multi-user setup, the normal location from front ends is on the local workstation.

    An unbound form should load exactly the same either way, so I suspect that either it isn't really unbound or there's something you aren't telling us. What do you mean by an "unbound form to access the data" anyhow? An unbound form doesn't access data, at least, not unless you use a bunch of code to populate controls and then write the results back to a table. If multiple loads from the server are instantaneous, I have to suspect you're running it from Citrix or Transaction Server and not opening it directly from an installed instance of Access.

    As Mark pointed out, performance is not really the primary reason for splitting a database, although performance usually improves with a local front end. Like him, I always design my databases split. When you have to maintain the things over time, you quickly discover that having the data in a database of it's own can save your sanity when your interface database becomes corrupted and you have to resort to backups to restore it.
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: database splitter (Access 2000)

    Ok, here is the situation: The backend (splitted by the database splitter wizard) is stored on a server machine and the two workstations each has a copy of the front end (also created by the wizard). The form on the frontend is unbound and what that means is there is nothing on the record source on the form properties. There is no code in the form load/open event to retrieve data either. There is a combo box which use a SQL statement in the row source to look up info in a table (a few hundred records).

    When I open the frontend and start the form, it takes a while to open this form. Once it is loaded, everything works swiftly. I can close the form and re-open it and it is up there instantly, unlike the first time. I close the entire database and back to Window. Re-open the database again and re-load the form, everything comes up quickly and I suspect that the cache in RAM helps here. When I open another form in the same frontend, since this will be the first time to open another form, this form will take a long time to load.

    Because of that, I copy the original database and store it on the server. Create a short cut to it and place the short cut on the workstations. Start the database from the short cut and open the form. The form pops up instantly, unlike the frontend / backend situation. Both workstations come up with the same performance.

    Both the server and workstation machines run on Win2K professional with 256MB RAM, PII 333, 100MB/sec network.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: database splitter (Access 2000)

    It sounds like your application has gotten decompiled. Did you compile it and then compact before you tried testing it?

    I've never used the splitter wizard, so I have no idea how it might mangle the database in whatever it does. I still don't understand your description of your form, since any form that looks up the information in a table and displays it is either bound or unbound. *When* it gets its recordsource set is a whole different issue.

    What exactly is it that you wnat from us? We can't know what the splitter did to your database or even guess at how you have it set up, so there isn't any way we can explain what you're seeing. If you have a specific question about how to do something or how something works, you can probably get an answer here. If you just want us to justify splitting a database, there's no way we can change your mind if you've already made it up.
    Charlotte

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: database splitter (Access 2000)

    Oh, and you're running Access on the bare minimum class of machine that will handle it. That has a profound effect on performance.
    Charlotte

  7. #7
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: database splitter (Access 2000)

    the original database was 23MB and after splitting it, both the frontend and backend were 23MB as well. I then compacted the database which made the frontend down to 200K. The form has a few textboxes in which the user will enter the time and amount. A click on the save button will open up a recordset via ADO to save the data. A few simple transaction screen.

    No, I have not made up my mind if I should or should not split the database. I just want others to share their experience on this. I split the frontend and backend using SQL server all the times and I don't have that performance issue to deal with. Of course SQL server backend and Access backend are totally two different issues. I was just surprised that the very first time I loaded the form and it took a while for it to be displayed. That's what puzzled me.

    Thanks for your insight on this matter.

  8. #8
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: database splitter (Access 2000)

    A couple of thoughts:-

    You said
    <hr>There is a combo box which use a SQL statement in the row source to look up info in a table (a few hundred records).<hr>

    This will certainly slow things down the first time that you open the form because the SQL statement needs to be optimised before it is run. Try to use a saved query as your combo row source because this will be optimised ahead of time.

    Without a doubt you should split the front-end forms, queries etc. from the back-end data tables for the reasons that Charlotte/Mark have so eloquently expressed. <img src=/S/bow.gif border=0 alt=bow width=15 height=15>
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: database splitter (Access 2000)

    Now I'm confused. Splitting the database into a front and back end is an entirely separate issue from upsizing to SQL Server, which incidentally splits the database at the same time. SQL Server *should* be much faster, whether you use linked tables or access remote tables in code or use an ADP front end. In either case, the place to look for cause of the delay is however you populate the combobox.
    Charlotte

  10. #10
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: database splitter (Access 2000)

    I think I have the answer to the problem. As I have been saying all along, the very FIRST time when I loaded the form it is slow and that's all. I suspect that somehow Access needs to optimize the frontend and backend together for the very first time when a form is loaded. Once this form has been loaded and everything is optimized, the optimized info is saved. Let's say you open the database a few days later and repeat the same process, the frontend needs no optimization (because it was optimized days ago) any further and thus will be loaded instantly.

    Again, the performance is an issue only the very FIRST time you open the form after splitting the database into backend and frontend. If you have 3 workstations with its own copy of the frontend, you should open the frontend and every form on each workstation before letting users to use the application.

    Charlotte is correct that SQL Server should be must faster and I think this is because the ADP frontend is optimized already when it is first created. The combobox has no significant performance effect as I have tested it on SQL server, splitted frontend/backend, and a single mdb file. They all returned the value within the same amount of time.

    Thanks for all the help.

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: database splitter (Access 2000)

    You still aren't comparing equivalent situations. An ADP is NOT the equivalent of an MDB, with or without linked tables in the MDB. They're entirely different things and an ADP has nothing to do with splitting the front and back end, since an ADP *always* is split, with a SQL Server backend.
    Charlotte

  12. #12
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: database splitter (Access 2000)

    It sounds to me as if the front-end database is not compiled after you use the splitter. We usually recommend decompiling and then recompiling the database before you put it into production in any event, and I suspect if you do that you will find the form loads as fast the first time as every other time.
    Wendell

  13. #13
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: database splitter (Access 2000)

    Wendell,

    that is the answer that I am looking for. In fact after re-compiling the front-end and distribute it to other workstations, they all work quickly as expected.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •