DB performance musings, FAYT, John Big Booty vs MajP

[GK in the UK]

I've been using this lookup routine in my application: https://access-programmers.co.uk/forums/showthread.php?t=188663

I've just been having a closer look at MajP FAYT which has some nice additional features. I commented out three lines of code in the class, so that when the user enters a partial search the listbox opens up pre-filtered to the search text. It doesn't seem to have broken it. This is how I tweaked the JBB routine to work. So, on the main form, product field, user enters 'Widgets' <enter> and the search form opens with blue widgets, red widgets, etc. The search box contains "Widgets" and the cursor is placed ready to receive further keystrokes, it's kind of seamless.

I may be over-thinking this (I usually do) but it seems to me:

John Big Booty passes the search string into the query on first use so the db (query) returns only Widget records. But, on every keypress, it requeries the db for the new search results.
MajP queries the db for the entire table once only (even if I pre-filter theTextBox as far as I can see) and filters the display results on each keypress. So one DB lookup only, albeit, potentially, a big one.

Just interested in commentary before I commit to changing over, for client-server usage. Network traffic kind of thing (Did I say I might be over-thinking it?)

 

[CJ_London]

think it depends on the number of records - but I use a method where I do not fetch any records until at least one character has been entered, and once populated, use the filter. Gets complicated if the user types 'ABC', then deletes the C after data has been returned, but all manageable.

simplistically, 10000 alpha records filtered for just one letter would reduce the number of records returned to around 400.

Not looked at your options in detail but I do not normally include an initial * - using it means indexes cannot be used so performance is affected because of the consequent requirement for a sequential search.

However I do train users how to use an initial * when required

 

[MajP]

I have mentioned this previously in other threads and the code I wrote is for ease of use and utter flexibility, it is highly inefficient. It is really meant for local databases (not pulling from a non Access backend) with less than 100k records. But with that said, I have tested up to 80k without any really noticeable issues so for that average Access user the efficiency question is not really an issue.

MajP queries the db for the entire table once only (even if I pre-filter theTextBox as far as I can see) and filters the display results on each keypress. So one DB lookup only, albeit, potentially, a big one.

This is not quite right, and it is actually more inefficient than that.
I pull the initial recordset which is the whole recordset built on the rowsource. Then each time I create a new recordset by first applying a filter to the recordset and opening a new recordset based on the initial recordset. This is way more inefficient than just creating a new sql string and requerying.

So the question may be why do it this way. The reason is simplicity and flexibility for the user. I can give you all the features of my FAYT with is well beyond the link shown, without any knowledge of the SQL string. So the user does not have to write any complex SQL code in vba. This allows for very complex SQLs as the row source. The user only has to write one line of code

faytProducts.InitalizeFilterCombo Me.cmbProducts, "ProductName", anywhereinstring, True

I have seen other examples where the authors try to get around this by using a subquery
Select * from (me.Somecombo.rowsource) where somefield like ...
Anything in Access using a subquery is equally inefficient.

If I was going to rewrite this for efficiency, but still try to keep it simple for the user I would do the following.
1. The user will have to pass the rowsource as a string during the initialize event. This is much more complex already since it requires a user to know how to do this and type a potentially long string into vba. But this way you do not load the rowsource until some letter (or minimum set of letters) is typed.
2. The complex part is then applying a filter without using my recordset technique or an subquery. This will require you to parse the rowsource to see if there is an existing WHERE statement and then add to that. This could get really complicated if the WHERE has lots of bracketing (). Imagine the original SQL is
Select * from addresses where AddressType = 'Client' and Status = 'Active' Order BY ClientName
Now you would have to figure out where to add the like filter
Select * from addresses where AddressType = 'Client' and Status = 'Active' AND ClientName LIKE '*ABC*' Order BY ClientName

In general any find as you type is inefficient, because you are querying after every keystroke. If you want to be efficient provide a filter button that the user has to hit, that way they can control how "efficient" they are. That way if you have a list of a million records and they type "W" and hit search the user will sit there and wait as it returns a few hundred thousand records.. Pretty soon they will not hit search until typing "Widget".