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