Οκτώβριος 2015 - Δημοσιεύσεις
In this post I am going to provide you with a hands-on example on how to take advantage of asynchronous execution using Entity Framework. EF 6 has the ability to execute a query and command asynchronously using DbContext.
Entity Framework is an object-relational mapping (ORM) framework for the .NET Framework.EF addresses the problem of Object-relational impedance mismatch. I will not be talking about that mismatch because it is well documented in many sites on the Internet.
Through that framework we can program against a conceptual application model instead of programming directly against a relational schema-model. By doing so we can decrease the amount of code we do write to access a data storage and thus decrease maintenance time. You can find many posts regarding Entity Framework in this blog.
Before I go on with my hands-on example I would like to talk about asynchronous programming.
Asynchronous programing is everywhere these days. It doesn't matter if you're on the client or the server. On the client, you want asynchronous operations to take place because you want to keep the UI thread free. You don't want it to block on IO operations because then the UI freezes and it doesn't responds to the user's mouse clicks. This is why the A in Ajax stands for asynchronous and it's partially why Ajax makes browser applications more responsive. It keeps that UI thread free.
On the server, we also want async operations because that keeps the request processing thread free from blocking on IO operations that keeps them busy in executing and servicing network request.
There's such a demand for async programming these days that Microsoft introduced new keywords into the C# language to make async programming simple. The keywords are async and await.
I would like to continue on asynchronous programming and explain what is thread processing on the application server (IIS) and how the .Net Framework maintains threads to service requests.
If you need to build a highly scalable web application then one of your goals is to keep the threads in server process as busy as possible.
In an environment like ASP.NET, there are only a limited number of threads reserved for processing HTTP requests.
If these threads have to wait for a long input output operation to complete, you end up with those threads sitting idle and doing no work.
A classic example of this is a controller action (in ASP.Net MVC) that needs to call another web service, an IO operation to produce the result.
If that request that travels over the network, and we often measure that in milliseconds,let's say that it takes 250 milliseconds, then the processing thread has to sit idle for 250 milliseconds. If all the threads are tied up and idle,any new HTTP request that are arriving at the server, ASP.NET will queue them up and force them to wait for one of this request processing threads to free out.
We can free our threads out from running long input output operations and using asynchronous behavior on the server. This has nothing to do with Ajax or asynchronous calls on the client. People often confuse Ajax partial updates with asynchronous programing on the server.
On the web server, the .NET Framework maintains a pool of threads that are used to service ASP.NET requests. When a request arrives, a thread from the pool is dispatched to process that request.
If the request is processed synchronously, the thread that processes the request is busy while the request is being processed, thus the thread cannot service another request.
This might not be a problem, because the thread pool can be made large enough to accommodate many busy threads. However, the number of threads in the thread pool is limited (the default maximum for .NET 4.5 is 5,000). In large applications with high concurrency of long-running requests, all available threads might be busy. This condition is known as thread starvation. When this condition is reached, the web server queues requests. If the request queue becomes full, the web server rejects requests with an HTTP 503 status (Server Too Busy).
Another very important point that I want to make is that asynchrnous does not mean parallel, it simply means that the IIS web server can handle more concurrent requests. If you have a database that is the bottleneck asynchronous programming will not solve that problem. You should tackle the issues that make database performance to be the main problem in the application lifecycle (poor indexes, badly designed queries, low memory).Basically, the rule of thumb is to use an async task when your operation is accessing a slow medium like the network, disk, or database.
You may want to have a look in this article as well.
I will build a small ASP.Net application that will fetch data from a database using the EF 6.0 (database first) as my data access layer and writing LINQ to Entities queries against the entity data model.
Building a web application using non blocking calls to the data layer is a great way to increase the scalability of your system. When performing a task asynchronously we free up the worker thread to accept another request while work is being done in the background. Entity Framework 6.0 has support for Async/Await pattern (e.g ToListAsync, FirstAsync).
1) Create an empty ASP.Net Web Application (Empty MVC Application) and give it the name EF6AspNetAsynchronous. I am using Visual Studio 2015 Enterprise edition.
2) I will use the AdventureWorks2014 database (You can download it here) for this application and more specifically the Person.CountryRegion table. I have installed SQL Server 2014 Enterprise edition in my machine. SQL Express edition will work fine.
3) I will add an ADO.Net Entity data model (inside the Models folder) using Database First. Follow the wizzard steps, create the connection string and then import into the conceptual model the Person.CountryRegion table which will become an entity in the domain model. If you want to look at those detailed steps if you are new to EF and Database First have a look at this post.
4) I will query the database using synchronous methods first. Then I will change the code in my EF 6.0 data access layer and in the controller to leverage async support in ASP.Net MVC and EF 6.0.
5) Add a new item, a class file in the Models folder. The name of the class is DBHelper.cs
public static class DBHelper
{
public static List<CountryRegion> SelectAll()
{
AdventureWorks2014Entities ctx = new AdventureWorks2014Entities();
var query = from c in ctx.CountryRegions
orderby c.Name ascending
select c;
return query.ToList();
}
}
6) Add an empty Controller with the name CountryController.cs
The code for the CountryController controller follows
public class CountryController : Controller
{
// GET: Country
private AdventureWorks2014Entities db = new AdventureWorks2014Entities();
public ActionResult Index()
{
List<CountryRegion> model = DBHelper.SelectAll();
return View(model);
}
}
We also need to reference our model
using EF6AspNetAsynchronous.Models;
7) Add an empty View in the Views Folder. First create a new Country folder inside the Views folder.
The code for the View follows.
@model IEnumerable<EF6AspNetAsynchronous.Models.CountryRegion>
@{
ViewBag.Title = "CountryRegion";
}
<h2>Index</h2>
<table class="table">
<tr>
<th>
@Html.DisplayNameFor(model => model.CountryRegionCode)
</th>
<th>
@Html.DisplayNameFor(model => model.Name)
</th>
<th>
@Html.DisplayNameFor(model => model.ModifiedDate)
</th>
<th></th>
</tr>
@foreach (var item in Model)
{
<tr>
<td>
@Html.DisplayFor(modelItem => item.CountryRegionCode)
</td>
<td>
@Html.DisplayFor(modelItem => item.Name)
</td>
<td>
@Html.DisplayFor(modelItem => item.ModifiedDate)
</td>
</tr>
}
</table>
8) Build and run your application. All the information is displayed in the page. Have a look at the picture below.
9) Let me show you how to create the asynchronous version of this simple web application.
In the DBHelper.cs class change the code to the code below
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Threading.Tasks;
namespace EF6AspNetAsynchronous.Models
{
public static class DBHelper
{
public async static Task<List<CountryRegion>> SelectAllAsync()
{
AdventureWorks2014Entities ctx = new AdventureWorks2014Entities();
var query = from c in ctx.CountryRegions
orderby c.Name ascending
select c;
return await query.ToListAsync();
}
}
}
I changed the name of the method to SelectAllAsync.The method SelectAllAsync() method is denoted with async keyword.The method returns a Task of a List of CountryRegion entities. The return keyword is followed by the await keyword.
I do not call the ToList() method the return statement calls the ToListAsync() method.The ToListAsync() is the asynchronous method that EF 6.0 supports.
10) Now let's make the necessary changes to the Controller
public class CountryController : Controller
{
// GET: Country
private AdventureWorks2014Entities db = new AdventureWorks2014Entities();
public async Task<ActionResult> Index()
{
List<CountryRegion> model = await DBHelper.SelectAllAsync();
return View(model);
}
}
}
We also need to reference the using System.Threading.Tasks; namespace.
The Index() method itself is marked with an async keyword and it returns a Task object wrapping the ActionResult.
I make a call to the SelectAllAsync() method of the DBHelper class. This time SelectAllAsync() is called with an await keyword and returns a List of CountryRegion objects.Build and run your application again. You will get the same results.
We looked into a detailed example on how to select data from a data table in a datastore, SQL Server, through EF Database first leveraging the EF 6.0 support for asynchronous operations and the Async/Await pattern of ASP.Net 4.5.
Hope it helps!!!
This is the second post in a series of posts where I talk about good coding practices when it comes to using Entity Framework as our data access layer when building our applications.
You can read the first post of the series here. The main things to take away from that first post is to use projection whenever possible (do not query sql server for data that you do not want on the client) and to filter on the server (meaning SQL Server) which has a powerful engine to do that and not on the client.
In this post I am going to provide you with a hands-on example on how to avoid writing your LINQ to Entities queries in a way that will hinder performance of your application. The aim of this post (hopefully a series of posts on performance and Entity Framework) is to highlight bad coding practices when architecting an applications that uses EF as the ORM to fetch and manipulate data from the data store. I am going to point out some practises and patterns that very often developers use and cause EF to create poor-performing T-SQL statements.
First, a quick word on what Entity Framework is. Entity Framework is an object-relational mapping (ORM) framework for the .NET Framework.EF addresses the problem of Object-relational impedance mismatch. I will not be talking about that mismatch because it is well documented in many sites on the Internet.
Through that framework we can program against a conceptual application model instead of programming directly against a relational schema-model. By doing so we can decrease the amount of code we do write to access a data storage and thus decrease maintenance time. You can find many posts regarding Entity Framework in this blog.
A lot of people wonder why we should use Entity Framework in the first place. We could still keep using good old T-SQL in our applications.
The obvious answer is that EF addresses the Object Relation impedance mismatch and it bridges those two different worlds. Entity Framework creates an object oriented model for accessing the data tier. In an object oriented development environment, it makes working with the data tier much more seamless for the developer. It allows developers to spend time writing code for their application rather than dealing with the tedious tasks of opening connections to the database e.t.c. The abstraction that is offered by EF by generating the intermediate code, which in this case is T-SQL, it's much easier to migrate code to another platform, such as Oracle or Postgres or some other ODBC source. We get incredible flexibility by doing this.
Using EF does not mean we should forget about SQL Server, T-SQL, relationships, foreign keys and performance. We should keep in mind that SQL Server is based on set theory and relational algebra and it thrives when acting on sets of data, updating a set of rows rather than each row of data at the time.
1) Create an empty ASP.Net Application (Web Forms Application) and give it the name EFoptimisation2. I am using Visual Studio 2013 Ultimate edition.
2) Add a new web forms page in the application. Leave the default name. The application I am going to build is very simple web forms application. The user will enter a last name and will get back the first name(s) for that last name.
3) I will use the AdventureWorks2014 database (You can download it here) for this application and more specifically the Person.Person table. I have installed SQL Server 2014 Enterprise edition in my machine.
4) I will add an ADO.Net Entity data model using Database First paradigm. Follow the wizzard steps, create the connection string and then import into the conceptual model only the Person and EmailAddress tables which will become an entities in the domain model. If you want to look at those detailed steps if you are new to EF and Database First have a look at this post.
5) Add a textbox and a button to the page. The user will enter the first name in the textbox and will hit enter and then the results (the email addresses for that first name) will be printed on the page.We will navigate to the EmailAddress entity throug the navigation property EmailAddresses
This is the code inside the Page_Load event handling routine.
protected void Page_Load(object sender, EventArgs e)
{
using (var ctx = new AdventureWorks2014Entities())
{
string FirstName = TextBox1.Text;
var query = from p in ctx.People
where p.FirstName.Equals(FirstName)
select p;
foreach (var person in query)
{
foreach (var email in person.EmailAddresses)
{
Response.Write(email.EmailAddress1);
Response.Write("<br/>");
}
}
}
}
The code above is pretty straight forward.
6) Now we are ready to run the application. Before we do that I will launch SSMS and connect to my local instance of SQL Server. Then I will also launch the SQL Profiler and create a new trace. The trace will listen only for the RPC:Completed event. I activate te trace so the trace is running.
7) I build and run my web app. The results I get back when typing "Alex" as first name is 51 email addresses.
8) Let me see what the trace recorded in my SQL Profiler and the T-SQL that was generated.
We have an individual statement for every email address that we retrieved. This is a not a set based operation since we issue many transactions to the SQL Server.
9) Now we will rewrite our code above in order for EF to work better with the SQL Engine. I am going to use the "Include" method in my code.
We do inform Entity Framework that not only we want all the columns from the People object specified in the from clause, but also want all those columns in the path specified as a parameter of the include method-- EmailAddress in our scenario.
protected void Page_Load(object sender, EventArgs e)
{
using (var ctx = new AdventureWorks2014Entities())
{
string FirstName = TextBox1.Text;
var query = from p in ctx.People.Include("EmailAddresses")
where p.FirstName.Equals(FirstName)
select p;
foreach (var person in query)
{
foreach (var email in person.EmailAddresses)
{
Response.Write(email.EmailAddress1);
Response.Write("<br/>");
}
}
}
}
The Profiler is still running on the background.
I build and run my web app. The results I get back when typing "Alex" as first name is 51 email addresses.
This is what I get back from the Profiler.
Now as you notice it's a pretty extensive query but there is a problem with this approach.We are not allowed to use projection. We can only use the columns of the People object.
10) We need to rewrite our code again. I will use explicit joins this time.The code follows.
protected void Page_Load(object sender, EventArgs e)
{
using (var ctx = new AdventureWorks2014Entities())
{
string FirstName = TextBox1.Text;
var query = from p in ctx.People
join email in ctx.EmailAddresses
on p.BusinessEntityID equals email.BusinessEntityID
where p.FirstName.Equals(FirstName)
select new { email.EmailAddress1};
foreach (var item in query)
{
Response.Write(item.EmailAddress1);
Response.Write("<br/>");
}
}
}
The Profiler is still running on the background.
I build and run my web app. The results I get back when typing "Alex" as first name is 51 email addresses.
This is what I get back from the Profiler.
As we can see this is a T-SQL statement that we could type in an SSMS Query window. We have one query that results in one set based operation thus improving greatly the performance of our application by getting rid off the unecessary round trips.
11) We could rewrite the code above in a more object oriented way using lambda expressions.
protected void Page_Load(object sender, EventArgs e)
{
using (var ctx = new AdventureWorks2014Entities())
{
string FirstName = TextBox1.Text;
var query = ctx.People
.Where(p => p.FirstName.Equals(FirstName))
.SelectMany(email => email.EmailAddresses)
.Select(theemail => theemail.EmailAddress1);
foreach (var item in query)
{
Response.Write(item);
Response.Write("<br/>");
}
}
}
The Profiler is still running on the background.
I build and run my web app. The results I get back when typing "Alex" as first name is 51 email addresses.
This is the T-SQL statement I got back from the Profiler. This is what it was executed against the database.
EXEC sp_executesql N'SELECT
[Extent2].[EmailAddress] AS [EmailAddress]
FROM [Person].[Person] AS [Extent1]
INNER JOIN [Person].[EmailAddress] AS [Extent2] ON [Extent1].[BusinessEntityID] = [Extent2].[BusinessEntityID]
WHERE [Extent1].[FirstName] = @p__linq__0', N'@p__linq__0 nvarchar(4000)',
@p__linq__0 = N'Alex'
If we typed that query ourselves in an SSMS query window we would type something like this:
SELECT Person.EmailAddress.EmailAddress
FROM Person.EmailAddress
INNER JOIN Person.Person ON Person.EmailAddress.BusinessEntityID = Person.Person.BusinessEntityID
WHERE Person.Person.FirstName = 'Alex'
As you can see those two queries are pretty much the same.
Entity framework abstracts the T-SQL creation from us, the developers. Having said that we are still in charge of the overall performance of our application. Performance plays always a big role in any application. We do know that SQL Server thrives on set based operations.We should write our Linq to Entities queries in a way that set based T-SQL statements are generated.
Hope it helps!!!
In this post I am going to provide you with a hands-on example on how to avoid writing your LINQ to Entities queries in a way that will hinder performance of your application. The aim of this post (hopefully a series of posts on performance and Entity Framework) is to highlight bad coding practices when architecting an applications that uses EF as the ORM to fetch and manipulate data from the data store. I am going to point out some practises and patterns that very often developers use and cause EF to create poor-performing T-SQL statements.
Entity Framework will always create T-SQL, the thing to keep in mind is that we have to make sure that this T-SQL code (that we cannot write ourselves since it is abstracted by EF) if it is poor then when passed to the SQL Server engine (through the optimiser and the creation of the execution plan) will cause our applications to perform poorly especially under heavy load.
Let me talk a bit about query optimisation and T-SQL. T-SQL is declarative by nature. When we write T-SQL statements in a query window in SSMS and execute them,we just say to SQL Server “I want these results back”. We do not provide any details on how the results will be returned.If there was nothing else between our T-SQL code and the SQL Server Database engine, we simply would not get any results back.Luckily for us there is a very important component, the Query Optimizer that generates an imperative plan. By saying imperative I mean detailed. This plan that is called execution plan is what is actually executed by the relational engine.The query optimiser will not look for a perfect plan.It is a cost-based optimiser that must find an efficient plan.The optimiser when deciding upon the execution plan will take in to consideration the type of operations,statistics (must always be up to date),indexes,hardware resources (number of CPUs ,available memory),SQL Server edition,number of active concurrent connections and query hints. If the T-SQL that is generated by the EF is pooly written then the optimiser will not create an optimal plan hence the problems in performance.
Entity Framework is an object-relational mapping (ORM) framework for the .NET Framework.EF addresses the problem of Object-relational impedance mismatch. I will not be talking about that mismatch because it is well documented in many sites on the Internet.
Through that framework we can program against a conceptual application model instead of programming directly against a relational schema-model. By doing so we can decrease the amount of code we do write to access a data storage and thus decrease maintenance time. You can find many posts regarding Entity Framework in this blog.
1) Create an empty ASP.Net Application (Web Forms Application) and give it the name EFoptimisation. I am using Visual Studio 2013 Ultimate edition.
2) Add a new web forms page in the application. Leave the default name. The application I am going to build is very simple web forms application. The user will enter a last name and will get back the first name(s) for that last name.
3) I will use the AdventureWorks2014 database (You can download it here) for this application and more specifically the Person.Person table. I have installed SQL Server 2014 Enterprise edition in my machine.
4) I will add an ADO.Net Entity data model using Database First paradigm. Follow the wizzard steps, create the connection string and then import into the conceptual model only the Person.Person table which will become an entity in the domain model. If you want to look at those detailed steps if you are new to EF and Database First have a look at this post.
5) Add a textbox and a button to the page. The user will enter the last name in the textbox and will hit enter and then the results will be printed on the page.
This is the code inside the Page_Load event handling routine.
protected void Page_Load(object sender, EventArgs e)
{
using( var ctx = new AdventureWorks2014Entities())
{
string LastName = TextBox1.Text;
var query = from person in ctx.People
select person;
foreach (var p in query)
{
if (p.LastName==LastName)
{
Response.Write(p.FirstName);
Response.Write("<br/>");
}
}
}
}
The code above is pretty straight forward.
6) Now we are ready to run the application. Before we do that I will launch SSMS and connect to my local instance of SQL Server. Then I will also launch the SQL Profiler and create a new trace. The trace will listen only for the SQL:BatchStarting event. I activate te trace so the trace is running.
7) I build and run my web app. The results I get back when typing "Yuan" as last name is 92 first names.
8) Let me see what the trace recorded in my SQL Profiler and the T-SQL that was generated.
9) If I copy and paste the T-SQL in my SSSM and execute the query (Enable Actual Execution Plan and Client Statistics) I will get the following results - 19972 rows in total. Ηave a look at the piscture below
Now let's have a look at the execution plan created. Have a look at the picture below.
We have a Clustered Index Scan.The Clustered Index Scan means that SQL Server started at the very top of the table and scanned every row until it reached the bottom of the table. Not a very good scenario to have in terms of performance. Now lets have at the Client Statistics tab. Have a look at the picture below.
As you can see the bytes transfered from SQL Server is almost 26 mbytes.That is a huge amount of data to be transfered through the network back to the client to satisfy a single query.
10) We need to refactor our code in order to create more efficient T-SQL code.
protected void Page_Load(object sender, EventArgs e)
{
using( var ctx = new AdventureWorks2014Entities())
{
string LastName = TextBox1.Text;
var query = from person in ctx.People
where person.LastName.Equals(LastName)
select person;
foreach (var p in query)
{
Response.Write(p.FirstName);
Response.Write("<br/>");
}
}
}
As you can see from the code above I am doing now the filtering on the server.
Have a look below to see what the Profiler's trace output was.
The T-SQL now has a Where clause.It is a parametirised query.If I place this query in my SSMS and execute it I will get back 92 rows only and my execution plain will look like this
This is a by far more optimal executon plan(Index Seek & Lookup) that the Clustered Index Seek.
If I look at the Client Statistics tab (Bytes received from the server), I have only 145Kbytes of data compared with the 26Mbytes I had previously.
11) Now we can use projection to retrieve only the columns that we are interested in (FirstName) and get rid of the other ones.
I go back to the Page_Load routine and instead of
var query = from person in ctx.People
where person.LastName.Equals(LastName)
select person;
I rewrite my code to utilize projection
var query = from person in ctx.People
where person.LastName.Equals(LastName)
select new { person.FirstName };
Then I build and run the application with the Profiler running.
This is the T-SQL from the Profiler when I type "Yuan" in the textbox.
exec sp_executesql N'SELECT
1 AS [C1],
[Extent1].[FirstName] AS [FirstName]
FROM [Person].[Person] AS [Extent1]
WHERE [Extent1].[LastName] = @p__linq__0',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'Yuan'
As you can see from the T-SQL above I get only the FirstName column. If I place the T-SQL code in an SSMS query window (Enable Client Statistics & Actual Execution plan) I get back 92 rows of data and the following picture shows the actual exection plan. Now we see a Non Clustered Index Seek. We are seeking and not scanning which is a great thing.
Let's have a look at the client statistics.As you can see from the picture below the bytes received from the server is just 9183 bytes , 9 Kbytes. This is huge reduction compared to the 145 Kbytes and 26 Mbytes.
To recap, make sure that you do all the filtering on SQL Server and use projection when you do not want all the table columns in your application, when writing apps that use EF as the data access layer.It is always wrong to filter on the client when SQL Server has all the power to do that for us.
Hope it helps!!!