[RESOLVED] Search/Filter GridView - SQL Stored Proc or DataTable (Efficiency)?

I have a GridView containing Employee data and want to be able to search/filter the results.

What is the most efficient way to do this out of the options below (or maybe another option)?

1. SQL Stored Proc with various Parameters (EmployeeID, FirstName, LastName, Manager, etc) meaning that only matched results are returned to the GridView.

2. DataTable containing the entire Employee dataset (approx 3,000 employees) from a simple SQL Stored Proc that returns all employee records. Handle the filtering of the DataTable in code behind.

My gut feeling is that option 1 would be more efficient (in a way) because you are only passing a limited number of employee records from the [seperate] database server to the app/web server. Also, it means that large DataTables will not be held in memory.

On the other hand, option 1 would potentially result in many more calls to the database server, whereas option 2 would be a one time call.

I would still prefer option 1... since it will be clean, more maintainable and typical search operation is generally not invoked very repetadely

also, if number of users grows in future then, for search operation by every user, it will store data in server memory... so, not a good approach in terms of scalability

hope this helps...