Slow search

[Guest]

Hi, I'm pairing a text box and list box and using incremental search to fill
the list box based upon whats being typed in the text box, unfortunatly there
are now a large number of records and users accessing the data. As a
consequene this routine has now become unbearably slow, does anyone have any
ideas on how to speed it up or change it.

I'm using Access 2003, and the table is sat on SQL 2000, im searching on a
surname field which has a clustered index on it created within SQL.

Any help would be greatly appreciated.

Ally.

 

[Gijs Beukenoot]

From Ally H. :

Hi, I'm pairing a text box and list box and using incremental search to fill
the list box based upon whats being typed in the text box, unfortunatly there
are now a large number of records and users accessing the data. As a
consequene this routine has now become unbearably slow, does anyone have any
ideas on how to speed it up or change it.

I'm using Access 2003, and the table is sat on SQL 2000, im searching on a
surname field which has a clustered index on it created within SQL.

Any help would be greatly appreciated.

Ally.

Why not use a combo-box (having this behavious 'out of the box')? OK,
it has to be populated with the recordset first.

How do you search the database?

Speeding up: Create a listbox (for example), fill it with the whole
recordset you want to search (ID and surname in this case) and search
the listbox instead of the database.

 

[Guest]

I've now changed it to a combo-box and its so much faster, thanks. It
doesn't look as "nice" as the method I was using but, i'm sure the users will
prefer the increase in speed, thanks again.

Ally.

 

[Albert D.Kallal]

Hi, I'm pairing a text box and list box and using incremental search to
fill
the list box based upon whats being typed in the text box, unfortunatly
there
are now a large number of records and users accessing the data. As a
consequene this routine has now become unbearably slow, does anyone have
any
ideas on how to speed it up or change it.

You know what, all that "filling" while you type is arguably not that great
from a users point of view. They start typing, and screen starts
flickering, as they type. Each keystroke whacks the serer with a query. In
fact, I don't even risk this behaviors when I am NOT using sql server.

I would suggest you modify this approach *slightly*.

Present the user with a text box, and have them type a few chars and THEN
HIT enter key. So, to search for smith, I would type in smi, and then enter.

I use the above for small files in the 100,000 record range, and I find
response time instant. (mind you, I am not using sql server, and only got 5
users searching at the same time - my point here is that with sql sever, you
got a huge powerful engine here, and can handle far many users and a far
larger file size then the small tables of 100,000 records that I use).

Be your data on sql server, or a simple file share with JET only,
by changing the search to have the user hit enter, then
performance will instant...

Here is some screen shots of what I mean:

http://www.members.shaw.ca/AlbertKallal/Search/index.html

 

[Guest]

Thanks for that I'll probably use that method in the future with larger data
sets not on SQL

Ally.