Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Upsizing to SQL2000 (2003)

    Hi

    We did a test of a new Access Database today, getting about half a dozen people to work on the DB at the same time, and while the DB was fast after the Records had loaded, waiting for each Form to load took a good 10-20 seconds, which seems an eternity.

    As such, we will probably *have* to go with SQL2000, so I just wanted to get some timescale sfrom ppl "in the know".

    Can anyone advise how long it takes to Upsize to SQL2000 using the Access Wizard. Is it really just a few minutes, or is it more complicated than it appears? If so, are we talking an hour, hours, a day?
    Is there a difference, or a problem, upsizing a Split Database as opposed to a single MDB file?
    What performance increase can we expect from SQL2000?

    We are on a 100Mbps Network, utilizing a Switch, so there is now slow-down with older Network Cards (I think there's only 1 or 2 PCs left that have 10Mbps cards).

    Thanks for your advice and patience.

    Brian

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

    Re: Upsizing to SQL2000 (2003)

    Is your database split into a front-end and a back-end? You will need that structure if you are contemplating moving to SQL Server. As to forms taking 10 to 20 seconds to load, depending on the nature of your design, SQL Server may not solve that problem. The upsizing process is fairly simple if your database table structure isn't too complex, and should take just a few minutes, but seting up SQL Server isn't a trivial exercise unless you already have it for other reasons. But in general, half a dozen people shouldn't be a problem with an Access database from a performance perspective - unless they are all doing heads-down data entry. Does your form use lots of combo boxes or subforms?
    Wendell

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

    Re: Upsizing to SQL2000 (2003)

    I agree with Wendell, moving to SQL Server may not solve your problem. Typically, the reasons for slow performance on forms are:
    - A form is bound to a large recordset.
    - There are many subforms.
    - There are many comboboxes with large rowsources.

    Also, make sure you have turned "Name AutoCorrect" to OFF (a big factor in form performance), and remove subdatasheets from your tables.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Upsizing to SQL2000 (2003)

    Is your database split into a front-end and a back-end?
    It was at the time I tested it (it was a test run). I'm only going to split it when we're ready to move from our existing database to the Access DB . The database needs to work across 2 locations/sites. We have a shared Active Directory, obviously, and are connected via ADSL at our end and a (slowish) leased line at the other end. When I split the database, I used a UNC path as opposed to a Mapped Drive path. We have a login script that is supposed to map the drive in the other office, but it only seems to work in our office???? That's why I used the UNC path instead of the mapped path. In my experience, opening files via UNC is slower than opening a file from a mapped drive. Is that just my imagination? If, when I split it, I split it to a Mapped Drive, would it be faster?

    <<You will need that structure if you are contemplating moving to SQL Server.>>
    Oh, I thought SQL would split it when it was upsized?

    <<As to forms taking 10 to 20 seconds to load, depending on the nature of your design, SQL Server may not solve that problem.>>
    I thought it was taking a long time to load b/c Access pulls all the records across the Network when the form loads. I thought the Jet Engine was slower than SQL's, so that's why I thought SQL would be faster *sending* the Records across the Network.

    <<The upsizing process is fairly simple if your database table structure isn't too complex, and should take just a few minutes>>
    Well, there are 13 Tables. Do you consider that complex?
    <<but seting up SQL Server isn't a trivial exercise unless you already have it for other reasons. >>
    It isn't? Well, it would be loaded from scracth on a new Server. So . . . it's not just like installing Office, huh? [img]/forums/images/smilies/sad.gif[/img]

    <<But in general, half a dozen people shouldn't be a problem with an Access database from a performance perspective - unless they are all doing heads-down data entry.>>

    Well, eventually, it would be installed on about 40 PCs, but there's never 40 people in the office at the same time (maybe 20-30 people at most at any time), and of those people that are in, they wouldn't be using it intensively--just looking up Phone Numbers, Communications, etc (mostly).

    <<Does your form use lots of combo boxes or subforms?>>
    The first form is the 'Company Form'. This contains 1 Subform (Branches); 1 Find Combo Box, and 1 other Cbo. The Branches Form contains 1 Subform (Personnel) with (again) 1 Find Combo Box, and 1 other Cbo.

    Thanks for your reply. I look forward to any further comments you might have. [img]/forums/images/smilies/smile.gif[/img]

    Mark
    <<Typically, the reasons for slow performance on forms are:>>
    << A form is bound to a large recordset>>
    Well, the Company Form would contain 2,000 records with a similar number of Branches. The Branches Form would contain about 2,000 records, as stated, with probably 9,000 Personnel.
    Do you consider that large?
    << There are many subforms>>
    As stated above, most only have 1 subform.
    <<There are many comboboxes with large rowsources>>
    Well, the FindRecord CBO would obviously be pulling about 2,000 records across the Network, right?

    <<Also, make sure you have turned "Name AutoCorrect" to OFF (a big factor in form performance), >>
    Thanks, I'll do that. [img]/forums/images/smilies/smile.gif[/img]

    << . . .and remove subdatasheets from your tables>>
    You mean open each TABLE and go to Format -> Subdatasheet -> Remove? Access adds those automatically, doesn't it? Removing this doesn't affect the database in any way, except to speed it up?

    Thank you both heaps for your thougths and feedback.

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

    Re: Upsizing to SQL2000 (2003)

    If your form is bound to a table or a query returning multiple records, it doesn't matter whether you are using SQL or Access, you are still going to pull all the records down. And just to clear-up a myth. Access doesn't always have to pull down ALL the records. It can intelligently use its indexes to start/stop reading from tables; which is why it is important to create indexes on fields frequently used as selection criteria.

    And no, turning off the subdatasheets does not affect the database in any other way.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Upsizing to SQL2000 (2003)

    <hr>The database needs to work across 2 locations/sites. We have a shared Active Directory, obviously, and are connected via ADSL at our end and a (slowish) leased line at the other end. <hr>
    If it was slow when it tested it, were you running in this configuration? If so, that would account for most of the slowness. Running a database across even a fast (1Mbit) DSL line will be fairly slow. Access databases work well across 10 or 100 Mbit LANs, but putting them on slower facilities usually is unsatisfactory. A couple of possible solutions:
    <UL><LI>Deploy the front-end to each workstation - this is probably still a long-shot, but your tables are relatively small and you don't have a great many. But you may still find this unsatisfactory.
    <LI>Use the Access replication feature to put a local copy of the back-end at each location - that should give you good performance locally. It does create some new problems however, so you need to understand the downside of replication (simultaneous updates of the same record by two separate users) - you might start with the replication tutorial on our web site.
    <LI>Consider using something like Windows Terminal Services, Citrix, or PC Anywhere and simply send keystrokes, mouse clicks and graphical updates across the slower facilities.[/list]
    Wendell

  7. #7
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Upsizing to SQL2000 (2003)

    Thank you for your considerations. I'm sure they'll help. Terminal Service might be a great idea. I'll look into the cost of that! [img]/forums/images/smilies/smile.gif[/img]

    Thanks again! [img]/forums/images/smilies/smile.gif[/img]

Posting Permissions

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