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.
Hello slinex,
As this matter seems to be related to an existing development issue, I have linked this case to the existing work item - 114943. The next step would be for our development team to provide more information regarding this scenario.
Your support ticket regarding this matter is CAS-94207-YF6KG3. Please feel free to contninue sending us any updates through this case.
Do not hesitate to contact me if you need more information.
Hi guys,Just a quick update from the RnD team:The fact that the AutoGenerateLayouts option is set to true (which is by default) is causing the excessive data-retrieval query.So, as a workaround you can set that option to false.The RnD team have implemented a fix which will appear in the next Service Release) which triggers an additional TOP 1 (Take(1)) query instead of the current full data-retrieving one.Cheers,Borislav
Hi Borislav,
We already have that set to false and as I just checked from the sample you have on skydrive, that is also set to false.
Thanks.
I looked into the source code and got around the issue of the double querying for now.
But it looks like sorting and filtering are doing some strange things as well, see attached zip file for a screenshot of the requests that occur when sorting with a filter already in place. We filtered down 300k records down to 50 (took 5 seconds), and then did a sort which took 10 seconds to return the data. The last column is the duration in the screenshot.
Edit: After a day of trying to optimize the query in various ways, everything seems to be going smoothly even with the double querying.
Another quick update:AutoGenerateLayouts is slated to be set to FALSE by default in the next Service Release so that along the applied fix from the RnD team only one data retrieval query will be executed.The fix (and the change) will be included in the next Service Release which should be available somewhere around the middle of next month if all goes according to schedule.Thank you for helping us drill down to the bottom of the problem and for your patience - hopefully the next SR will give you exactly the performance you're looking for.Cheers,Borislav