Results 1 to 15 of 15

Thread: Adp is so slow

  1. #1
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81

    Unanswered: Adp is so slow

    Hey,
    I am using adp appliction with sql server at backend.
    I have a form that is bound to a table. and shows the data when it loads. i means every record you wanna see.
    But its running too slow.
    actually in the row source propety i have select statement from that large table.
    Despite of being efficient this solution might seems to be slow than access.
    So plz tell me if there is anyother way to resolve this prb.

    Thanks

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Start at the server. How long does it take to execute the query you're sending through QA?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81
    Its at least three time slower than before.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Start at the server. How long does it take to execute the query you're working with if you execute it from Query Analyzer?

    QA <> Access.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81
    Well,
    from query analyzer it takes about 2 seconds but with forms its taking at least 5 secs to load.
    and when i load reports it takes 30 secs. Whether before we were having 10 sec max with access.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Can you give a more detailed overview of your architecture? Did you run QA from the same box you're running Access from? Are you doing anything unusual in your reports? How much data are we dealing with?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81
    I am dealing with more than 10000 records. and i am running access but thats local.
    but when i runs adp it fetches data from the server which is too far from my pc.
    the sole purpose of upsizing was to prevent data corruption. But it was not in my mind that it will slow down the application.

  8. #8
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Are you using passthrough queries? (where you pass the SQL through to the server?)

    Else, all 10K records get copied from the server to the client, and THEN the query gets run locally.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'm pretty sure that's not how an adp works. Try executing a query in an adp with JET-specific syntax once...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You need to find out if it's the SQL Server table(s) causing the slow performance, the form(s) design, the network itself, or a specific computer/connection. A couple of things to look at...

    If you use Query Analyzer in SQL Server and run a query, is it slow?

    Instead of loading all the records when the form first opens, try creating an unbound "search" value text/combo box and setting the record source "search" field = to the value on the form (ie. = Forms!MyFormName!MySearchValue.) Also look at how many comboboxes you have on the form. Comboboxes are form performance killers, especially if the rowsource is against the main data table and on non-indexed fields. Also watch out for dlookup expressions! (these are real performance killers!) If you want the form to load faster, try leaving the recordsource blank and setting the recordsource to the search value when selected if you can do this. If you find that this doesn't increase performance of the form loading, then you've got comboboxes or multiple subforms, etc..slowing it down. Start with a new blank form and then adding comboboxes, expressions, checking the load time after adding each combobox, expression, etc..and then finally setting the recordsource of the form, until you find the biggest performance hit.

    If worse comes to worse, you can always design unbound forms which will give you instant form opening but 10,000 records should open the form fast even if it's a bound form. I went to unbound forms when I started working with recordsets in the million size. You're working with ADP and I've only done a few of these in the past and found that using good techniques, I could get the same performance in an mdb/mde file with SQL Server linked tables.

    5 seconds is way too long in my opinion and not reflective of a 10,000 size recordset. Users will get tired of this fairly soon. I allow at the "most" 1-2 seconds for a form to load regardless of the recordset size.

    Also check your indexes and make sure you have the right field(s) indexed. Sometimes 1 non-indexed field can really slow things down. On SQL Server you can also try "rebuilding" the index. Make sure you've done a back-up of the db on SQL Server truncating the tranlog. Make tranlog backups at certain time intervals (ie. once a day or every hour.) Truncating the tranlog on backups helps. Tranlog backups also allow you to restore at a "point-in-time". Test out the performance of your tables against the forms by designing some queries (in a non-adp application). For example, if you link the SQL Server tables into an mdb and then open the table, does it open slowly or sluggish? Try it on different computers. If it's slow opening the table in an mdb, and you've eliminated computer/networking, it's most likely something with your table. I've went to the extent of designing a new table (with all the indexes) and appending all the main data to that new table.

    How's the network setup? Daisy chained hubs are extremely slow verses a switchbox! Are there slow network cards involved? Is there a big difference in speed on some computers?

    And lastly, don't load all your subform(s) when the form opens. These are also performance killers. A lot of times you can set the source object of a subform when the user clicks on a specific button.

    A couple of things to check with your reports taking 30 seconds. Again, indexing. Also, are you doing a lot of calculations on the reports? Are there a lot of dlookup expressions? Do you have multiple tables linked together in the record source of the report? For every linked relational table, it will cost you performance time. If your table structure is overly normalized, you will take performance hits when trying to put all the information together again. Double-field joins between 2 relational tables are also extremely huge performance hits but you may not be able to help this if your structure requires this and cannot be revamped.
    Last edited by pkstormy; 08-13-07 at 20:00.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    agree with most of PKStormy's comments (well if the truth be told all off....), another thing to avoid if possible are sql aggregation on the report, unless you can get all the values in one hit. putting lots of things like count(<blah>), sum(<blah>) in group footers can seriously impact performance, even with a server backend. if the group footers are simple mathmatical functions then consider doing that calculation int he reports on format/on print events instead

    also make sure that everything needed in the report detail & groupings is included in the base SQL statement.... it goes against the whole approach of OOP but a single SQL statement retreving the required data from the disparate tables that make up your schema is a heck of a lot more efficient thasn trying to populate numerous classes with different packets of data. or if you prefer not to do that then make sure you use pass through queries to retrieve each and every value.. placing the code in the relevant on format event.. it may mean you populating some blank controls on the fly.. but thats what being a developer is about.. you dont have to let Access do everything for you.

    one more thing ask you users if they are concerned about the time its taking... By all means weed out as many delays as possible, but what you regard as unacceptable time to run a report may not be to the user base. You should never leave horrible, inefficient klunky code, so do your utmost to cut down on network traffic, program execution speed, but at some stage do a trade off between the time taken to reduce these things against time available, ,and see if it is actually a problem.

    another thing you could do is take some timings and print them in the report.. or even on the debug window eg 'entered product group header at:', 'found product group header at:'....... that may indicate which processes are slowing things down.. or taking an inordinate amount of time.
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81
    Thansk guys for so much info,.
    I never thought about that many things involving in speed of processing. I think from the sql server perspective everything is good coz running queries using query analyzer is pretty fast. than doing the same by any form.
    But you right i have to take a look on everything. and i definitely will.
    and will be right back.

    Thanks all of you.

  13. #13
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    One last thing I just thought of, does your SQL Server box have adequate memory and a fast processor? Nothing worse than taking a fast mdb table and upsizing it to a slow cpu, memory critical SQL Server box which is running a bunch of other processes. Personally, I think the SQL Server box should be a dedicated box for just SQL Server dbs. You can also tell SQL Server itself how much memory to use in the options. If you've only allowed it to use a small amount of memory, performance will suffer.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  14. #14
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81
    I have recreated the form as you told me and found a lot of improvement. At least the performance is double.
    Well as far as my server is concerned. Thats right i do have adequate memory and fast processor.
    Now i m thinking about the reports to get them done.

  15. #15
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Double the performance for forms - !! Great!! Now you know there was something on the old form which was slowing things down. Look at healdem's comments again on the reports - he's got some good tips. Re-read all these posts. There are some other little tricks for speeding up reports but let's see if you can first cut the speed on them in half.

    Check your indexes on SQL Server! Index any fields you have criteria against. You can have a lot of indexes so try indexing one of the key/grouping fields and see how that helps. You'd be surprised at how much this helps.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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