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

    Unanswered: Access with 100mbs vs gigabit

    I've asked this question on other forums and no one seems to have any experience. Perhaps someone here does...
    All other things being equal, with an I/O intensive A2K2/A2K3 commercial application using Jet4 (and all its weaknesses):
    Win2003 Server. WinXP Workstation. Appropriately sized.
    What type of speed differences would you conjecture on a 100mbs vs a gigabit ethernet. Cards, Servers, proper Cat5E cabling. Let us assume that (within reason) cost is not an issue.
    Opinions?
    Thanks,
    Winston

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Well, here's my 2 cents, for what it is worth:

    I can say from experience that there is a noticable difference running a client-server application with the front end on the server versus on the host PC. I conclude that the weakest link is processing the front-end through the network, not the actual data transmission itself. A lot of the time Access consumes is processing the data, not pulling it.

    I guess the question is: do you expect your intended users to be pulling data in excess of 100 mbps? This is more likely to happen as the number of simultaneous users increases, and the limitation may become how many simultaneous users the server can handle.

    If money is not an object, go for the faster network - there are a lot of other reasons to do it.

  3. #3
    Join Date
    Apr 2004
    Posts
    130
    My app is broken into a FE mde on the client and a backend mdb on the server. Most tables are (obviously) on the server - nothing else is on the server. Where possible, temporary tables are located on the client. I really have no way of telling if users are pulling 100mbs - but would really like to find out if they are. The cost of a gigabit card in the server, a gigabit switch and a gigabit card in the client is really quite minor if you are buying new equipment anyway, but I would like to know in advance if this would give any actual benefit.

    There are really some other issues as well. As the horsepower of the hardware increases, the number of users supportable by a file server based db like Access increases. What started to degrade at 5-10 users two years ago, should in theory not start to degrade until 10-15 users today (using the latest and greatest hardware). Theoretically this principle should continue to apply and two years from now the envelope should stretch to 15-20 users.
    Does this conclusion make sense to you?

    I look forward to your further comments.
    Last edited by Winston; 05-21-04 at 00:52.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    any SQL server (including mySQL (which unfortunately lacks stored procedures in it's current release)) will blow the socks off giga- terra- whatever-bit/sec networks running a dumb .MDB backend...

    ...if you design the SQL to suit!


    for the price ($0.00 !!!!), please do try mySQL. it is infinitely cheaper than a single Gb/s network card, and sooooooooo much faster than anything you can fudge with network hardware.


    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Apr 2004
    Posts
    130
    Good point, but... I would have to change a lot of my code to utilize the pass thru queries. Would have to learn MySQL. A lot less headache to throw some hardware at the issue especially if my customers will pay for the hardware but not the SQL/MySQL coding. I have considered this option and keep rejecting it as too much effort that doesn't give my customers any significant application functionality benefit. The speed could be a great improvement, but I usually recommend high end server and workstations, hoping that I will get a significant benefit from the hardware.
    Opinions?
    Winston

  6. #6
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    I am in a similar boat as you winston. I have clients that wont pay me to learn another language, but most of the time will pay for the better hardware, so I understand your route.

    The difference with me is that one of my clients is actually contemplating the idea of paying me to learn the language. That is still up in the air... I would go with the hardware all the way until which time it is necessary to learn something else. That atleast is what I am doing.

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    don't learn: use ODBC and live with your app as it is! ..it works ..and it is faster than a dumb backend.

    it's not optimal until you tweak your SQL, agreed, some of your combos will take as long to fill for each individual user as they did with the .MDB, but it's scaleable: adding 50 extra users is not even a hiccup

    but you really CAN start with NO code rewrite to use ODBC with your current app (call it 5 minutes to set up your ODBC and an hour or two to setup mySQL (use innodb tables if you are using referential integrity in A and want no re-code))

    buy you a beer if 10+ users are not faster on mySQL via ODBC with your standard unmodified app.

    buy you a second beer if the next 50 users have ANY impact on performance if you have a decent server.

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Apr 2004
    Posts
    130
    I control ref integrity myself. Never trusted Access. I've looked closer at MS SQL Server than MySQL. Autonumber fields are an issue. I still like the hardware solution, but regardless, can you point me to a few good articles/books on Access and MySQL?

    I have one other question. I use A2K2 and Jet 4 for a close to "mission critical app". Regardless of the concerns with Jet4, it has been very stable for 2 years. Would you use MySQL for a "mission critical app"?
    thanks
    Winston
    Last edited by Winston; 05-21-04 at 16:22.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Winston

    I'm in no way contradicting Izy or the others (clearly the best option for you is for the client to pay for your professional development!!! You just need to justify it). However, to return to your original question I'd expect a huge pay off from an increase in bandwidth of that magnitude - I would take bandwidth over processing muscle any day. The point of Citrix, Terminal Server etc. is less about focussed horsepower (although that is significant) but more about passing as small a number of packets as possible through the bottleneck (i.e. the link from application to server). You are approaching this from the other angle but the principal of proportion of bandwidth to information exchanged is still the key issue.
    But still, the ideal has to be for someone to pay for some extra lines on your CV.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    check out who uses what on the big web sites!
    mySQL is a poor-man's solution cos it's free, not cos it's bad: i'd trust mySQL at least as much as MS-A

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Izy - your enthusiasm is infectious.

    Similarily to Winston (and probably most people - in my experience the holders of the purse strings tend to prefer tangible purchases rather than the abstract aquisition of knowledge) I am limited less by financial resources and more by having to justify my allocation of working time, however I am always interested in learning new technologies and utilising tools that will improve my effectiveness.
    However, I know nothing about mySQl - do you have any good links to dispell my ignorance?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Apr 2004
    Posts
    130
    Thanks for all the responses. (sorry for the delay, I was away for a long weekend). In summary, the consensus seems to be as follows:

    1) Gigabit Ethernet can't hurt - but no one wants to take a guess at quantifying the possible speed improvement (don't blame anyone, I don't have a clue either - far too application and environment dependant).
    2) Throwing hardware at the MS Access Speed issue is an option if the funds are available. Nobody disputed my statement that hardware P1 server 10000rpm scsi would be slower than P4 server 15000rpm scsi. The P1 world of max 5 users (eg) would be a max 10 users on a P4. Conjecture, not fact.
    3) The best speed improvement would be to convert to MSSQL or MySQL (even without optimizing queries). A real conversion with new queries would be the best approach and would probably scale the user count by an order of 10.
    thanks for all the opinions. Any more?
    Winston

  13. #13
    Join Date
    Nov 2003
    Location
    Sussex, England
    Posts
    404

    Citrix

    Someone mentioned Citrix Server.

    I spent five years at an organisation which used Citrix extensively, and my advice to any potential user is - don't go near it. The set up we had was blindingly slow. Access databases used to constantly display white screens because of the s-l-o-w access times. I even watched one secretary click on the database icon and go off to make a cup of coffee while the screen loaded. Neeldess to say data access was almost a complete waste of time on this system.

  14. #14
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Ahh, Citrix. I also work extensively with Citrix.

    We use Citrix to run resource intensive (including network traffic) operations, including heavy ODBC operations (if you want to see slow, use an Access DB heavy with ODBC). The Citrix server is a "rocket ship", sits right next to the main server and has it's own, direct connection to the main server. These "batch processes" that we run nightly go MUCH faster this way, and they are administered through Citrix.

    I wonder if Ryker's DB's had ODBC tables in them - queries against large ODBC tables can give you "the Snow Storm of Delay" (white screens) even if the DB is running on the local workstation.

    Here are the secrets to Citrix:
    1) run everything on the server
    2) make sure your server is a sporty, high performance system

    It also helps a lot if both the server and the client have high quality network/internet connections (unfortunately, my customer has Adelphia .....).

    Citrix seems to me to basically be a mechanism to run a "Windows Desktop" on a remote server. The information that Citrix is meant to transfer back and forth between the client and the server is the display and the inputs (mouse, keyboard). If you are bold (and have a fast server and broadband) you can also transmit print jobs and sounds. That's what they mean when they call it a "Thin Frame".

    Citrix sports itself as a pseudo workstation, and allows file transfers and data transfer, but try to avoid these as much as possible.

    If you must transfer lots of files between the server and the client, use FTP (much less overhead and a lot faster).

    As for running database (or any other data processing applications), it's fine IF you have the front end ON THE SERVER SIDE (as long as your server is good - see rule #2 above). If you run both the FE and the BE on the server side, and you have a sleek server, you get the advantage of a high performance machine running the application and the client side becomes nothing more than a dumb terminal displaying the information and gathering your inputs. This allows people with dinosaurs to run resource intensive applications.

    Many of the other items discussed in this thread also apply (such as structure of the DB, quality of the network, etc.). Keep in mind that the performance on the client side is limited by how good the performance of the server is.

    ... do you think Citrix will give me a commission? Probably not - I would recommend NOT using Citrix unless your needs dictate it. The old KISS rule still applies: "Keep It Small & Simple". Adding Citrix to the mix adds software, potentially hardware, and most importantly OVERHEAD.

    Bill Gates (cough, gag!) once said "Why would a computer ever need more than 256 kb?" Well, Bill, just look under the hood of Windows and you'll see why: OVERHEAD.

    And than, my firends, is my 2 cents.
    Last edited by tcace; 05-26-04 at 10:37.

  15. #15
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    I would go with SQL Server just because I think it will be much better in the long run than MySQL. (I dont know that for a fact but I am pretty sure about it.) There is a place to get SQL Server for a good price. If you email me I will let you know where it is.

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

Posting Permissions

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