Search Filter on a DB Application

Posted on 2012-09-05
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

    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


    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

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
    It’s quite interesting for me as I worked with Excel using for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now