Hi All,
On one of my pages I have a growing issue with performance. The grid has 72K records and comes from an SQL data source. I've done gotten rid of unneeded columns from the data and have reduced page size from 200 to 50 records. These things have speeded it up slightly.
When I do a test and return the top 100 records from the datasource the grid is rendered much faster so the issue is certainly down to the IIS - SQL connection rather than the IIS to client side of it.
With that in mind I've looked at the options, I could build an elaborate mechanism for displaying the top 100 records then passing on any filters and sort order info that the user uses however that seems like a lot of custom work for one grid.
Another option that seems to be open is using a LinqDataSource rather than a SQLDataSource. I've not used a LinqDataSource before and from my casual look at it just now can see that it will be a big change from where I am at.
1) Can I use a SP with LinqDataSource and still gain the paging benefits that it provides or does a SP essentially make a LinqDataSource run the same way that a SQLDataSource does?
2) Would I experience big improvements by using the LinqDataSource - is it worth the change?
Any advice would be very much appreciated.
Regards,
Jon
On one of my pages I have a growing issue with performance. The grid has 72K records and comes from an SQL data source. I've done gotten rid of unneeded columns from the data and have reduced page size from 200 to 50 records. These things have speeded it up slightly.
When I do a test and return the top 100 records from the datasource the grid is rendered much faster so the issue is certainly down to the IIS - SQL connection rather than the IIS to client side of it.
With that in mind I've looked at the options, I could build an elaborate mechanism for displaying the top 100 records then passing on any filters and sort order info that the user uses however that seems like a lot of custom work for one grid.
Another option that seems to be open is using a LinqDataSource rather than a SQLDataSource. I've not used a LinqDataSource before and from my casual look at it just now can see that it will be a big change from where I am at.
1) Can I use a SP with LinqDataSource and still gain the paging benefits that it provides or does a SP essentially make a LinqDataSource run the same way that a SQLDataSource does?
2) Would I experience big improvements by using the LinqDataSource - is it worth the change?
Any advice would be very much appreciated.
Regards,
Jon
6 Answers, 1 is accepted
0
Jon
Top achievements
Rank 1
Rank 1
answered on 23 Apr 2013, 08:48 PM
Further to this question I have tried something out. I stripped out all of the parameters and created a view with the data that I want. I then did a linqdatasource to that view and display that. It works very well and is damn near instant for the page views. I now have a couple more issues.
1) I use a user ID to filter the list of values in the data - basically I go off and get the permitted values then use a SQL IN to restrict to the allowed list. How can that be done in the View
2) Using parameters seems to be tricky in LINQ by comparison to SPs.
3) I believe that I answered my own question, using a SP for LINQ is self defeating as to be efficient with paging the LINQ needs to pass extra restrictive data.
4) When using filters on the radgrid it's a lot slower than without any filters, if I filter it down to say 400 records, switching pages is much slower than with 72K records. Is there something else that I need to do to boost performance.
Would it be possible for an example be posted on the demo section showing the following:
1) A RadGrid with paging and filtered powered by a parameter driven sqldatasource and stored proc.
2) The same as above but with LINQ.
I'm sure that a lot of people would find a direct example of the same data in the 2 scenarios very helpful.
Out of scope I know but how is it that we seem to be told how good it is to use central SPs in SQL yet along comes LINQ which seems to completely break the centralisation of data access?
Regards
Jon
1) I use a user ID to filter the list of values in the data - basically I go off and get the permitted values then use a SQL IN to restrict to the allowed list. How can that be done in the View
2) Using parameters seems to be tricky in LINQ by comparison to SPs.
3) I believe that I answered my own question, using a SP for LINQ is self defeating as to be efficient with paging the LINQ needs to pass extra restrictive data.
4) When using filters on the radgrid it's a lot slower than without any filters, if I filter it down to say 400 records, switching pages is much slower than with 72K records. Is there something else that I need to do to boost performance.
Would it be possible for an example be posted on the demo section showing the following:
1) A RadGrid with paging and filtered powered by a parameter driven sqldatasource and stored proc.
2) The same as above but with LINQ.
I'm sure that a lot of people would find a direct example of the same data in the 2 scenarios very helpful.
Out of scope I know but how is it that we seem to be told how good it is to use central SPs in SQL yet along comes LINQ which seems to completely break the centralisation of data access?
Regards
Jon
0
Hello Jon,
Yes, you have answered your own question - LinqDataSource works with linq queries not storec procedures. If you need a filtered view of the data retrieved just use the where clause of the data-source control (that relates to your ID filter question). Using filters on the grid should not slow down performance in the control itself but the bottleneck is most probably on the database server as the linq query in that case gets translated to more complex and cumbersome sql statements.
Greetings, Tsvetoslav
the Telerik team
Yes, you have answered your own question - LinqDataSource works with linq queries not storec procedures. If you need a filtered view of the data retrieved just use the where clause of the data-source control (that relates to your ID filter question). Using filters on the grid should not slow down performance in the control itself but the bottleneck is most probably on the database server as the linq query in that case gets translated to more complex and cumbersome sql statements.
Greetings, Tsvetoslav
the Telerik team
If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
0
Jon
Top achievements
Rank 1
Rank 1
answered on 26 Apr 2013, 11:45 AM
Hi Tsvetoslav ,
Thanks for the confirmation. I managed to get this working and it sped up one grid dramatically. Conversly on a more complex grid it actually slowed the system down. The interesting thing was that when I ran the resultant SQL directly it was fast..
Will be looking into some more over the next few weeks.
Would I be better using the Telerik object model?
Regards
Jon
Thanks for the confirmation. I managed to get this working and it sped up one grid dramatically. Conversly on a more complex grid it actually slowed the system down. The interesting thing was that when I ran the resultant SQL directly it was fast..
Will be looking into some more over the next few weeks.
Would I be better using the Telerik object model?
Regards
Jon
0
Hello Jon,
Telerik offers an object - relational mapper called OpenAccess ORM that has a control similar to LinqDataSource - OpenAccessLinqDataSource, that could be used in your scenario.
Just like LinqDataSource, it executes LINQ statements against the database and gives you the flexibility to customize those statements (the OpenAccessLinqDataSource wizard and the Expression Editor dialogue).
Generally, Telerik OpenAccess ORM helps you build and maintain the data access layer of your application. It works with a large variety of database engines (the complete list is available here), offers visual tools that allow you to customize the code that will be generated and is completely independent from the .NET UI technology you would use.
The actual performance of the application with it would depend on the specifics of the scenario and I would suggest that you test it in the examples distributed with our Samples Kit. The Data binding with OpenAccessLinqDataSource sample would give a good overview.
I hope that helps. If you have additional questions, do not hesitate to get back to us.
All the best,
Doroteya
the Telerik team
Telerik offers an object - relational mapper called OpenAccess ORM that has a control similar to LinqDataSource - OpenAccessLinqDataSource, that could be used in your scenario.
Just like LinqDataSource, it executes LINQ statements against the database and gives you the flexibility to customize those statements (the OpenAccessLinqDataSource wizard and the Expression Editor dialogue).
Generally, Telerik OpenAccess ORM helps you build and maintain the data access layer of your application. It works with a large variety of database engines (the complete list is available here), offers visual tools that allow you to customize the code that will be generated and is completely independent from the .NET UI technology you would use.
The actual performance of the application with it would depend on the specifics of the scenario and I would suggest that you test it in the examples distributed with our Samples Kit. The Data binding with OpenAccessLinqDataSource sample would give a good overview.
I hope that helps. If you have additional questions, do not hesitate to get back to us.
All the best,
Doroteya
the Telerik team
Using Encrypted Connection Strings with Telerik OpenAccess ORM. Read our latest blog article >>
0
Jon
Top achievements
Rank 1
Rank 1
answered on 01 May 2013, 12:13 PM
Hi Doroteya
Thanks for that. I've managed to get it all working just fine with LINQ but have realized that the filters don't work properly now. I've put a support request in. Initially I'd like to resolve that issue with the LINQ solution as I'm pressed for time. In the end though I will need to look at the ORM mapper for the query aspect.
Cheers
Jon
Thanks for that. I've managed to get it all working just fine with LINQ but have realized that the filters don't work properly now. I've put a support request in. Initially I'd like to resolve that issue with the LINQ solution as I'm pressed for time. In the end though I will need to look at the ORM mapper for the query aspect.
Cheers
Jon
0
Hi Jon,
Thank you for the feedback.
Once you start that task you may find the following links particularly useful:
- OpenAccess ORM Starter Series - a set of getting started videos that will help you with the basics in OpenAccess
- OpenAccess Samples Kit - the off-line resource browser that offers the implementation of more than 20 real-life scenarios (developed on both C# and VB.NET) with the help of a variety of UI technologies and a data access layer provided by OpenAccess
- Our documentation - here are the programmer's guide and the feature reference
- The KB articles and the code libraries will help you with the solutions to eventual problems you may experience
- In the forums you can contact our community
If any questions come up or you need assistance, do not hesitate to get back to us.
Doroteya
the Telerik team
Thank you for the feedback.
Once you start that task you may find the following links particularly useful:
- OpenAccess ORM Starter Series - a set of getting started videos that will help you with the basics in OpenAccess
- OpenAccess Samples Kit - the off-line resource browser that offers the implementation of more than 20 real-life scenarios (developed on both C# and VB.NET) with the help of a variety of UI technologies and a data access layer provided by OpenAccess
- Our documentation - here are the programmer's guide and the feature reference
- The KB articles and the code libraries will help you with the solutions to eventual problems you may experience
- In the forums you can contact our community
If any questions come up or you need assistance, do not hesitate to get back to us.
Regards,
Doroteya
the Telerik team
Using Encrypted Connection Strings with Telerik OpenAccess ORM. Read our latest blog article >>