I need a SQL Query that uses COUNT, LIKE and Inner Query
I need to query an entire table and get some strong sense of how often to query, based on keystrokes into a ComboBox. Query too often, like after each keystroke, and you slow down high production users, who are trying to use the ComboBox to find a single Product Code, and do so in the least amount of wait and keystrokes.
Query infrequently and you slow them down when they made a keyboard input error OR the product code for which they are searching does not exist.
Here's what I mean...
Let's say the full Product Code is SS-SWJ0723
If we ran the query for every string 3-or longer, there is a lot of wait time...
LIKE 'SS-%'
LIKE 'SS-S%'
LIKE 'SS-SW%'
LIKE 'SS-SWJ%'
LIKE 'SS-SWJ0%'
LIKE 'SS-SWJ07%'
LIKE 'SS-SWJ072%'
LIKE 'SS-SWJ0723%'
TADA, on the final query, the ComboBox is guaranteed to have only one entry. In this example, as many as 7-queries ran, so the operator would likely be getting very frustrated.
I am considering doing a single query on keypress of the third character, then filtering the ComboBox with the list that is in memory.
So, please provide me the SQL to query that table and get a count of string by length. Would this take an infinite amount of time to run?
It's like a group by that is based upon the first X-characters.
How do I code that?
if you are interesed in stuff that BEGINS rather than contains stuff, you would probably run the query after x chars are typed and no char has been typed for x ms, but the query will be efficient. you probably should include a LIMIT in said query so you do not pull most or all of the db by accident.
ASKER
DevExpress give me a way to do this. I am just in need of that query to do a sizing of the Product Code grouping, so I can assess whether I should load the entire group, then offer instant response time.
use a limit clause in the query. simplest approach by far.
if the number of returned results matches the limit, expect there are more results.
or use a count before you run the actual query
or add SQL_CALC_FOUND_ROWS and a limit though i would not recommend that approach which adds server load for no reason.
you can probably add some caching or audit what is sent so you can fine grain the rules on the client side and avoid sending too many useless queries. it totally makes sense to have a json list of all queries that produced too many results and both expose that to the clients and check it once in a while to tune the rules better.
ASKER
Well, I need to query the value for "SS-".
There are thousands of possibilities. I need an inner query to set that value, and iterate through them all.
ABC
ACB
123
132
And so on...
500K unique product codes...
Here is a longer one...
1050110252617NSS