Results 1 to 10 of 10
  1. #1
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544

    Question Slow query - Help to speed up?

    I have the below query running and it is taking forever. I'm not quite sure why as my other queries run with reasonable speed, so I cannot assume it is a network issue. [LAST_NAME] & [EMPLOYEE_NUMBER] are indexed. Any tips on speeding up this query? The table does have 945k records in it and is housed in a back end DB. I realize that is a lot of records, but this seems to be running abnormally slow. Here is why I ask...I have a form that provides a search function which is based on this query. The query is not called upon until the user needs to search for an employee. However, for some reason unknown to me the query runs every time the form is opened. I DO NOT have the query running at the open/load of the form. The only time the query is to run is when the user clicks on a search button. Can anyone explain why the query runs on start-up anyway? Any way to fix/work around this? Thanks in advance!



    Code:
    SELECT tblSearchSource.LAST_NAME, tblSearchSource.FIRST_NAME, tblSearchSource.EMPLOYEE_NUMBER, tblSearchSource.STORE, [FIRST_NAME] & " " & [LAST_NAME] & " " & [EMPLOYEE_NUMBER] & " " & [STORE] AS Searchable, StrConv([FIRST_NAME],3) & " " & StrConv([LAST_NAME],3) & ", Store # " & [STORE] AS Employee
    FROM tblSearchSource
    GROUP BY tblSearchSource.LAST_NAME, tblSearchSource.FIRST_NAME, tblSearchSource.EMPLOYEE_NUMBER, tblSearchSource.STORE
    ORDER BY tblSearchSource.LAST_NAME;

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You say it only runs when user clicks the search button. There does not appear to be anyway to limit the query to only records with a certain lastname or some other search criteria. It looks like the query runs against the whole table. The StrConv([FIRST_NAME],3) and Last_Name functions will run against all 954K records and they will not use the index.

    Maybe I'm missing the intent, but usually with a search, you would put in some criteria and then limit the search based on the criteria. And then (possibly) open another query to get all the details and string manipulations -so it's only working on a record or 2 (not 954 K).

    Just an initial view, hope it's useful.

    You might want to put a few debug.prints in some of the event code to see if anything "unexpected" is actually occurring.
    Wouldn't be the first time, some logic is not as expected.
    Good luck.

  3. #3
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544
    Quote Originally Posted by orange View Post
    You say it only runs when user clicks the search button. There does not appear to be anyway to limit the query to only records with a certain lastname or some other search criteria. It looks like the query runs against the whole table. The StrConv([FIRST_NAME],3) and Last_Name functions will run against all 954K records and they will not use the index.
    I did not explain the intent very well. Here is the intent of the query...

    I want the user to be able to search for several different parameters (the fields listed in the [SEARCHABLE] field. So if a user types in "Smith" any employee with "smith" in their first or last name will be displayed in a list box below the "search" text box. So I do not want to limit the criteria in the query, rather allow the user to search all records within the query. I suppose I could run an append query with my update process, and just append all records in this query to a table, that way the user would be pinging off a table rather than a query.

  4. #4
    NTC is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Posts
    2,392
    appending to a table is not generally a good nor faster approach to this situation.

    in your original post you wonder why the query is running when the form opens. this should be resolved. do you have a combobox in the header?

    also - to help chop this issue into digestible chunks; open the slow query as a query only - not as part of the form at all. is it extremely slow still?

  5. #5
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544
    Quote Originally Posted by NTC View Post
    appending to a table is not generally a good nor faster approach to this situation.

    in your original post you wonder why the query is running when the form opens. this should be resolved. do you have a combobox in the header?

    also - to help chop this issue into digestible chunks; open the slow query as a query only - not as part of the form at all. is it extremely slow still?
    No combo boxes in the entire form. I have a text box where the user types their criteria. I have a list box where the search results are populated. I have several more text boxes that display results for the specific employee...

    -Yes, it is extremely slow by itself.

  6. #6
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544
    So I've gone a bit further....I don't believe it is this query from my first post that is slowing it down. I removed all associations to the query from the form and it is still lagging...I also placed a "stop" at the beginning of my minimal code that I do have running on the open of the form (setting some text boxes to null, etc.). The "query" is running before it his the open procedure. What the hell is going on?!? This form doesn't even have a record source! I am assigning a record source upon the click of a button....

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Possibly something in acc2010?

    I'm still a little confused on your intent.
    So if a user types in "Smith" any employee with "smith" in their first or last name will be displayed in a list box below the "search" text box.
    Types Smith where? Do you really test for exact match?

    If this was on a form in a textbox called WHO , the query would be

    SELECT A.FirstName, A.LastName, A.CompId
    FROM tblSearch AS A
    WHERE (((A.FirstName)=[Forms]![frmSearch]![txtWho])) OR (((A.LastName)=[Forms]![frmSearch]![txtWho]))

    You wouldn't be searching for EmployeeNumber or Store in the Names.

  8. #8
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544
    The query is no longer the issue. When the form opens before any code executes, before it even gets to the "Open" event procedure I get the "Running Query" progress bar in the bottom right-hand corner of the screen. I have stepped through my code line for line and it happens after the "DoComd.OpenForm..." and before my first line of code in the "Open" event procedure. I have tried moving everything to a new DB shell, I have ran compact and repair multiple times. My front end is only 2.5MB. I am at a loss as to why this phantom query is running. Anyone have any ideas? Maybe it is something Access 2010 does, although I have been working with it for several months and never encountered anything like this.

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Do you have any autoexec?
    Can you click a button or open a specific form to "start the whole thing"?
    I don't have 2010, so can not try anything.

    When you did the C&R in a new shell, same thing? No change?
    Can you set up a dummy form with button to Start everything?

    On the Form Loads, Opens use a Debug.print " Loading Form XYZ" etc
    to see if there is something????
    bizarre.

  10. #10
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544
    Solved problem.....I had the recordsource of my list box set to the query that was taking so long. I had neglected to look there because I was assigning recordsource in my code as well. Once I removed the "default" recordsource from the list box it runs like a dream. Thanks for all your input and suggestions.

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

Similar Threads

  1. Improve query speed
    By FMJO in forum Queries
    Replies: 3
    Last Post: 02-10-2011, 08:37 AM
  2. Replies: 0
    Last Post: 11-15-2010, 09:21 PM
  3. Slow Runtimes any ideas to speed up process?
    By salisbut in forum Access
    Replies: 9
    Last Post: 09-16-2010, 12:14 PM
  4. Query speed issues
    By thart21 in forum Queries
    Replies: 2
    Last Post: 04-07-2010, 05:16 PM
  5. Replies: 0
    Last Post: 02-09-2007, 09:20 AM

Tags for this Thread

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