So I’ve been playing around a bit with ASP.NET MVC lately and the ADO.NET Entity Data Model piece in Visual Studio.
Being of a Dev/DBA mind I started to wonder what effect there was (if any) by willy-nilly adding all the tables of a given database to the Entity Data Model Diagram.
Would it add any additional overhead in SQL Server?
Additionally I wondered how using LINQ and the Entity Model compared to the “old school” .NET SQL Data Provider as far any differences over the network.
I’ll address the former first.
Does selecting all the tables (see the following image) add overhead to normal LINQ-based queries compared with their SQL Data Provider counterparts?
To begin I started a new MVC2 project, selected all the tables in the AdventureWorks database to my model, and created a “ShowList” list view with the following LINQ query:
[sql]
AdventureWorksEntities myCtx = new AdventureWorksEntities();
var records = from a in myCtx.Employees
from b in myCtx.Addresses
from c in myCtx.EmployeeAddresses
where
c.EmployeeID == a.EmployeeID && b.AddressID == c.AddressID
select new {
Title = a.Title,
HireDate = a.HireDate,
VacHours = a.VacationHours,
Address = b.AddressLine1
};
return View(records.ToList());[/sql]
For its Ad-Hoc T-SQL counterpart to compare I used this:
[sql]
SELECT a.Title,
a.HireDate,
a.VacationHours,
b.AddressLine1
FROM HumanResources.Employee AS a,
Person.Address
AS b,
HumanResources.EmployeeAddress AS c
WHERE c.EmployeeID = a.EmployeeID
AND b.AddressID = c.AddressID[/sql]
Running the Ad-Hoc I get this queryplan:
Running the LINQ/Entities query I got this:
So, this is exactly the same “actual query plan”. Not really any surprises there. So, there is no additional overheard on SQL Server itself as far as the SQL being run. (At least not in this simple example). It also does not matter if you add all the tables in your Entities Model or just a few. There is no pre-initialization or weird extra SQL being run.
Now addressing my other question. How does the Entity Model query compare to something like a regular ad-hoc SQL call on a network level?
To test this I’ll need fire up my WireShark (http://www.wireshark.com) network analyzer and filter traffic to the SQL Server.
For the LINQ query I used this statement:
[sql]
AdventureWorksEntities myCtx = new AdventureWorksEntities();
var records = from a in myCtx.Employees
from b in myCtx.Addresses
from c in myCtx.EmployeeAddresses
where
c.EmployeeID == a.EmployeeID && b.AddressID == c.AddressID
select
new
{
Title = a.Title,
HireDate = a.HireDate,
VacHours = a.VacationHours,
Address = b.AddressLine1
};
return View(records.ToList());[/sql]
No initial network hit on program startup. There were 52 lines of activity in WireShark.
For the Ad-hoc query I used the System.Data.SqlClient library and did this:
[sql]
string conString = “Data Source=172.16.15.62;Initial Catalog=AdventureWorks;Integrated Security=SSPI;”;
SqlConnection myConn = new
SqlConnection(conString);
myConn.Open();
string sqlQuery = “SELECT a.Title,a.HireDate,a.VacationHours,b.AddressLine1 FROM HumanResources.Employee AS a, Person.Address AS b, HumanResources.EmployeeAddress AS c WHERE c.EmployeeID = a.EmployeeID AND b.AddressID = c.AddressID”;
SqlCommand myCom = new
SqlCommand(sqlQuery, myConn);
SqlDataReader myReader = null;
myReader = myCom.ExecuteReader();
[/sql]
Again, no initial network hit (no surprise). Got 44 lines of activity in WireShark.
After exporting both of these captures from Wireshark I concluded that LINQ consistently had more line entries in the packet capture logs. As I said above I got 52 lines for LINQ/Entities query and 44 lines for a normal .NET SQL Data Provider query.
The main difference was additional chatter between the SQL server and the client in the LINQ/Entities log. Mainly this line:
[TCP segment of a reassembled PDU]
And also a couple of these:
ms-sql-s > 12988 [ACK] Seq=31429 Ack=1860 Win=64208 Len=0
I’m NOT a networking expert, but to my understanding the reassembled PDU’s are just Wireshark’s way of explaining that it has re-assembled a set of packets to form a TCP segment. For whatever reason it had to do this more often with the LINQ/Entities query.
The last item still has me stumped though. Why is SQL Server talking back to the client? (This also happens to be at the very end of log).
If anyone out there has a link to a whitepaper or an idea as to why let me know.
In summary I can only say that there is definitely some sort of additional network overheard using ADO.NET Entity model versus the .NET SQL Data Provider.
I also found in my example that the query plans were identical and the SQL being queried is the same for the Entity Data Model and LINQ as it is for its .NET SQL Data Provider counterpart.
So, in addition to the inherent things that are necessary for LINQ to do to generate SQL there does seem to be some additional network considerations (however slight). It is, however, much quicker to implement from a developer point of view in my opinion
