Results 1 to 3 of 3
  1. #1
    Cfish3r is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2012
    Posts
    4

    How to speed up my Query

    Hi all,

    I have the following set of queries, and when I try to view or export the 'CrashQuery' it takes at least 5-10 minutes to process, or it simply locks up my entire computer. Is there an obvious way that I can increase the efficiency and speed this up?

    Any help is really much appreciated!

    Query Fields:

    My XeroxRequest table has roughly 1,000 records at most, and the SaData table has roughly 6,000 records.



    Please see the below screenshots for reference.

    xTrimSerial: Trim(Left([Serial Number],10))

    UsageConverted: IIf([Usage Function]="ZBWT","M",IIf([Usage Function]="ZC1M","M",IIf([Usage Function]="ZCOT","C",IIf([Usage Function]="ZC2T","C",IIf([Usage Function]="ZC3T","C",IIf([Usage Function]="ZC3M","E",""))))))

    FDate: Replace([Reading Date],"/",".")

    TrimSerial: Trim(Left([Serial No],10))

    Meter2match: IIf([Stock meter desc]="Colour","C",IIf([Stock meter desc]="MONO","M",IIf([Stock meter desc]="Black & White","M",IIf([Stock meter desc]="EXPRESSIVE","E",IIf([Stock meter desc]="expressive colour","E","N")))))

    Expr1: Current meter Reading

    Screenshots for relations etc.

    Click image for larger version. 

Name:	Xerox-Screen.png 
Views:	3 
Size:	15.8 KB 
ID:	9536

    Click image for larger version. 

Name:	SA-Screen.png 
Views:	4 
Size:	18.0 KB 
ID:	9537


    Click image for larger version. 

Name:	Crash-Screen.jpg 
Views:	6 
Size:	66.9 KB 
ID:	9538

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Must be something about the table joins because nothing unusual about the nested IIf expressions.

    UsageConverted and Meter2match fields don't sound like same data but if you say they are then must accept.

    Why replace / with . in date value?

    Could have 'lookup' tables for the Usage Function and Stock meter desc codes. Maybe table joins will be faster than the nested IIf expressions.

    Sometimes an alternative to nested IIf is Switch() or Choose() function.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Cfish3r is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2012
    Posts
    4
    Well, running the first two queries takes little to no time whatsoever, it is only the crash query that takes time.
    I will try using a lookup table however, since that does sound like a cleaner method.

    I would love to provide the database for analysis, but virtually all the data in it is fairly confidential.

    The reason I change the / to . in the date function, is because the report that the query produces, is uploaded to website that then processes it and imports it to another database which requires it explicitly in that format.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. increase running query speed
    By afshin in forum Queries
    Replies: 1
    Last Post: 10-15-2011, 12:36 PM
  2. Slow query - Help to speed up?
    By jgelpi16 in forum Queries
    Replies: 9
    Last Post: 05-26-2011, 01:23 PM
  3. Improve query speed
    By FMJO in forum Queries
    Replies: 3
    Last Post: 02-10-2011, 08:37 AM
  4. Replies: 0
    Last Post: 11-15-2010, 09:21 PM
  5. Query speed issues
    By thart21 in forum Queries
    Replies: 2
    Last Post: 04-07-2010, 05:16 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums