Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Mar 2004
    Posts
    5

    Unanswered: Poor Split DB Performance

    I have a DB application being used by people in two different location within New Jersey and a third location in Bangalore India. we're looking into way to improve the performance of the interface which at this stage is far too slow. I read an article on this forum (http://dbforums.com/arch/84/2003/4/743030) which talked about splitting the DB into a front and back-end and distributing the front-end if performance was an issue. The front/back end approach had a number of benefits and moreover distributing the front ends to the local machines to cut down on network traffic and improve performance sounded like a perfect fit.

    Unfortunately, when we tested the split application the performance was worse the having people access a signal file directly. I was somewhat surprised by this. The slow down seems to be more so when you open or switch between forms. There is a long delay while what appears to be a large bulk of data, I assume the linked table, is transferred to the local machine. Additionally some of the forms have events that force a requery on a record change which really has an impact.

    I'm looking for pointers on what I've done wrong or tricks/things I should look for to improve the performance of the application for remote access. My only other alternative is to place separate copies in the three locations and do a nightly sync. Something I'd like to avoid at all costs. Then again that may be my next posting to the forum:-(
    Robert M. Bartis

  2. #2
    Join Date
    Feb 2004
    Posts
    142
    On apps spread out over this wide an area it is best to sync. You can still do the split app for easier maintence.

    Another solution would be to consider using something like SQL server on the back end. With good indexes the Server will offer better performance.
    KC

  3. #3
    Join Date
    Mar 2004
    Posts
    5
    Originally posted by AZ KC
    On apps spread out over this wide an area it is best to sync. You can still do the split app for easier maintence.

    Another solution would be to consider using something like SQL server on the back end. With good indexes the Server will offer better performance.
    Do you have suggestions on how to so a sync? I've never done that. My concern is every time I change the structure of the DB I'll need to make changes to the code that does the sync operation. I'm not an expert, just dangerous enough to cause problem. Unfortunately, I'm all they got:-(

    The idea of using SQL server as the back end is interesting. What does that mean? Setting it up shouldn't be an issue, but can the existing data/structure be exported easily? can the front-end that results from the MS access split still be used to access the data without change? How do you maintain the data ones its exported. I've never used the SQL server.

  4. #4
    Join Date
    Mar 2004
    Posts
    29
    SQL Server is a great option if you have it available...

    However since I did not here's what I did...

    Create your front end and your back end for a start

    Synch your front end and instead of just linking your databases just have the front end make a local copy of the tables/records that the users will most likely use.

    Then you can do one of two things either....

    A) have users make the changes only on the front end al la replica style then update everything (you may as well make a partial replica if you want to do it this way)

    or

    B) Have the front end use the Local tables to view/search and whatnot but updates and appends would be done to BOTH the local and back-end shared database.

    I do it this way because I put sycronization in the hands of the user and I use sycronization only to change the logic/build of the client, but my reporting needs to be as close to real time as possible so I can't have users failing to synch up all the time.

  5. #5
    Join Date
    Mar 2004
    Posts
    5
    Originally posted by Coldendus
    SQL Server is a great option if you have it available...

    However since I did not here's what I did...

    Create your front end and your back end for a start

    Synch your front end and instead of just linking your databases just have the front end make a local copy of the tables/records that the users will most likely use.

    Then you can do one of two things either....

    A) have users make the changes only on the front end al la replica style then update everything (you may as well make a partial replica if you want to do it this way)

    or

    B) Have the front end use the Local tables to view/search and whatnot but updates and appends would be done to BOTH the local and back-end shared database.

    I do it this way because I put sycronization in the hands of the user and I use sycronization only to change the logic/build of the client, but my reporting needs to be as close to real time as possible so I can't have users failing to synch up all the time.
    Sorry for being so ignorant, but can you help me to understand where to find information on creating local copies of tables and how to implment option B. Never used replica's, but will investigate.

  6. #6
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    with three locations I assume you are using VPN technology, and if that is the case then the split frontend/backend is a viable solution. Database replication (in essence using disconnected recordsets) can be very delicate and should you go thid route you may want to invest in the developer edition of office where you can invoke the replication manager - allowing you to set replication on timer events and such.

    My first concern would be the degradation of performance

    What version of access?

    are all of the systems using the most up to date versions (SP1, 2, etc) of Access?

    A split database theoretically is supposed to faster as well as easier to maintain.
    check out the qarticles below from the support.microsoft.com site and see if they apply

    http://support.microsoft.com/default...roduct=acc2000
    ACC2000: Slower Performance on Linked Tables
    (261000) - You may notice that linked tables in your database have slower performance when you open tables, or that performing updates takes longer than you expect. You may notice this behavior after you convert the database from an earlier version of Microsoft...

    http://support.microsoft.com/default...roduct=acc2000
    ACC2000: Slow Performance Opening Object with Name AutoCorrect Enabled

    http://support.microsoft.com/default...roduct=acc2000
    (200600) - If a database has the Name AutoCorrect feature enabled, users may experience a delay when they try to open database objects.



    ACC2000: Slow Performance When You Open Objects in Design View in Access Running on Microsoft Windows NT 4.0
    (264239) - After you upgrade to Microsoft Windows NT 4.0 Service Pack 6/6a on a client computer without upgrading the server to the same service pack, opening Access objects such as forms, reports, macros, or modules in Design View across a network is unusually...

    http://support.microsoft.com/default...roduct=acc2000
    ACC2000: Defragment and Compact Database to Improve Performance
    (209769) - You can improve the performance of Microsoft Access if you periodically defragment your hard disk and compact your database.

    http://support.microsoft.com/default...roduct=acc2000
    ACC2000: Tips for Improving Subform Performance
    (209113) - This article lists several things that you can do to improve the speed and performance of your subforms.
    Last edited by axsprog; 03-09-04 at 16:21.
    Dale Houston, TX

  7. #7
    Join Date
    Mar 2004
    Posts
    5
    Originally posted by axsprog
    with three locations I assume you are using VPN technology, and if that is the case then the split frontend/backend is a viable solution. Database replication (in essence using disconnected recordsets) can be very delicate and should you go thid route you may want to invest in the developer edition of office where you can invoke the replication manager - allowing you to set replication on timer events and such.

    My first concern would be the degradation of performance

    What version of access?

    are all of the systems using the most up to date versions (SP1, 2, etc) of Access?

    A split database theoretically is supposed to faster as well as easier to maintain.
    check out the qarticles below from the support.microsoft.com site and see if they apply

    http://support.microsoft.com/default...roduct=acc2000
    ACC2000: Slower Performance on Linked Tables
    (261000) - You may notice that linked tables in your database have slower performance when you open tables, or that performing updates takes longer than you expect. You may notice this behavior after you convert the database from an earlier version of Microsoft...

    http://support.microsoft.com/default...roduct=acc2000
    ACC2000: Slow Performance Opening Object with Name AutoCorrect Enabled

    http://support.microsoft.com/default...roduct=acc2000
    (200600) - If a database has the Name AutoCorrect feature enabled, users may experience a delay when they try to open database objects.



    ACC2000: Slow Performance When You Open Objects in Design View in Access Running on Microsoft Windows NT 4.0
    (264239) - After you upgrade to Microsoft Windows NT 4.0 Service Pack 6/6a on a client computer without upgrading the server to the same service pack, opening Access objects such as forms, reports, macros, or modules in Design View across a network is unusually...

    http://support.microsoft.com/default...roduct=acc2000
    ACC2000: Defragment and Compact Database to Improve Performance
    (209769) - You can improve the performance of Microsoft Access if you periodically defragment your hard disk and compact your database.

    http://support.microsoft.com/default...roduct=acc2000
    ACC2000: Tips for Improving Subform Performance
    (209113) - This article lists several things that you can do to improve the speed and performance of your subforms.

    Yes we're all using the same version of Access and VPN. Everyone saw the same result. The split DB was much slower than the single file. More specifically, the split DB took at least twice as long to open a form, sometime well into the 60sec+ range. Once opened it seemed to be reasonable quick to navigate between records. The problem is the speed is so slow that tables that work in the split version when in the office failed to populate correctly over a VPN Broadband connection. I plan to have approx 20+ users and from what I can see Access as a back-end may be a bad option anyway. Just not sure how complicated going to a SQ Server is or how difficult it is to move the data from a back-end access file to an SQL server.


    Thanks for the articles though I will read through them.

  8. #8
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    ok you still have not looked at all of the causes. Did you look at any of the articles I suggested.

    Have you compacted the front end and the backend.

    Here is what is possibly happenning :

    These new tables in the backend are now "linked" to the frontend, and these links need to be read by Access for integrity, location, etc.

    Make sure that all users have the most up to date SP releases for Access.

    Compact the back end

    open each individual table from the db window in the front end.

    import the front end objetcs (no tables!) into a new db and make sure to set any references in the VBE window that you may have.
    then relink to the backend tables
    test you forms - does the performance increase?

    remember if you go to SQL you need a SQL admin (money)
    Also if need be you could upsize to the runtime SQL (MDE) with just as good performance in a db less than say 600 mb
    Dale Houston, TX

  9. #9
    Join Date
    Feb 2004
    Posts
    142
    Originally posted by rmbartis
    Yes we're all using the same version of Access and VPN. Everyone saw the same result. The split DB was much slower than the single file. More specifically, the split DB took at least twice as long to open a form, sometime well into the 60sec+ range. Once opened it seemed to be reasonable quick to navigate between records. The problem is the speed is so slow that tables that work in the split version when in the office failed to populate correctly over a VPN Broadband connection. I plan to have approx 20+ users and from what I can see Access as a back-end may be a bad option anyway. Just not sure how complicated going to a SQ Server is or how difficult it is to move the data from a back-end access file to an SQL server.


    Thanks for the articles though I will read through them.
    A split database should be on very close to the front end - like on a shared network folder on the local network. If you are trying to run a split design across a larger area then performance will suck.

    SQL Server will cost $ for both software and hardware. If you are in a larger company, see if any other department has additional capacity on a database platform that could be bartered for. I've used Access with SAP, MaxDB, Oracle and SQL server with good results.
    KC

  10. #10
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    bandwidth is bandwidth is bandwidth.

    Your performance hit is caused by the initial relinking to the backend tables

    recreate the front end in a new container (mdb) and then relink to the backend tables.

    As a past Microsoft employee (Internal Trainer for Microsoft Access)
    we used Access databases for various internal tools that were accessable (VPN) from sites world wide, and performance was not an issue if you use good design.
    Dale Houston, TX

  11. #11
    Join Date
    Mar 2004
    Posts
    2
    I have 2 databases ( with the same tables, queries, etc , same structure). Can someone help me on making a procedure to make one database from this two databases ?

  12. #12
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369
    One tip I came across regarding performance involved making just a slight change to "any" front end form and save it before you distribute the front end - that is doing this AFTER you make a Compact and Repair. Supposedly that could affect performance for some reason. Easy to try out at least. Not suggesting that this small thing may solve the whole of your situation, though ;-)

    Regarding costs and other back-ends:
    It is also possible to use MySQL, though that would mean some more manual adjustments. It seems to be necessary to remake parts of the front end anyway if you are going to switch to SQL Server. There is a general option that could be set before making the DB in order to have Access generate SQL92-compatible queries. This is not turned on by default, so it is a good chance your db has all queries in a language/version that needs to be adjusted/rewritten in order to work towards SQL server or MySQL.

    Daniel.

  13. #13
    Join Date
    Mar 2004
    Posts
    5
    Originally posted by kedaniel
    One tip I came across regarding performance involved making just a slight change to "any" front end form and save it before you distribute the front end - that is doing this AFTER you make a Compact and Repair. Supposedly that could affect performance for some reason. Easy to try out at least. Not suggesting that this small thing may solve the whole of your situation, though ;-)

    Regarding costs and other back-ends:
    It is also possible to use MySQL, though that would mean some more manual adjustments. It seems to be necessary to remake parts of the front end anyway if you are going to switch to SQL Server. There is a general option that could be set before making the DB in order to have Access generate SQL92-compatible queries. This is not turned on by default, so it is a good chance your db has all queries in a language/version that needs to be adjusted/rewritten in order to work towards SQL server or MySQL.

    Daniel.
    I looked in MS DB for help on SQL92, but did not see the option. Do you know where the option is? Using MS2000. Leaning toward SQL Server, but I like the idea of MySQL.

  14. #14
    Join Date
    Feb 2004
    Posts
    3
    well if its any consolation, i am having the exact same problem that you are... my backend is on the server, and the frontend is on my workstation/laptop.....and performance is seriously hindered this way... im still doing some reading to see whatelse i can come up with.....im not much of a network guru, but my network administrator told me the reason its slow is because we have file servers and not data servers...is there a difference and if so would this be my problem?

  15. #15
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    data server meaning the processing is done server side and results are passed as text streams to the user. To determine the reason for performance hits, one must analyze the type of dataprocessing that is being done and the technology or methods that are being used (DAO, ADO, SQL, etc) And I also must reemphasize bandwidth is bandwidth, so evn on a sql server if an end user is returning records , there is no guarantee that the records will be available any faster with sql backend on small record sets. Performance is based on indexing, sorting, etc.
    Dale Houston, TX

Posting Permissions

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