How to tell the datacontext to load relations every time you load the main entity:
public static MyDataContext CreateDataContext() {
var datacontext = new MyDataContext();
datacontext.CommandTimeout = 120;
var dlo = new DataLoadOptions();
dlo.LoadWith<Order>(a => a.Rows);
dlo.LoadWith<Order>(a => a.Company);
datacontext.LoadOptions = dlo;
return datacontext;
}
and that it is better to return a IQueryable than a List.
public static IQueryable<Order> FindAll()
public static List<Order> FindAll_SlowVersion()
Have a look in HomeController and you will see.
Please bear in mind that we use Linq2Sql in lightweight projects. Normally you will not find static methods or storing the data context in the HttpContext, but it works well and cuts the development time a lot.
Sooner or later I think that you should move to nHibernate…
choroomi
Member
33 Points
31 Posts
Performance and security comparison -- Is Stored Procedure really faster than LINQ
Jun 30, 2012 06:26 AM|LINK
Hi,
I've built an ASP.NET Webforms application using Linq to Sql data context.
I've got so many complicated linq queries all around my pages and its just working fine.
But my users and data capacity are growing so fast and queries are getting slow. :(
I was wondering if I used native Stored Procedures in database and just called them from my pages, would it be faster and more secure?
I dont care how extra development i have to do to transform all queries from linq to SPs. I just want it to be fast.
I'll have at least 100,000 users and maybe 1,000,000 records yearly.
Vanda Developers Co.
ignatandrei
All-Star
137682 Points
22147 Posts
Moderator
MVP
Re: Performance and security comparison -- Is Stored Procedure really faster than LINQ
Jun 30, 2012 02:16 PM|LINK
Not necessarly faster. More, you should think of making an archive of oldest records.
rickevry
Member
386 Points
88 Posts
Re: Performance and security comparison -- Is Stored Procedure really faster than LINQ
Jun 30, 2012 03:12 PM|LINK
Stored procedures will not be faster, but it will force you to write better sql.
When you use an ORM-tool, you will usually get unnecessary queries because it is more difficult to get correct joins between tables.
I would do the following:
/ Rickard
choroomi
Member
33 Points
31 Posts
Re: Performance and security comparison -- Is Stored Procedure really faster than LINQ
Jun 30, 2012 04:13 PM|LINK
Thanks for reply;
I'm not having a problem right now but I think i'll have some when my db gets bigger.
I already have indexes.
And I did not understand the two last suggestions :)
whould u explain'em to me in more details, please?
Regards,
Amin Choroomi
Vanda Developers Co.
rickevry
Member
386 Points
88 Posts
Re: Performance and security comparison -- Is Stored Procedure really faster than LINQ
Jun 30, 2012 08:01 PM|LINK
Hi,
I will send you some sample code. I just need to know how if you have one-to-one or one-to-many relations in your code.
/ Rickard
choroomi
Member
33 Points
31 Posts
Re: Performance and security comparison -- Is Stored Procedure really faster than LINQ
Jul 01, 2012 05:07 AM|LINK
Hi,
I appreciate your consideration.
Yes I do have one-to-one and one-to-many relations in my code.
Regards,
Amin
Vanda Developers Co.
rickevry
Member
386 Points
88 Posts
Re: Performance and security comparison -- Is Stored Procedure really faster than LINQ
Jul 01, 2012 10:00 AM|LINK
Hi,
Have a look at this example project:
https://docs.google.com/open?id=0B1B3sE8vLc7CMjQtVUktX0J1eWM
It will show you two important things:
How to tell the datacontext to load relations every time you load the main entity:
public static MyDataContext CreateDataContext() {
var datacontext = new MyDataContext();
datacontext.CommandTimeout = 120;
var dlo = new DataLoadOptions();
dlo.LoadWith<Order>(a => a.Rows);
dlo.LoadWith<Order>(a => a.Company);
datacontext.LoadOptions = dlo;
return datacontext;
}
and that it is better to return a IQueryable than a List.
public static IQueryable<Order> FindAll()
public static List<Order> FindAll_SlowVersion()
Have a look in HomeController and you will see.
Please bear in mind that we use Linq2Sql in lightweight projects. Normally you will not find static methods or storing the data context in the HttpContext, but it works well and cuts the development time a lot.
Sooner or later I think that you should move to nHibernate…
/ Rick
choroomi
Member
33 Points
31 Posts
Re: Performance and security comparison -- Is Stored Procedure really faster than LINQ
Jul 01, 2012 11:24 AM|LINK
Thanks a lot Rick
It's gonna help me very much.
Vanda Developers Co.