I created an Entity Framework Model for an existing DB and then used the created DbContext to retrieve/update some tables using linq. What I am noticing is that the first query is very slow (I believe it's the context creation that's taking a long time)
compared to the subsequent queries using the same dbcontext.
So, my questions are:
1. How can I speed up the first query?
2. I am going to use this repository as part of an ASP application. Can I create the context once and store it in session or in the cache so I don't have to create it for every request that comes to the server?
Here is some code: I create a DBContext, then pass that context to my repository class and execute the repository functions to retrieve and update some records. I notice that the first retrieval is slow (5+ seconds) and the second update is efficient.
///TestEntities is a EF DbContext class created when I created the Edmx model
TestDataAccess.TestEntities context = new TestDataAccess.TestEntities("Lib.DbConnectString", false);
///Create repository
TestDataAccess.Repository.PhonesRepository rep = new TestDataAccess.Repository.PhonesRepository(context);
///repository to retrieve phone #, returns an IEnumerable of EFEntities and the query is not executed
IEnumerable<TestDataAccess.PhoneTest> phones = rep.GetPhonesForPerson(3582107);
///This takes a long time since the query is now executed
Assert.IsTrue(phones.Any());
//added the update query to check performance
List<TestDataAccess.PhoneTest> updateList = phones.ToList();
TestDataAccess.PhoneTest ph = updateList[0];
if (ph.PhoneExt.Length == 4)
ph.PhoneExt = "1";
else
ph.PhoneExt += (ph.PhoneExt.Length + 1).ToString();
//this is quite fast
rep.UpdatePhones(null, updateList);
EF will do many reflected things through EF's model entities，so you have to use ADO.NET instead of EF if you'd like to speed up your filtering results.
And what's more，you can also use Stored Procdure + EF. Maybe that can help you a lot.
Thanks for the response Decker. However, I don't think the issue is with the query, I can do the same query again (with different parameters) and it's quite fast. It's only for the first time when a query is executed that the query is really slow. I believe
it's an issue of object context creation.
It's only for the first time when a query is executed that the query is really slow.
Hi again,
As far as I see, I think when you do it for the first time, it will fetch out all the satisfied records from db and do caching or something like this, just like when you do asp.net's programming for the 1st time, it's slow; but later it will be fast.
ajayatl
Member
17 Points
16 Posts
First query with DbContext creation takes a long time
Dec 28, 2012 05:37 PM|LINK
I created an Entity Framework Model for an existing DB and then used the created DbContext to retrieve/update some tables using linq. What I am noticing is that the first query is very slow (I believe it's the context creation that's taking a long time) compared to the subsequent queries using the same dbcontext.
So, my questions are:
1. How can I speed up the first query?
2. I am going to use this repository as part of an ASP application. Can I create the context once and store it in session or in the cache so I don't have to create it for every request that comes to the server?
Thank you.
Specs
Member
612 Points
149 Posts
Re: First query with DbContext creation takes a long time
Dec 28, 2012 07:17 PM|LINK
Can you post some of the code?
ajayatl
Member
17 Points
16 Posts
Re: First query with DbContext creation takes a long time
Dec 28, 2012 08:02 PM|LINK
Here is some code: I create a DBContext, then pass that context to my repository class and execute the repository functions to retrieve and update some records. I notice that the first retrieval is slow (5+ seconds) and the second update is efficient.
///TestEntities is a EF DbContext class created when I created the Edmx model TestDataAccess.TestEntities context = new TestDataAccess.TestEntities("Lib.DbConnectString", false); ///Create repository TestDataAccess.Repository.PhonesRepository rep = new TestDataAccess.Repository.PhonesRepository(context); ///repository to retrieve phone #, returns an IEnumerable of EFEntities and the query is not executed IEnumerable<TestDataAccess.PhoneTest> phones = rep.GetPhonesForPerson(3582107); ///This takes a long time since the query is now executed Assert.IsTrue(phones.Any()); //added the update query to check performance List<TestDataAccess.PhoneTest> updateList = phones.ToList(); TestDataAccess.PhoneTest ph = updateList[0]; if (ph.PhoneExt.Length == 4) ph.PhoneExt = "1"; else ph.PhoneExt += (ph.PhoneExt.Length + 1).ToString(); //this is quite fast rep.UpdatePhones(null, updateList);Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: First query with DbContext creation takes a long time
Dec 29, 2012 08:19 AM|LINK
Hi，
EF will do many reflected things through EF's model entities，so you have to use ADO.NET instead of EF if you'd like to speed up your filtering results.
And what's more，you can also use Stored Procdure + EF. Maybe that can help you a lot.
Reguards！
ajayatl
Member
17 Points
16 Posts
Re: First query with DbContext creation takes a long time
Dec 30, 2012 12:32 PM|LINK
Thanks for the response Decker. However, I don't think the issue is with the query, I can do the same query again (with different parameters) and it's quite fast. It's only for the first time when a query is executed that the query is really slow. I believe it's an issue of object context creation.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: First query with DbContext creation takes a long time
Dec 30, 2012 11:44 PM|LINK
Hi again,
As far as I see, I think when you do it for the first time, it will fetch out all the satisfied records from db and do caching or something like this, just like when you do asp.net's programming for the 1st time, it's slow; but later it will be fast.