Slow query - Help to speed up?

[jgelpi16]

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!

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;

[orange]

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.

[jgelpi16]

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.

[NTC]

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?

[jgelpi16]

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.

[jgelpi16]

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....

[orange]

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.

[jgelpi16]

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.

[orange]

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.

[jgelpi16]

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.