Query to find substring in table with half million rows = SLOW

[orange]

I'm missing something. The title of the post indicated a SLOW process. The keyword approach, if applicable to your situation, changes a sequential read against ~419000 records.
You should always work with normalized tables; then if necessary "de-normalize/restructure" for optimal performance.


I don't understand the need for aggregate queries when trying to find a record(s).
Minimize the size of the set of records involved and do subsequent processing if necessary. It's a bit like reading an entire recordset and then ignoring all but the 2 you need. Get the 2 records you need, then do the processing.

[keviny04]

I'm missing something. The title of the post indicated a SLOW process. The keyword approach, if applicable to your situation, changes a sequential read against ~419000 records, to an indexed search of ~3000 records.
You should always work with normalized tables; then if necessary "de-normalize/restructure" for optimal performance.
I don't understand the need for aggregate queries when trying to find a record(s).
Minimize the size of the set of records involved and do subsequent processing if necessary. It's a bit like reading an entire recordset and then ignoring all but the 2 you need. Get the 2 records you need, then do the processing.
Did you look at the code and query I returned?

Before your thoughts run on tangent any further, which I've seen you do to other posters on occasion, let me state (multiple times already) point blank what I need right now: I need to know why there is a 1.5 to 2 second slowdown in aggregate queries on a half-million-row table. Your keyboard approach, though nice, didn't answer the question. Your code and query also didn't. That is why I titled this thread as such: slow. Even though I could avoid the slowness by not using aggregate queries, I still would like to know the answer to the original question.

Just to answer one of your tangential questions: my actual aggregate query is not just to find a record. The actual query in my project is a summation query that returns records containing the substring being sought, hence the use of aggregate functions. The queries I posted in this thread were all "stripped down" versions of the real thing, made only to demonstrate the slowdown (the only subject I'm inquiring about, and nothing more). I stripped them down in order to prevent someone from noticing some tangential issues in the queries that were not relevant to my central question. Apparently, it didn't work.

[orange]

Sorry for all the tangential things like normalization and use of indexes.
I've gone back to your database to see where you have a summation. I didn't see one.
Still unclear with exactly what you are trying to do.
Perhaps you could tell us more about the summation. And show us the real query with the summation.

As I said the keyword approach may not be applicable

The keyword approach, if applicable to your situation, changes a sequential read against ~419000 records, to an indexed search of ~3000 records.

but if it is, it may get you some speed.

After looking at all of your queries, I'll offer code for 2 guesses

Group and Count for "*james*"

SELECT Table1.ContactName, Count(Table1.ContactName) AS CountOfContactName
FROM Table1
GROUP BY Table1.ContactName
HAVING (((Table1.ContactName) Like "*james*"));

Dcount without Grouping

?Dcount("contactname","table1","Contactname Like '*James*'")
 5159

Good luck with your project.