Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2009

    Unanswered: Faster way to do this query?


    We have a locker / lock list. We track who has what lock and locker and the combination.

    This is easy, but here's the tricky part: 4 pairs of locks have the same combination, so they would like to keep those locks in separate locker rooms.

    The locker list looks like this:
    1 - Men's
    2 - Men's
    3 - Men's
    1 - Women's
    2 - Women's
    3 - Women's

    On the employee form there are combo boxes for both locker (LockerCombo) and lock (LockCombo).

    I made this query which gets any lock that has another lock with the same combination:

    SELECT tbl_Locks.Combination, tbl_Locks.LockNumber, tbl_Locks.LockSerial, qry_AllLocksAndLockers.LockerNumber
    FROM tbl_Locks LEFT JOIN qry_AllLocksAndLockers ON tbl_Locks.LockNumber = qry_AllLocksAndLockers.LockNumber
    WHERE (((tbl_Locks.Combination) In (SELECT [Combination] FROM [tbl_Locks] As Tmp GROUP BY [Combination] HAVING Count(*)>1 )))
    ORDER BY tbl_Locks.Combination;
    then this query for the actual list of available locks that will show up in [LockCombo].
    SELECT tbl_Locks.LockNumber, tbl_Locks.Combination, DLookUp("[LockerNumber]","[qry_LocksDuplicates]","[LockNumber] <> " & [tbl_Locks].[LockNumber] & " And [Combination] = " & [tbl_Locks].[Combination]) AS DuplicateLock, IIf([Forms]![frm_Employees]![LockerCombo] Like "*Women's*","W","M") AS AssignedLocker
    FROM tbl_Locks LEFT JOIN qry_AllLocksAndLockers ON tbl_Locks.LockNumber = qry_AllLocksAndLockers.LockNumber
    WHERE (((qry_AllLocksAndLockers.EmployeeName) Is Null));
    The query above designates either an M or W depending on what locker that person is assigned, compares locks with their duplicates, and removes from the list any lock with the same combo that is already in that locker room.

    It works fine - but it's very slow. About 15 seconds from the time you click on the combo box to the time the list populates.

    Any suggestions on how I can speed this up? I think I must be taking too many steps to do something simple.

  2. #2
    Join Date
    Nov 2011
    Query Performance Tips
    Compact Often to Update Statistics

    Compact the database often. When you compact the database, you reorganize records so that they are stored in adjacent spaces, making retrieval faster. Additionally, compacting a database updates its data statistics, which can be used by a query to run faster. You can force a query to recompile (which in turn causes it to use the latest statistics) by opening it in design view, saving it, and then running it.

    You might want to defragment your disk by using a program such as the Disk Defragmenter that is part of Windows before you compact your database. This leaves contiguous free disk space immediately after the database file. In theory, this makes future additions to the database occur faster. You might want to experiment with this on your system.
    Index Query Criteria Fields and Join Fields

    Index any fields in the query that are used to set criteria. Index the fields on both sides of a join. Alternatively, you can create a relationship between joined fields, in which case an index is automatically created.

    Search Access Help for “Index”.
    Use Identical or Compatible Datatype in Join Fields

    Fields that are joined in a query should have the same data type, or compatible data types. For example, the Long Integer data type is compatible with the AutoNumber data type.
    Limit Fields Returned by a Query

    Where possible, limit the number of fields returned by a query. This results in faster performance and reduced resource usage.
    Avoid Calculated Fields and IIF Functions

    Avoid calculated fields, or fields that use expressions in subqueries. Pay special care to avoid the use of immediate If (IIF) functions in sub-queries.
    Don't Use Non-Indexed Fields for Criteria

    Avoid using non-indexed fields or calculated fields for criteria restrictions.
    Index Sort Fields

    Index any fields you use for sorting. Be careful not to over-index.
    Use Temporary Tables to Eliminate Running the Same Queries Over and Over

    If you are processing data that is used multiple times (on multiple reports for example), it might be faster to store intermediate results in temporary tables rather than running a series of Select queries many times. Create a temporary table to store your results. Empty the table and fill it with your data by using an Append query. You can then use that table for multiple reports and forms.
    Avoid Domain Aggregate Functions on Foreign Tables

    Do not use domain aggregate functions (DLookup for example) in queries to access data from a table that is not in the query. Link to the table and set criteria accordingly, or create a separate aggregate (totals) query.
    Use Fixed Column Headings in Crosstab Queries

    Wherever possible, use fixed column headings in your crosstab queries with the PIVOT syntax.
    Use BETWEEN Rather than >= and <=

    Between lets the search engine look for values in one evaluation rather than two.
    Use Count (*) To Count Records

    If you use the Count function to calculate the number of records returned by a query, use the syntax Count(*) instead of Count([fieldname]). The Count(*) form is faster because it doesn't have to check for Null values in the specified field and won't skip records that are null.
    Compile Each Query before Delivering Your Application

    When you compact your database, its data statistics are updated. When you then run a query, these updated statistics are compiled in the query's execution plan. This sequence of events results in the fastest possible query. Before you deliver your application, compact the database, and then force each query to be recompiled. You can force a query to recompile (which in turn causes it to use the latest statistics) by opening it in design view, saving it, and then running it.
    Take Advantage of Rushmore Query Optimization

    Microsoft Jet uses Rushmore query optimization whenever possible. Rushmore is applied to queries run on native Access data, and on linked FoxPro and dBASE tables. Other linked data types do not support Rushmore optimization. To ensure that Rushmore optimizations are used, create indexes on all fields that are used to restrict a query's output. If you have queries that don't contain indexes on fields used in the query's restriction clause, Rushmore is not used.
    Link on Primary Key Indexes Whenever Possible

    To make queries run faster, you should have indexes on all fields in the query that join, restrict, or sort the data. Whenever possible, link on Primary Key fields instead of other fields. Indexes are most critical on tables with large numbers of records, so you might not see a difference on small tables. You also don't need to add secondary indexes on fields that are part of referential integrity.
    Experiment with One-to-Many Restrictions

    If you have a one-to-many join in a query with a restriction, try moving the restriction to the other side of the join. For example, if the restriction is on the many side, move it to the one side. Compare performance results for both versions, and choose the fastest one.
    De-Normalize If Necessary

    Although you should strive to normalize your data for best performance and future flexibility, consider denormalizing some of your data if you frequently run queries with joins that would benefit from such data restructuring.
    Experiment with Sub Queries Instead of Joins

    If you have a query with a join that is not performing adequately, consider replacing the join with a sub query. In some cases, the sub query might cause the overall query operation to run faster.
    Limit the Number of Fields Returned by Each Query

    Where possible, queries should use a Where clause to constrain, or limit, the number of records returned. This results in faster performance and reduced resource usage.

  3. #3
    Join Date
    Oct 2009
    I ended up finding a faster way...this takes about a second to process:

    SELECT tbl_Locks.LockNumber, tbl_Locks.Combination, IIf([Forms]![frm_Employees]![LockeRCombo] Is Null,"",IIf([Forms]![frm_Employees]![LockerCombo] Like "*Women's*","W","M")) AS AssignedLocker, qry_LocksDuplicates2.DuplicateLocker, (IIf([Forms]![frm_Employees]![LockeRCombo] Is Null,"",IIf([Forms]![frm_Employees]![LockerCombo] Like "*Women's*","W","M")))=(IIf([DuplicateLocker] Is Null,"",IIf([DuplicateLocker] Like "*Women's*","W","M"))) AS [Match]
    FROM (tbl_Locks LEFT JOIN qry_AllLocksAndLockers ON tbl_Locks.LockNumber = qry_AllLocksAndLockers.LockNumber) LEFT JOIN qry_LocksDuplicates2 ON tbl_Locks.LockNumber = qry_LocksDuplicates2.LockNumber
    WHERE (((IIf([Forms]![frm_Employees]![LockeRCombo] Is Null,"",IIf([Forms]![frm_Employees]![LockerCombo] Like "*Women's*","W","M")))="W" Or (IIf([Forms]![frm_Employees]![LockeRCombo] Is Null,"",IIf([Forms]![frm_Employees]![LockerCombo] Like "*Women's*","W","M")))="M") AND (((IIf([Forms]![frm_Employees]![LockeRCombo] Is Null,"",IIf([Forms]![frm_Employees]![LockerCombo] Like "*Women's*","W","M")))=(IIf([DuplicateLocker] Is Null,"",IIf([DuplicateLocker] Like "*Women's*","W","M"))))=0) AND ((qry_AllLocksAndLockers.EmployeeName) Is Null));

Posting Permissions

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