Re: [Gnumed-devel] patient search widget timings

[Horst Herb]

Karsten Hilbert wrote:
> > > How long *does* it take to bring up a list of 200 patients ?
> >
> > Entering * or *,* takes approx 1.5 seconds to bring up list box of 
> > 5,500+patients ordered by surname inc name surname, dob, address, suburb. 
> > This is  MS2.0 table from Pracsoft, linked to my Access7.0 database. 
>
> Darn it ! :-)   But thanks anyways.  Is the database running
> on the same machine as the program you are using to send the
> query ?


Python 2.2 (#28, Dec 21 2001, 12:21:22) [MSC 32 bit (Intel)] on win32
Type "copyright", "credits" or "license" for more information.
IDLE 0.8 -- press F1 for help
>>> from pyPgSQL import PgSQL as DB
>>> db = DB.connect(database='gnumed', host='192.168.0.1', user='hherb')
>>> import time
>>> def test(n):
        t1 = time.time()
        cur = db.cursor()
	cur.execute("select id, title, firstnames, lastnames from names limit 
%d" % n)
        cur.fetchall()
	print "%d records fetched in %3.3f seconds" % (cur.rowcount, 
time.time()-t1)
        
>>> test(100)
100 records fetched in 0.060 seconds
>>> test(1000)
1000 records fetched in 0.270 seconds
>>> test(2500)
2500 records fetched in 0.500 seconds
>>> test(2500)
2500 records fetched in 0.500 seconds
>>> def test2(search):
        t1 = time.time()
        cur = db.cursor()
	cur.execute("select id, title, firstnames, lastnames from names where 
%s" % search)
        cur.fetchall()
	print "%d records fetched in %3.3f seconds" % (cur.rowcount, 
time.time()-t1)
        
>>> test2(r"lastnames like 'H%'")
232 records fetched in 0.060 seconds
>>> test2(r"lastnames ilike 'a%'")
100 records fetched in 0.000 seconds
>>> test2(r"lastnames ilike 'h%' and firstnames ilike 'a%'")
18 records fetched in 0.000 seconds

My database has 5978 records. As you can see, it fetches via network 
(100MBit, but quite busy with 5 clients currently working it during 
practice hours) 2500 records onto the client in half a second. It does a 
case insensitive search (=can't use index) across two columns in less 
than 1/1000 seconds, and a search returning 232 records including 
fetchin the records onto the client in 0.06 seconds

I have done a similar test before where we simultaneously launched the 
query from 5 clients - virtually no difference at all.

The server is a P233MHz with 256 MB of RAM on ~50% CPU load (our surgery 
file & print server and internet gateway)

I suspect in your case your server is just swapping to death b/o the 
minimalistic RAM

Horst