Fastest way to search list?

[vanvb]

I have a number of items(800-1000) in a SQL Server database and currently I have my searching arrangement as follows. I have 5 radio buttons that the user selects and then a text box and a listbox. The user starts to type into the text box and as the user types, the change event searches the database for matching items to what the user has entered so far. The radio buttons are to allow the user to select what field to search by. This all works fine, but the search is a bit slower that I would like. What is happening now is that as the user types, I do a select statement to fill a recordset and then populate the listbox from the resulting recordset.

My question then is: Is there a faster way to do this?
Is it faster to do a filter of some sort? My guess would be that I would create a recordset based on the search field, then filter the recordset as the user types rather than connect to the database each time.
Is there a control that I am not aware of that does this sort of thing faster than using recordsets?

Any thoughts on how to improve the speed would be helpfull.
Thanks

 

[nickske]

There are controls that support database connections.
I think that they are faster than the code that you are using.

You can find it at project/components/Microsoft Datalist Controls 6.0 (VB 6.0)

You have to set the datasource -, datafield - , rowsource - and boundcolumn - properties.

then you can change the datasource en refresh the datalist.

I think that this would be a faster way then the one you are using.

 

[KCI]

vanvb,

Your guess is correct. If you can avoid populating a recordset over and over with each keystroke you will save tons of work for SQL server, the client, and your network.

Set the recordset to all records then set the filter to the typed text with each keystroke (narrowing the results). nickske's idea of using a bound control would probably be a good option for the listbox.

You sould create Views for each possible recordset (one for each radio button option) and populate the recordset from the views. Views like stored procedures are pre-compiles (and sometimes already in memory waiting depending on there frequency of use and you available memory on the SQL server). The more complex your queries the more benefit you will see.

You should also use stored procedures. This allows SQL to pre-compile the SQL statement and set up an execution plan, so you save time every time you call the procedure.

Good luck,
KCI ~

 

[vanvb]

KCI:
I have just finished changing the search code around so that it works using filters and for some reason, this actually runs slower that the way that I was doing it. I don't see why this would be the case because the filters work with data on the local machine, but the only thing I can think of is that the SQL Server machine must be running fairly quick and our network load is also not too bad. That being the case, the help for filters does say that a SQL statement would be quicker...so I guess the speed of the SQl statement is still effective even over the network...any other thoughts?
I will give stored procedures and views a try when our database guy gets back next week.

Nickske:
Same as above. I tried using an adodc control, then bound it to my listbox, and still didn't get great performance...maybe I should just leave well enough alone and stick with my original plan.

Let me know if you or anyone else have any other ideas.

Thanks!

 

[lalee5]

Can you share your code to search base on what the user type so far?


I also need help with the same thing.

Thanks


Kay