Search Filter on a DB Application
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
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
ASKER
LIONKING:
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?