1

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 to filter the dataset or continue to use sql or is there a better way using vb.net with asp.net.

MyHeadHurts
  • 1,514
  • 5
  • 36
  • 87
  • It sounds like there are additional requirements that you're not expressing here. (I cannot using paging). Why don't you tell us exactly what you want to do. – Stephanie Page Nov 16 '10 at 21:04

4 Answers4

4

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.

Heiko Hatzfeld
  • 3,197
  • 18
  • 15
  • using an IIS slows down the page? and i cannot using paging, but what if i used json or xml, because i need to perform arithmetic with the dataset, like having a running total – MyHeadHurts Nov 16 '10 at 18:18
  • 2
    Storing the data in IIS memory between request will cause your application to not become scalable, since you might need to hold data for more then 1 user at once. Make sure you loadtest your solution. And the overhead for a query is not that much. If you include running totals, then its a completely different question... You asked about filtering, which only requiers to know how many items to skip ;) – Heiko Hatzfeld Nov 16 '10 at 18:25
1

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.

Darin Dimitrov
  • 1,023,142
  • 271
  • 3,287
  • 2,928
  • so it would be better to load all the values, when the page loads. since my gridview is in an updatepanel. I could call another query and just update the gridview. – MyHeadHurts Nov 16 '10 at 18:07
  • and by linq i meant filtering the dataset using linq. – MyHeadHurts Nov 16 '10 at 18:08
  • Load all values only if you are going to show them on the screen. So you meant Linq to Objects. Well, personally I would prefer doing this by the SQL server (you could also enable caching to avoid hitting the database too much). – Darin Dimitrov Nov 16 '10 at 18:08
  • do you know how i could do it using a webservice like gbn said – MyHeadHurts Nov 16 '10 at 18:15
  • @NEWProgrammer, yes I know. There's [WCF](http://msdn.microsoft.com/en-us/netframework/aa663324.aspx) which is what I would recommend you to create a web service on the .NET platform. – Darin Dimitrov Nov 16 '10 at 18:15
0

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.

Pacane
  • 20,273
  • 18
  • 60
  • 97
0

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...

gbn
  • 422,506
  • 82
  • 585
  • 676
  • my problem is i have been trying to figure out web services in vb.net(asp.net) and i can't.. thats what i have been trying to do http://stackoverflow.com/questions/4169158/json-string-from-vb-net-side-to-asp-net – MyHeadHurts Nov 16 '10 at 18:10
  • if you could help me out with webservices it would be awesome, i even tried wcf – MyHeadHurts Nov 16 '10 at 18:11
  • Even if it's a billion rows? Size has to be a consideration in what's best but you offer answers which are impractical for large volumes of data; especially when the OP says it's a large dataset. – Stephanie Page Nov 16 '10 at 18:56
  • @Stephanie Page: offer a solution then. How would *you* do it then? – gbn Nov 16 '10 at 19:02
  • there are already good solutions on the question; Heiko's for example. – Stephanie Page Nov 16 '10 at 21:00