Searching a database/dataset

DoJa

Active member
Joined
Mar 23, 2014
Messages
33
Programming Experience
1-3
I need to create a page which allows the user to search through a database table and return any records which match the keyword entered.

A number of websites I use have a very elegant search function of this nature where it will search any matching surname or forename without needing to specify which one you are looking for and the search is updated in real time each time you add or remove a letter to the search box.

I know mostly what I would need to do to achieve this, I'm just wondering the best way to approach it ... and whether my searches should query the database each time or if I should just pull the entire table into a dataset and then do the search operations on that instead. I imagine the latter might allow for faster searching, though would it still be a wise approach if the db table contained thousands rather than hundreds of users?
 

wim sturkenboom

Well-known member
Joined
Aug 6, 2014
Messages
85
Location
Roodepoort, South Africa
Programming Experience
10+
Your search might be faster, but by pulling thousands or millions of records from the database, you more than likely will loose. Database engines are quite good at searches, specially when you have proper indexing on the columns that matter.
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,219
Location
Sydney, Australia
Programming Experience
10+
Those web sites will undoubtedly be searching the database each time. We have a few applications that do that sort of thing at my office and we use a jQuery plugin on a text box that allows us to make an AJAX call to populate a drop-down list of matching records. How you match is completely up to you and we query the database each time. You're going to have to search the data somewhere and a database is optimised for that sort of thing specifically and will be faster than your C# code at doing it. You're only going to be getting a small subset of the data each time so the time saved by searching at the database is likely going to outweigh time lost by possibly retrieving the same data multiple times.
 

DoJa

Active member
Joined
Mar 23, 2014
Messages
33
Programming Experience
1-3
**Edit - Sorry jmcilhinney, looks like you posted whilst i was writing a response, not sure if what I wrote is still relevant as I was replying to wim ** :)

They are indeed, but over a low bandwidth mobile connection it might be too noticeable. An app might be more useable if it took 30s to download at start-up but then was near instant during use rather than taking 5 seconds for each search. I suppose the size of the dbtable is a big factor here and the question is how big is too big. Is it a case of low thousands is ok to store locally but tens of thousands is better to query the db directly each time?
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,219
Location
Sydney, Australia
Programming Experience
10+
A simple rule is, if in doubt, test. You can easily create a simple app to test just this functionality and then see how it performs. I wouldn't even be storing low thousands on a mobile device but you should check it out for yourself.
 

DoJa

Active member
Joined
Mar 23, 2014
Messages
33
Programming Experience
1-3
Fair point, but its nice to get the opinions of others to make a start in the right direction. I'll try the direct db search first and if that is too slow, attempt the alternative. Mobile device in question will be a win8 pro laptop rather than a phone so processing power will not be an issue. As most places we work from don't have accessible wifi, connections are through dongles or tethered phones which can be slow outside of major towns and cities.
 
Top Bottom