Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Configuration of Split DB (AccessXP)

    This is more of a configuration question but goes something like this. I have a split database that has to be used by our employees in both our Pennsylvania office and our New York office. Both offices are connected via T1 line. New York users can get access to Pennsylvania server and vice versa. When I put the back-end of DB on the Pennsylvania server, the New York users have problems. (I use UNC path names in front-ends). Problems include timing out and/or very slow performance. Do I make two back-ends and push one to each office each week when I do my weekly updates?
    Are there some tricks to optimizing code in front-ends that makes things work faster? How would you configure such a system?

    Some info: Front-ends about 120MB, Back-end about 100MB (contains tables only)

  2. #2
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Configuration of Split DB (AccessXP)

    Are the front ends located on workstations or servers? Make sure your tables have good indexes. Also, when returning recordsets, try and limit the number of records being returned with criteria. This should improve performance greatly if you are using large recordsets. I have one multi-office application which I upsized to SQL server. This is not for the faint of heart, though. But it is something you might want to look into.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Configuration of Split DB (AccessXP)

    Thanks. Front-ends are located on workstations, mostly WindowsXP with OfficeXP. Tables are indexed but I am curious about what gets pulled to the user's front end when they open a report based on a query. Is the recordset made on the backend and then just the results sent over the lines to the front-end or do entire tables come accross for processing on the user desktop?

    Also, did SQL Server improve performance?

    Thanks,

  4. #4
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Configuration of Split DB (AccessXP)

    Yes, performance was improved with SQL server. I'm still monitoring it though and am currently researching replication.

    I'm not sure about your first question. I'll see if I can find out though.

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

    Re: Configuration of Split DB (AccessXP)

    To clarify things a bit - when you run a query in Access using .mdb tables, all the work is done in the front-end even if you are using tables linked to the backend. In other words, the Jet engine runs on your local workstation, so it has to pull each table you are working on over to the workstation. On a 100MBit LAN, no problem, but on a shared 1MBit pipe it takes a while, and if there's a fair bit of traffic you start getting timeouts and other fun things.

    SQL Server on the other hand does all of the processing on the server and simply sends the results across to the workstation. That's generally true, even if you are using ODBC linked tables - the ODBC driver converts the Jet SQL into TSQL and passes it to SQL Server. If you want to get fancier and used stored procedures and so forth, you can use PassThru queries, or you can switch to an ADP arrangement. So Yes, SQL Server does improve performance, in some cases dramatically. If you already use it in other applications and have a DBA setup, go for it. If not, there is a fairly significant learning curve you will have to surmount in order to not expose data to things like the Slammer worm that struck 10 days or so ago.

    Never the less, depending on the traffic on your T1, you may still not get adequate performance. In that case you might want to look at Windows Terminal Services or Citrix. We've found them to be pretty usable at DSL speeds, and acceptable at ISDN speeds.
    Wendell

Posting Permissions

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