SQL filtering, which method is best?

I have a large dataset and datagrid on my asp.net page. I am going to make it so the user can filter the original dataset. What is the best way? Should I use linq t开发者_C百科o filter the dataset or continue to use sql or is there a better way using vb.net with asp.net.

---

I think the answer is as allways... "It depends"

You said you are talking about a "large dataset".

If it is really large, then you should not show it to the user. You should also consider that storing it in IIS can decrease performance. You want a small and fast page. So I think its not good to send more then 50 records per page to the user (It allways depends what a record is)

You should consider creating some SQL that will allow you to do some paging in your results, and only transmit those. If you want to try to sort it out in code, then you might end up with "lots" of data on your lient. And the main purpose of a sql server is to sort and store data. So let it do his job. You also have to consider invalidating any data that you cache, which can be a challengs all by itself. Take a look at the nice LINQ functions like Skip(30).Take(10)...

On the other hand, if you have some that that is static, then you might gain from cashing it on the client, or a webservice.

---

What is the best way?

It would be subjective to say what would be the best way. What I can say is what would be a bad way: perform the filtering by the application and not the SQL database. So if by Linq you mean Linq to Entities and your query is translated to SQL statements so that the filtering is performed by the database then it is OK. If you mean Linq to Objects then you are filtering in-memory and in order to do this it means that you have loaded the whole dataset into memory which is BAD. Don't load something into memory which is never needed/shown on the screen.

---

Well, I personally think that if you already have all the data in memory, it'd be faster to filter it in memory than doing a network access.. Therefore, you won't have the latest updates of the data, unless you refresh it...

But as Darin Dimitrov said, if you don't have all the data already, you should only use a query, requesting the necessary data.

---

Filtering by calling back to SQL could be expensive, for example auto-complete style filtering. The same effect could be done by Calling back to a web service where data is cached.

If you have a set of filters where the user clicks "Search", then a call back to the database may be reasonable to avoid caching data in the web server or even client.

If you have a busy database, then you could different results calling back so this goes back to calling a web service.

In this case, I'd consider filtering in a web service with no extra DB calls. Saying that, this could kill a web server if you have many clients. If the secondary filters reduce the dataset greatly then why cache it at all?

Unfortunately, there is no best way as such...