Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2003
    Location
    Bogota
    Posts
    67

    Unanswered: things taking way too long

    In my setup, the users have on their local drives the front-end mdb, which contains the forms, code modules, and tables (only data that doesn't change too much). Whenever they open the application, this 'stable' data (emps, products, etc..) is copied from the network to their front end, in order to make lookups and combo boxes run more quickly.

    The back-end, located on a file server, has the data that changes a lot, and is linked to the front end.

    Anyway, it takes a LONG time for things to happen. There are only 10000 or so records in the biggest back-end table, and only a few joins. I have all the indexes in place.

    When I open the case form, for instance, I use a SQL string in the form_open event to set the recorsource and bring only that record to the user's machine. I had hoped this would make things quicker, but it takes up to 20 seconds for this form to open.

    What can I do?

  2. #2
    Join Date
    Feb 2003
    Location
    Auckland, NZ
    Posts
    150
    I assume you clear the local table copy the 10000 records every time, the database opens.

    20 Seconds is not to bad considering the amount of records you are transferring over the network. (500 recorsd per second).

    Here are a few options

    1. If you are using DAO for this transfer. Consider changing to ADO(ActiveX Data Objects).

    2. Only copy the fields you use in the combo boxes , instead of the whole table.

    3. link to the backend instead of copying.
    JJ Kennedy
    Double J IT Solutions
    www.doublejit.co.nz

    VB 6, VB.NET, ASP.NET, MS SQL Server, MySQL, MS Access

  3. #3
    Join Date
    Mar 2003
    Location
    Bogota
    Posts
    67
    The problem is not so much with the opening of the application, but in opening records after the app is open. My tables look something like this:

    LOCAL:
    emps (app users - about 20 records)
    actions (for a combo box - about 25)
    action_status (to enforce
    agents (for a combo box - about 2000)

    NETWORK:
    cases (main records - about 4000)
    items (subform records - about 10000)
    contacts (subform records - about 6000)

    When I create a new case record, the form takes a good 10 or 15 seconds to open.

    There is a custom find feature I put in, which uses the id number as entered by the user to bring up the appropriate record.

    I'm a little confused about what the purpose of table relationships are. Besides cascading deletes, and making it easier for people who don't understand joins, what's the point?

  4. #4
    Join Date
    Oct 2003
    Posts
    706

    Question

    Do you happen to have combo-boxes that are linked to a data source? You could be in for a bi-i-i-g surprise if that is the case.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  5. #5
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    Database table relationships are *essential* because they offer Referential Integrity. This prevents orphans on a many side of a relationship and deletions on the one side. For example, you wouldn't want anybody to delete a Customer in a Customers table if there are matching orders in an Orders table. Similarly, you wouldn't want anybody to be able to enter a Customer code in an Orders table if that code doesn't exist in the Customers table. Referential Integrity prevents both these occurrences ever happening in the tables that have the Relationship. The validation, in addition, is done at the engine level, so is impossible for a user to get around. This is why Relationships are essential to create, because they allow us to ensure the integrity of data between tables. And in Access, as you say, they allow us to implement cascade updates and deletes as well.


    Originally posted by pd9n
    The problem is not so much with the opening of the application, but in opening records after the app is open. My tables look something like this:

    LOCAL:
    emps (app users - about 20 records)
    actions (for a combo box - about 25)
    action_status (to enforce
    agents (for a combo box - about 2000)

    NETWORK:
    cases (main records - about 4000)
    items (subform records - about 10000)
    contacts (subform records - about 6000)

    When I create a new case record, the form takes a good 10 or 15 seconds to open.

    There is a custom find feature I put in, which uses the id number as entered by the user to bring up the appropriate record.

    I'm a little confused about what the purpose of table relationships are. Besides cascading deletes, and making it easier for people who don't understand joins, what's the point?
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  6. #6
    Join Date
    Mar 2003
    Location
    Bogota
    Posts
    67

    Unhappy

    So the table relationships have no effect on retrieval speed? In other words, the relationship you establish (a join) in the table relationships window is only for referential integrity, and not for improving the speed of queries that utilize that join?

    That would mean that table relationships aren't going to help me, except for cascading deletes.

    So is 15 seconds normal to bring up one record? The IT resources of my current company (a major investment firm) are horribly inadequate. I've never seen anything like it. Techniques (like retrieving one record over a network through SQL) that I've used elsewhere, with more data and more users, are resulting in long delays.

    What are the typical causes of these bottlenecks? Am I simply at the mercy of the network here?

  7. #7
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    The joins between tables will work no faster if it is a "Default" relationship (i.e. created in the Relationships window) than those created on an "ad hoc" basis in a query. So you're right, that's not the problem. My guess is that your network is the main culprit - you could try running some packet-tracing software on it to see what's happening to your data and how long it's taking to get across the network. And, in my opinion, it's not uncommon for large companies with lots of money to be using antique and inadequate hardware. That's why I'll never get involved in building a system for a company if their hardware isn't up to it. A lot of these companies don't seem to understand the meaning of the phrase "false economy". Even fewer seem to have anybody in a senior position who understands even the basics of IT. No names though!!


    Originally posted by pd9n
    So the table relationships have no effect on retrieval speed? In other words, the relationship you establish (a join) in the table relationships window is only for referential integrity, and not for improving the speed of queries that utilize that join?

    That would mean that table relationships aren't going to help me, except for cascading deletes.

    So is 15 seconds normal to bring up one record? The IT resources of my current company (a major investment firm) are horribly inadequate. I've never seen anything like it. Techniques (like retrieving one record over a network through SQL) that I've used elsewhere, with more data and more users, are resulting in long delays.

    What are the typical causes of these bottlenecks? Am I simply at the mercy of the network here?
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  8. #8
    Join Date
    Mar 2003
    Location
    Bogota
    Posts
    67

    hourglass blues

    To answer Sundial, no, I don't. That's why I copy a select group of tables into the local front end on login, so that combo box population and employee id lookups are executed locally.

    My problem is not with the combo boxes, but with opening the form.

    There are two subforms on the form: Contacts and Actions. The form is called Cases. Apparently, when I open the main form, Access goes through the whole process of linking the subform and main forms and calling up their recordsources or something. I don't know. I think this is what is taking so long. I went in and put a msgbox "open" as the first line in my form_open event, and it takes 10 seconds for the message to appear!

    Even in design view my main form takes 12 seconds to load! My subforms, too. When I close them (even from design view) it takes 10 sometimes 20 or 30 seconds before they close! Meanwhile I'm looking at an hourglass.

    These delays only happen when my linked tables are pointing to the network. When, for the sake of development, I point them to a local mdb, it's instantaneous.

  9. #9
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    I had a similar problem. I had a form with several tabs and several subforms. It was painfully slow to move from record to record. If I deleted one of the tabs, then the form was super fast, so I concentrated my efforts on tuning that one form. I deleted one subform at a time from that tab and found which one caused the most lag. All I had to do was tune the query behind that subform. Now the entire form is much faster.

    Queries are notorious for being easy to create and difficult to tune to make them efficient. In this case I had to restrict the data available to it and its sub queries. Other solutions are to change the order of the joins so the most restrictive join is done first rather than last. Tuning is a large topic, so I have a couple books on tuning that have helped me.

  10. #10
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    Originally posted by pd9n
    So the table relationships have no effect on retrieval speed? In other words, the relationship you establish (a join) in the table relationships window is only for referential integrity, and not for improving the speed of queries that utilize that join?

    That would mean that table relationships aren't going to help me, except for cascading deletes.

    So is 15 seconds normal to bring up one record? The IT resources of my current company (a major investment firm) are horribly inadequate. I've never seen anything like it. Techniques (like retrieving one record over a network through SQL) that I've used elsewhere, with more data and more users, are resulting in long delays.

    What are the typical causes of these bottlenecks? Am I simply at the mercy of the network here?
    the basic problem is Access. Access is not a server based applicaton such as SQL server. Any data requests over a network will result in all the data being transferred to your local machine before it is processed.

    there are several techniques you could use to improve the performance:

    1) If the user is entering data only, set the form properties to DataEntry. A more complex method would be to make the form open with no record source, then use code to capture what the user has typed in and use an INSERT SQL statment to add to the relevant tables. If the user is viewing a record only retrive one record at a time. Keep the form design simple - less controls on a form can make the form faster.
    2) Reduce the number of combo boxes on a form. Use pop up forms instead if the user has to chose a value from a list.
    3) Queries can be optimised by making good use of indexes
    4) The jet database engine compiles and optimises queries the first time they are run. Run all the queries at least once before releasing the database. If you change an index or significantly chang the number of rows in a table the query should be re-compiled.
    5) Never make a join between a local and a linked table
    6) Only include required columns in a query rather then use a SELECT * FROM MyTable SQL statement
    7) Create indexes on all columns used in query joins, restriced columns (WHERE clauses) or sorted columns. If the column is part of a relationship then it is already indexed.
    8) Use primary keys instead of unique indexes if possible. Use unique instead of non-unique indexes if possible
    9) Try to avoid expressions in queries such as IIF - put these expressions on a form or report
    10) Use Count(*) instead of Count([column])
    11) Use the Between operator instead of >.<,>=,<=
    12) Normalise and de-normalise tables appropriatley. The more joins you have the higher the cost. The normalised tables maybe smaller reducing table scans and improving the speed of a query. A denormalised table will have less or no joins and could be quicker. De-normalising tables can be good for reports where the data changes infrequently. Experiment to find the best balance.
    13) Avoid outer joins if possible
    14) Use saved queries instead of SQL statements as the save query will have been optimised.
    15) Use action queries instead of looping through recordsets in VBA
    16) Use flags in DAO or ADO to open a recordset for what you are going to do with it. eg to open a recordset for adding single rows use the dbOpenTable flag (DAO) or adCmdTableDirect (ADO) to allow the additions as quickly as possible.
    17) Don't over index a table as this can have the opposite affect. If you are bulk updating or adding data to a table, it can be better to drop the indexes and then recreate them. An index only improves performance for seacrhing and sorting not inserting or updating.

    You will need to experiment with these tips to get the best performance.

  11. #11
    Join Date
    Mar 2003
    Location
    Bogota
    Posts
    67
    Thanks for the excellent suggestions. I will try them out.

Posting Permissions

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