Search Filter on a DB Application

Posted on 2012-09-05
Medium Priority
Last Modified: 2012-09-21
I have a usercontrol with a gridview, and a textbox called search criteria, for which the TextChanged event populates the gridview.  

The tables being searched are fairly large, so the SP is set to return the top 100 rows.

My problem with this is that if you type something in, it will rerun the SP for each letter you type e.g. type in the name Pink Floyd and this would requery the DB for "P", then for "i" then for "n", etc.. , In this case requerying a total of 10 times.  
This causes the control to slow down considerably when someone types in search criteria at normal typing speed.

This automated populating functionality is already in place, and so won't be removed, so I'm looking for help on a way to reduce the calls made to the database based on the time between keypresses.  

Can anyone help me with a method to do this, that would not itself cause alot of overhead / sluggish response
Question by:prototye
LVL 13

Expert Comment

ID: 38368293
When you say "fairly large" how many rows are you talking about? Several millions?

I haven't tried this with millions of rows, but what I've done in the past is that I load a datatable with all the values and on the TextChanged event I filter "on the datatable", without having to query the server again. This has been pretty good time-wise, but I guess it depends on the amount of rows you have.

Have you tried this approach? Is it viable for you?

Author Comment

ID: 38368504

There are approx 1/2 Million records, and each row contains about 10 large fields, including pictures, and some nvarchar fields.  This would be a large amount of data to hold in memory.

I'd prefer something that requeries the database based on how quickly the user is typing
LVL 83

Accepted Solution

CodeCruiser earned 1500 total points
ID: 38370140
For starters, only do the first search after say 3 letters?

Add a timer to the form. On key press start it. In elapsed event do the search but if key is pressed again before elapsed then reset it.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question