[Gambas-user] Using a timer to speed data retrieval ( was Gambas "Best Practices")

[richard terry]

By the way, what I forgot to mention about how I did this in VB (havn't done 
this yet in gambas)

Used a timer to cancel the query until a particular count was reached. 

I set the timer interval to the users typing speed (configurable - I can touch 
type at around 80wpm, so I set the timer to I think from memory 300msec or 
less, and that way one can type continuously 
WITHOUT SENDING OFF A QUERY TO THE DATABASE EVERY KEY STROKE.

As soon as you pause typing the query gets sent off, blindingly quick results.

Also in situations (many) where you know you don't want an instring seach, and 
your field is indexed, speed is never a problem. (ie like txtUpper & %). You 
can also instruct users who really want an instring search occasionally to 
use the wild-card.

The whole of my medical records program in VB relied on 'just in time' 
delivery of result sets to popup list boxes - and on an old pentium 100 used 
to run instantly.

On postgres on my laptop celeron 1.8 it is instant, even when searching dozens 
of joined tables in a large query (could use a view of course).

BTW if I think I've mentioned it before, but to  anyone who is genuinely 
interested in using postgreql, wheras pgadmin3 is basic and I use it alot in 
linux, I also run VirtualBox in seamless mode and purchased a copy of ems 
postgresql data manager (www.sqlmanager.net), to work out and debug more 
complex functions and queries, saves hours of development time, and worth 
every cent.



Regards

Richard

On Mon, 26 May 2008 04:53:34 pm charlesg wrote:
> I too have been messing around with this. I loaded 20,000 stock records
> into sqlite3 and used my search method on the 50char max non-indexed
> description field. Bit tedious it was.
>
> Things got much snappier with the following although I agree with Stephen
> that you would have to watch performance in a large environment
> (fortunately not my problem!) because of the repeated calls to the database
> engine . By the 3rd digit, the search had caught up and displayed the
> result.
> '-------------------------------------------------------------------
> PUBLIC SUB txtDesc_KeyRelease()
>   DIM txtUpper AS String
>   lstBox.Clear
>   txtUpper = UCase(txtDesc.text)
>   sql = "select * from stmaster where st_desc like '%" & txtUpper & "%'"
>   res = conn.Exec(sql)
>   FOR EACH res
>     lstBox.Add(res!st_code & "," & res!st_desc)
>   NEXT
>  END
> '-------------------------------------------------------------------
>
> I agree with Richard that there is a need to help newcomers (like myself)
> particularly on the now non-Francophone side of the divide! I must say I
> find bits of code a great help rather than a bland description of
> procedure.