Using Jquery 12.1 and MVC 3 what's the best way to handle large datasets(100k+)?
We're using Linq to Entity framework and I'm selecting everything into a viewmodel
IQueryable<ViewModel> vm = from products in context.vw_Products select products;
and passing it into the view from our Index Action.
I have remote paging enabled so it doesn't load the entire dataset all at once and the pagingGetData action which returns the same dataset as before.
But this seems to be significantly slower than all the other grids, taking anywhere from 5 to 12 seconds just to finish rendering the grid.
Is there a way to set the grid model's total count and set how many pages it will be so I can utilize the int page and int pagesize I pass into the pagingGetData action? Or what is the best way to do this?
Hi, slinex,
The best way is to enable Continuous virtualization for the grid.
More information about can read here.
Regards,
Stanimir Todorov
That doesn't seem to quite make sense to have a large continuous grid when there is the option of paging.
Thanks for the vie and controller code!It appears that you have them set up correctly so I'd really like to investigate this scenario in a real MVC project. And so I did :)You can get the project from my SkyDrive account:https://skydrive.live.com/redir?resid=E4CF03E23FE33AFE!127The SlinexDB I created has only one table that is as close as possible to your CallHistory in terms of columns and number of records (I generated 180 000).I did a couple of slight changes to the controller and view, but nothing major really.The outcome, even at the first run of the project was excellent - less than 5 seconds (grid and its data rendering) and less than 3 after a page refresh.Thus I'm assuming that you are pre-populating the IQueryable that you pass to the grid and so the usefulness of the server-side paging is diminished by that (I could be wrong however).Please take a look at the project for yourself and let me know if you spot anything that differs from your scenario.Cheers,Borislav
Thanks for the sample solution file. I see the main difference is that you sorted with the primary key. I did that and the performance did go from 8-9 seconds to 1.5-2seconds for the data to render.
So I have tried to build indexes for the three fields i'm sorting, however that is still taking 5-6seconds to render. I actually did have it prepopulating the iqueryable before since I had a nullable date field and the grid doesn't like nullable datetime fields but that is another story. Prepopulating takes 3-5seconds to load the data so I guess that is the solution I'll go with for the time being. But it looks like I can speed up the other grids at least.
Thanks for your help.
slinex said:I see the main difference is that you sorted with the primary key
slinex said:I actually did have it prepopulating the iqueryable before since I had a nullable date field and the grid doesn't like nullable datetime fields but that is another story.
Sorry for not getting back on this issue, we were busy getting everything ready for a live release.
After some investigating, it seems like it works with the nullable datetime fields I have in the model now. I guess I should have checked on that from the 11.2 -> 12.1 upgrade but this is great since the filter now recognizes it as a date field instead of a string.
I'm not really sure why it's faster to prepopulate it, but it's equivalent in speed to having it ordered by the primary key, 3-5 seconds for 220k records.
But if I have the 3 columns that are required in the order by, it takes 3 times as long. I have already placed indexes on the 2 date fields and bit field I'm ordering it by and it is taking that long.
Hi,I'm afraid that the strangeness you're encountering with the better performance out of data pre-populating Vs. ordering indexed data might be caused by something specific to your scenario.I'm an ex-DB-guy so I've seen such weird performance quirks (especially when frameworks are involved) so my personal advice would be to stick with whatever's working out for you.If you run into any trouble with our controls in the future, don't hesitate to inform us.Cheers,Borislav
Great Scott!You're right - there is a duplicate query according to the profiler.(SQL Profiler FTW!) I've logged development issue with internal ID #114943 to our RnD team so they can investigate it.I'll ask my colleagues from the Developer Support team to create a public case and to associate it with the development issue and this forum thread so you can be notified when the issue is resolved.(this will probably mean next month's Service Release, but let's not get ahead of ourselves :))
Hi Borislav,
We just had some time to go over this issue again and we were using sql profiler to analyze what the paging action was doing and noticed that it was calling the select top 50 query twice. Is that happening in your sample as well if you run it through sql profiler?
Thanks,