Page 1 of 3 123 LastLast
Results 1 to 15 of 36
  1. #1
    Join Date
    Apr 2008
    Posts
    13

    Unanswered: FE / BE - how much improvement?

    Hi all,

    I'm a former lurker and new poster, thanks for all the past info I've got from this site. I have a question about FE / BE speed. I understand the standard issues with a FE / BE split, and am wondering how much of a speed improvement we might expect from changing our setup.

    Currently our FE and BE are on the same computer, and users have a shortcut to the FE on their computers. We've done this so far because there is no regular computer person here, and I would email updates from a distance, and it was much easier for a person here to just install a single new form in a single FE rather than copy FEs around the network and check versions, etc. But now it may be practical to have the FEs on each user's computer, so I'm wondering if it's worth the change. (Thinking "if it ain't broke, don't fix it".)

    We have a 100mb BE and 25mb FE with about 3-8 users. Not heavy data entry (a new invoice or PO every 10 minutes), more often running queries and reports. Simple peer-to-peer network and file-sharing. WinXP and Access 2002 with a 2000 format, using DAO since the db dates from Access 2.0.

    Thanks!
    Jerry

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Depending on the user's connection to the network drive (where the BE resides), you may actually get slower performance. A lot depends on the speed of the connection to the network drive, free memory, form design, coding, etc. But you also may see improvement if the user's computer is faster, has more memory, there is a good connection, etc. There's no definative answer to your question except it depends on different factors and I'd test it first on the slowest user's computer before going through the change.

    Personally, I like to keep the FE and BE in the same location (except when I use SQL Server as a BE). Having FE's on every user's computer is a pain to keep updated and I don't think there are pros which outweigh the cons when using good techniques. I also like to use the cloning technique (see code bank) to clone the source mdb/mde with the user's name so the source FE always stays lean, fast and never get's corrupted. Users just have a shortcut on their desktop to the vb script in the example which takes less than a second to launch. I never have to worry about compacting/repairing or deleting temp tables from the source mdb/mde utilzing the vb script. It really will help with performance using it (as each user is actually in their own cloned FE which is regenerated everytime they open the mde via the script). I usually never allow user's to open the mde directly (and often put in code in the Access app to determine if they are opening the source mde.)

    If you seriously want to see a big speed improvement though, design the forms unbound (if they aren't already) and opt with a SQL Server BE. Also if you haven't already, make a FE mde versus using an mdb for improved performance. You'd be amazed though at the difference in speed between unbound and bound forms.

    As an example, on a network server using SQL Server as a BE and mde FE's and using unbound forms, users were opening records in a db with about 3 million records within a second (or two but definately less than 3 seconds). I've always used 3 seconds as a max for returning/updating/searches/etc..
    Last edited by pkstormy; 04-09-08 at 17:16.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Apr 2008
    Posts
    13
    Thanks, pkstormy! I'll do a test and see what happens. I had read on some other forums that having the FE on the users' computer is by far the best, it's good to hear your balanced opinion.

    We're too small a company to afford SQL Server and the maintenance it would require, I understand the software and setup alone would cost several thousand dollars? And most things are currently under 3 seconds, except for large reports.

    Hadn't thought about making the FE into an MDE. The shortcuts the users use to get to the FE start up Access runtime. Would an MDE still be better? I'll test it and see.

    Hadn't heard about the "cloning" technique. I'll look it up on this site.

    Thanks again!

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    SQL Server Express is FREE! (maintenance is minimal if any but there is a small learning curve to set up SQL Server.) By far though, you get a better db server, backups (every 10 minutes or on the hour or whenever you specify if you wish) and much much more. SQL Server for me was actually more of a set-it and forget-it type of thing where after initially setting it up (other than adding new users for permissions), I very rarely had to do anything with it (but from your standpoint, it might make your life more difficult with table/field changes if you do this regularly.)

    It is an eye-opener though to actually see your code/transactions in action over the network via a Trace on SQL Server. First time I did a SQL Server Trace, I couldn't believe how much comboboxes can slow things down on unbound forms. I thought my forms were fairly fast but you'd be surprised at the improved speed simply by changing comboboxes to textboxes (although this is not ideal in all cases.) But having a form with 1 combobox verses a form with 15 comboboxes can really make a difference. It sounds like speed is not an issue though for you so as you stated "if ain't broke, why fix it?".

    I'd still create an mde - protects you from the user's getting into any code and it is better performing than an mdb - it's compiled.

    As a tip - change the recordsource query property (the Recordset Type line) for your large report from a Dynaset to a Snapshot (and for all sub-reports) - then check out the speed of the report. You should see a noticable difference (unless you've already done this) but let me know - I'm curious.
    Last edited by pkstormy; 04-09-08 at 17:50.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd deploy the front end to individual workstations.. saves network traffic
    ..assuming you are using JET....
    I'd dump some data into local tables, I'd consider dumping heavily hit tables to local tables on start up...
    I'd consider a redewsign that did more work on local tables.. sometimes it makes sense however perverse to copy data to the ocal disk and hammer the local disk rather than the network.

    at the sort of users you are referrign to I don't think you need to switch to a server backend just yet.. unless you are already findign problems

    I'd consider designing future applications using unbound recordsets.. it does mean loosing all thise dinky tools that make Access a breeze to develop front ends.

    Id think seriously about nay/ every bit of betwrik trafic to get or post data back...... combo and list boxes are greedy on resources.. but quite often you can get rond that by cipoying the data used in the combo boxes to a local table (eg copy customer name & id, copy product name & id....)

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just to throw my ingredients in to the pot I would say stick with a single front end which all users can access on a networked drive.

    The maintainability of this solution far outweighs the very minimal speed gains you may or may not achieve by deploying each user with their own front end.

    As Paul has touched on above, as long as you have designed your application cleverly (great example is indeed multiple comboboxes on one form!) then you will be fine.

    In your situation I think I would still split the app into FE and BE "parts" , but keep them on the same drive.

    Another great benefit of using a network drive front end is that you can set permissions to the stuff using windows security! You can apply it to a file, folder etc and even have a domain group which has access.

    Access security doesn't get much easier than that, eh?

    Anyhow, I'm not going to go on about it because it really is a 50/50 debate about what is the right method. Good luck
    George
    Home | Blog

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    And I'm afraid I do not support the transfer of entire tables to a local drive to toy with; the exception being a very rarely changing "lookup" table... Even then I would be tentative to do so.
    George
    Home | Blog

  8. #8
    Join Date
    Jun 2006
    Posts
    72
    One thing I am considering doing is having the FE on remote computers and have a batch that updates to the server master copy every time they open it. Also have any form that doesn't alter data in the BE and have a batch on the remote computer to copy the newest BE to the computer and open it and call it a "Reporting interface" or something.

    I'm not sure how this will effect network trafic but for the way my database is used, I don't think it will be a problem.

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by pkstormy
    Personally, I like to keep the FE and BE in the same location (except when I use SQL Server as a BE). Having FE's on every user's computer is a pain to keep updated and I don't think there are pros which outweigh the cons when using good techniques.
    Interesting. I've always done the each user has their own FE on their computer so as to save all the network traffic in requesting form and report objects. I've noticed significant improvements doing this on some applications, especially graphics-intensive ones. The more heavy the form is, the more of a difference it makes... and it also makes a difference with higher numbers of users. I also have code that checks for the latest version and installs it when necessary, so I don't find it difficult to keep users up to date.

    The launch scripting idea might be good though, re-installing the application every time you launch it does have it's benefits, but might take a few more seconds to launch the application each time... all pros and cons
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by StarTrekker
    Interesting. I've always done the each user has their own FE on their computer so as to save all the network traffic in requesting form and report objects. I've noticed significant improvements doing this on some applications, especially graphics-intensive ones. The more heavy the form is, the more of a difference it makes... and it also makes a difference with higher numbers of users. I also have code that checks for the latest version and installs it when necessary, so I don't find it difficult to keep users up to date.

    The launch scripting idea might be good though, re-installing the application every time you launch it does have it's benefits, but might take a few more seconds to launch the application each time... all pros and cons
    You remember this thing called RAM, right? When you open a networked Access file you will only have to transfer objects when requested; so if you open up your database with your main form, that's all that has to load on that call; and guess what else; the objects will be stored in local memory! So if you navigate away from the form and reload it aint going to be a problem.

    (Note that this is all assuming you have programmed the thing cleverly and you have a few Mb of RAM knocking around on the local PC and you're networking infrastructure is of a reasonable standard)

    Anyhow, do you not think that when you make an updated version, every time someone opens the app they will have to re-install the entire thing, rather than one object at a time?! I'm sure that at 8:30am the morning after you make an update will be the most network intensive moment for your app.

    Pah, my feelings on this subject have just got stronger.


    ...I'd like to point out that I have an app sat on a networked drive which the shortcut to which is distributed to nearly 30 users in IT. Many of these users will sit with this open in the background all day and I have recieved no concurrency or speed issues.

    And heck, if you want to be really clever about deployment, you make the shortcut you distribute point to a batch file
    Code:
    @ECHO OFF
    
    START mydb.mde
    EXIT
    And when you want to deploy a new version you can do it seamlessly; new file name, edi tthe batch file and wham-bam-thank-you-man; the next time they click that icon they open the new version.

    Not to mention that all I have to do to give a new user access is to add them to a domain group that has access to that folder and send them the shortcut in a batch file.

    What could be easier?

    Quote Originally Posted by Einstein
    Make everything as simple as possible, but not simpler
    George
    Home | Blog

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    one good reason to use a local front end is there is less risk of contentions, corrupiton and other problems with multiple people accessing the samee front end.

    it allows users to take a sanpshot of their own data into local tables manipulate it in any format they want without affecting other users. we had a generic analysis report which the users could manipulate to work out what they wanted it was a pig to try to do on a local servcer, and was messy stroign tables on a central front end.. the damn FE kept bloating as temporary tables were added or removed, and one more than one occasion due to (ahem) a programming cockup we had mor ethan one person dumping data into the same temporary table... caused chaos for the beancounters that month (tee hee)

    I agree copying data to local disks is not necesarily a good idea... especially if its volatile. I have used the technique befiore on know static datat to dramatically improvie performance.

    I would not store data locally that was voltile... thats dangerously silly practise, and should gurantee a nomiation in the daily WTF.

    We had a problem with a busy network,lots of information that was static (downloaded from the corporate box overnight). the key elements of the static infiomartion were dumped into local tables.. yes the app could take a while to load.. but the users were happy with that they knew what was happening, and besides which it gave them time to start yabbering on prior to starting work.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It's just another beautiful arguement for going unbound, eh?

    Never load anything unless you have to.

    Comboboxes are a great example; why populate them on form load when you could so easily wait and see if the user even wants to use them

    *special mention of thanks to Paul for his help with regards to linking Access into AD; I never knew it was possible until I saw his example in the code bank
    George
    Home | Blog

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    It's all pros and cons eh? I still see many of each for both sides of this.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Oh you know you can't sit on the fence like that!
    George
    Home | Blog

  15. #15
    Join Date
    Apr 2008
    Posts
    13
    Wow, thanks for all the opinions and ideas! I'm a bit overwhelmed, in a good way.

    I suppose I'm a decent programmer, but can't say the db has "clever" design or not - someone else designed and programmed it originally, and I'm self-taught in Access VB.

    What is an "unbound recordset"?

    The main issue with my workplace is that they don't have a regular computer person, due to limited need and finances. I live abroad and visit every 6 months and by then they have a few projects and updates they'd like me to do. I don't want to set up anything that would blow up while I'm gone, then they'd have to hire a consultant who would charge $100+ just to put a button on a form to open a report (this happened once before my time).

    Our network is just simple peer-to-peer file-sharing. Security is simply done through code and a password from the startup form. Not strong I know, but it works well enough in this environment and anything more complicated wouldn't be manageable while I'm away.

    How would SQL Server Express fit into this scenario? I didn't know about it before. Increased speed would always be nice - it's not *very* slow now (although I don't have much to compare it with in my experience), but shaving a second here and there off form-open and record-browse is always welcome.

    I never thought of keeping combo-boxes unbound until needed. This really makes a big difference with form-open speed? How to do it - keep it a combo with no set properties, then set them in code when receiving focus? Or make it a textbox then change to a combo when receiving focus?

    Thanks again!
    Jerry

Posting Permissions

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