I have a simple app, type in a surname in textfield1, using the textchanged event query postgresql database, return list of names starting with textfield1.text, store in recordset and display in listbox1
So as you type each letter, a list populates contain the names matching your search as you go.
This works great… if you are running a postgresql database locally, but over the internet, it has a pause. Not huge (maybe a second) but really interferes with the input.
Is there a way to run the query, but not wait for the result? Like google when you start typing a search, it will search as you type, but if the search doesn’t return a result by the time you type the next character, it starts the new search and discards the original search.
This way it doesn’t matter how big the database is, or how slow your connection is, it searches as you go and if it can keep up, will display results, but won’t stop the text entry.
i usually cache the data to an in memory sqllite database and then query that on the keypresses.
you have to remember to refresh your cache but that can be based on a last_updated field in the postgres table
or if its not data that updates frequently, just periodically or at program start.
it also allows you to create indexes on fields that may not be indexed on the main server, like post code or 3rd line of address etc
Andrew - I use similair instantly updated listboxes and (depending on the content of your tables) you can make quite a difference in speed by only fetching the data you absolutely need.
So in my case rather than doing
SELECT * FROM accounts WHERE...
Using
SELECT id, first_name, surname FROM accounts WHERE...
made quite a speed improvement on a table with 12,000 rows.
@Sascha S Not the same.
One is to reuse a recordset, the other is to not wait for the result of an sql query.
@Russ Lunn That may be a solution, but in some cases, absolute live data is required. (multi-site shops)
@Richard Brown Thanks, I already only fetch the data only needed to display rather than * and also apply a where clause.
It still makes for slow typing on a database over the internet.
yeah, thats the trade-off. I use SQLServer, and have a trigger on the customers/suppliers/etc tables that updates a lastupdate time
then i have a timer that runs a query that every couple of seconds for records where lastupdate > the last time i checked.
this only returns the changed rows, and i then update my in memory db accordingly, so the user rarely sees any issues.
I usually have the timer interval as a user setting, so it can be moved around from 1 second to 30 mins or so.
what if i created a non-modal window (invisible) that handled the sql query,
user types a letter, query is sent to window focus is sent back to original window and textbox. if user types a letter again, it send the query through to the window again. but the other window will still operate while the query is returning.
use a timer on original window to check for the query results.
the problem i had, which was why i use the in memory table, is that the round-trip time is variable based upon network usage at both (multiple sites) ends of the connection and the internet in general.
the users got annoyed with the lag and the fact that the lag wasn’t constant. sometimes it didnt lag, other times it was a second or more. once its in memory its consistent.
i also have one guy who has a laptop and i persist his cache to disk so he can go out and do account queries on farms.
he takes orders and then uploads them when he has wifi signal. works reasonably well.
That works in your scenario and is a great idea that i might just do if i can’t get this working.
But I’m wondering if my idea would work? I see no reason it wouldn’t?
Yes, there would still be a lag, but only of a second or 2 (in my scenario)
just not sure if the process would work. Anyone see why it would not?
I really like your idea, if absolute live data was not so important, i would go with it. I also like your persistent cache to disk to allow work outside of internet coverage. I have had that as a feature request many times, but my app has legislation behind it and i can’t allow it (has to assign a contract no on creation and no way to get what the next number in line is without a connection).
I will start testing it. If anyone can see why it wouldn’t work, please let me know. Im only fairly new to xojo, so getting this going will take me a fair bit of time (all learning is good though).
Might save myself a whole lot of trouble for nothing!
