Hey. used the trial and came into a very inefficient SQL

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
nadavsof
Posts: 1
Joined: Sat 07 Nov 2009 01:24

Hey. used the trial and came into a very inefficient SQL

Post by nadavsof » Sat 07 Nov 2009 01:30

* using docConnect with oracle DB

the sql is very hard to read, but thats ok
whats not ok, is that i've put Include for two tables (northwind) for orders
and filtered the orders with the same two tables: shippers and customer


if i would have written an sql statement for this, there would be only one join with each table (to bring the data, and to filter)

as you can see below, it makes few joins with every table...

that means i can't use your provider (or entity framework, i don't know what is the cause of this - the provider or may be its just a bug in EF) or may be i'm just using it wrong (in that case i would be very pleased if someone can correct me, because not using the EF means alot more code to be written).

looking forward for a reply. thanks.


* every time the word Membership appears there, its a join on some table (membership is the oracle schema name)
-----------------
The object query, and the SQL (commented) below
-----------------

Entities4 context = new Entities4();
var query = context.Orders.Include("Customers").Include("Shippers")
.Where(o => o.Customers.CustomerID == "BSBEV" &&
o.Customers.Country != "gever gever" &&
o.Customers.ContactName != "entity framework makes very bad SQL" &&
o.OrderID > 10570 &&
o.Shippers.CompanyName == "Federal Shipping" &&
o.Shippers.CompanyName == "Federal Shipping" &&
o.Shippers.CompanyName == "Federal Shipping") as ObjectQuery;


// it can get the queries result with one join for customers, and on for shippers (both include and Wheres. instead look what it does!!!
// Customers is there only 3 times - include, country and contact. it knows it should take the customer id from the orders table. very bad!
/*
query.ToTraceString()
"SELECT \r\n1 AS C1, \r\n\"Filter3\".\"OrderID1\" AS \"OrderID\", \r\n\"Filter3\".\"EmployeeID\" AS \"EmployeeID\", \r\n\"Filter3\".\"OrderDate\" AS \"OrderDate\", \r\n\"Filter3\".\"RequiredDate\" AS \"RequiredDate\", \r\n\"Filter3\".\"ShippedDate\" AS \"ShippedDate\", \r\n\"Filter3\".\"Freight\" AS \"Freight\", \r\n\"Filter3\".\"ShipName\" AS \"ShipName\", \r\n\"Filter3\".\"ShipAddress\" AS \"ShipAddress\", \r\n\"Filter3\".\"ShipCity\" AS \"ShipCity\", \r\n\"Filter3\".\"ShipRegion\" AS \"ShipRegion\", \r\n\"Filter3\".\"ShipPostalCode\" AS \"ShipPostalCode\", \r\n\"Filter3\".\"ShipCountry\" AS \"ShipCountry\", \r\n\"Filter3\".\"OrderID2\" AS \"OrderID1\", \r\n\"Extent8\".\"CustomerID\" AS \"CustomerID\", \r\n\"Extent8\".\"CompanyName\" AS \"CompanyName\", \r\n\"Extent8\".\"ContactName\" AS \"ContactName\", \r\n\"Extent8\".\"ContactTitle\" AS \"ContactTitle\", \r\n\"Extent8\".\"Address\" AS \"Address\", \r\n\"Extent8\".\"City\" AS \"City\", \r\n\"Extent8\".\"Region\" AS \"Region\", \r\n\"Extent8\".\"PostalCode\
" AS \"PostalCode\", \r\n\"Extent8\".\"Country\" AS \"Country\", \r\n\"Extent8\".\"Phone\" AS \"Phone\", \r\n\"Extent8\".\"Fax\" AS \"Fax\", \r\n\"Extent9\".\"ShipperID\" AS \"ShipperID\", \r\n\"Extent9\".\"CompanyName\" AS \"CompanyName1\", \r\n\"Extent9\".\"Phone\" AS \"Phone1\"\r\nFROM (SELECT \"Filter2\".\"OrderID1\", \"Filter2\".\"CustomerID1\", \"Filter2\".\"EmployeeID\", \"Filter2\".\"OrderDate\", \"Filter2\".\"RequiredDate\", \"Filter2\".\"ShippedDate\", \"Filter2\".\"ShipVia\", \"Filter2\".\"Freight\", \"Filter2\".\"ShipName\", \"Filter2\".\"ShipAddress\", \"Filter2\".\"ShipCity\", \"Filter2\".\"ShipRegion\", \"Filter2\".\"ShipPostalCode\", \"Filter2\".\"ShipCountry\", \"Filter2\".\"OrderID2\", \"Filter2\".\"CustomsDescription\", \"Filter2\".\"ExciseTax\", \"Filter2\".\"CustomerID2\", \"Filter2\".\"CompanyName1\", \"Filter2\".\"ContactName1\", \"Filter2\".\"ContactTitle1\", \"Filter2\".\"Address1\", \"Filter2\".\"City1\", \"Filter2\".\"Region1\", \"Filter2\".\"PostalCode1\", \"Filter2\".\"Country1\
", \"Filter2\".\"Phone1\", \"Filter2\".\"Fax1\", \"Filter2\".\"CustomerID3\", \"Filter2\".\"CompanyName2\", \"Filter2\".\"ContactName2\", \"Filter2\".\"ContactTitle2\", \"Filter2\".\"Address2\", \"Filter2\".\"City2\", \"Filter2\".\"Region2\", \"Filter2\".\"PostalCode2\", \"Filter2\".\"Country2\", \"Filter2\".\"Phone2\", \"Filter2\".\"Fax2\", \"Filter2\".\"ShipperID1\", \"Filter2\".\"CompanyName3\", \"Filter2\".\"Phone3\", \"Extent6\".\"ShipperID\" AS \"ShipperID2\", \"Extent6\".\"CompanyName\" AS \"CompanyName4\", \"Extent6\".\"Phone\" AS \"Phone4\", \"Extent7\".\"ShipperID\" AS \"ShipperID3\", \"Extent7\".\"CompanyName\" AS \"CompanyName5\", \"Extent7\".\"Phone\" AS \"Phone5\"\r\n\tFROM (SELECT \"Filter1\".\"OrderID1\", \"Filter1\".\"CustomerID1\", \"Filter1\".\"EmployeeID\", \"Filter1\".\"OrderDate\", \"Filter1\".\"RequiredDate\", \"Filter1\".\"ShippedDate\", \"Filter1\".\"ShipVia\", \"Filter1\".\"Freight\", \"Filter1\".\"ShipName\", \"Filter1\".\"ShipAddress\", \"Filter1\".\"ShipCity\", \"Filter1\".\"Shi
pRegion\", \"Filter1\".\"ShipPostalCode\", \"Filter1\".\"ShipCountry\", \"Filter1\".\"OrderID2\", \"Filter1\".\"CustomsDescription\", \"Filter1\".\"ExciseTax\", \"Filter1\".\"CustomerID2\", \"Filter1\".\"CompanyName1\", \"Filter1\".\"ContactName1\", \"Filter1\".\"ContactTitle1\", \"Filter1\".\"Address1\", \"Filter1\".\"City1\", \"Filter1\".\"Region1\", \"Filter1\".\"PostalCode1\", \"Filter1\".\"Country1\", \"Filter1\".\"Phone1\", \"Filter1\".\"Fax1\", \"Extent4\".\"CustomerID\" AS \"CustomerID3\", \"Extent4\".\"CompanyName\" AS \"CompanyName2\", \"Extent4\".\"ContactName\" AS \"ContactName2\", \"Extent4\".\"ContactTitle\" AS \"ContactTitle2\", \"Extent4\".\"Address\" AS \"Address2\", \"Extent4\".\"City\" AS \"City2\", \"Extent4\".\"Region\" AS \"Region2\", \"Extent4\".\"PostalCode\" AS \"PostalCode2\", \"Extent4\".\"Country\" AS \"Country2\", \"Extent4\".\"Phone\" AS \"Phone2\", \"Extent4\".\"Fax\" AS \"Fax2\", \"Extent5\".\"ShipperID\" AS \"ShipperID1\", \"Extent5\".\"CompanyName\" AS \"CompanyName3\", \"Exte
nt5\".\"Phone\" AS \"Phone3\"\r\n\t\tFROM (SELECT \"Extent1\".\"OrderID\" AS \"OrderID1\", \"Extent1\".\"CustomerID\" AS \"CustomerID1\", \"Extent1\".\"EmployeeID\" AS \"EmployeeID\", \"Extent1\".\"OrderDate\" AS \"OrderDate\", \"Extent1\".\"RequiredDate\" AS \"RequiredDate\", \"Extent1\".\"ShippedDate\" AS \"ShippedDate\", \"Extent1\".\"ShipVia\" AS \"ShipVia\", \"Extent1\".\"Freight\" AS \"Freight\", \"Extent1\".\"ShipName\" AS \"ShipName\", \"Extent1\".\"ShipAddress\" AS \"ShipAddress\", \"Extent1\".\"ShipCity\" AS \"ShipCity\", \"Extent1\".\"ShipRegion\" AS \"ShipRegion\", \"Extent1\".\"ShipPostalCode\" AS \"ShipPostalCode\", \"Extent1\".\"ShipCountry\" AS \"ShipCountry\", \"Extent2\".\"OrderID\" AS \"OrderID2\", \"Extent2\".\"CustomsDescription\" AS \"CustomsDescription\", \"Extent2\".\"ExciseTax\" AS \"ExciseTax\", \"Extent3\".\"CustomerID\" AS \"CustomerID2\", \"Extent3\".\"CompanyName\" AS \"CompanyName1\", \"Extent3\".\"ContactName\" AS \"ContactName1\", \"Extent3\".\"ContactTitle\" AS \"ContactTit
le1\", \"Extent3\".\"Address\" AS \"Address1\", \"Extent3\".\"City\" AS \"City1\", \"Extent3\".\"Region\" AS \"Region1\", \"Extent3\".\"PostalCode\" AS \"PostalCode1\", \"Extent3\".\"Country\" AS \"Country1\", \"Extent3\".\"Phone\" AS \"Phone1\", \"Extent3\".\"Fax\" AS \"Fax1\"\r\n\t\t\tFROM
MEMBERSHIP.\"Orders\" \"Extent1\"\r\n\t\t\t
* LEFT OUTER JOIN
MEMBERSHIP.\"InternationalOrders\" \"Extent2\" ON \"Extent1\".\"OrderID\" = \"Extent2\".\"OrderID\"\r\n\t\t\t
* LEFT OUTER JOIN
MEMBERSHIP.\"Customers\" \"Extent3\" ON \"Extent1\".\"CustomerID\" = \"Extent3\".\"CustomerID\"\r\n\t\t\t
* WHERE (('BSBEV' = \"Extent1\".\"CustomerID\") AND (\"Extent1\".\"OrderID\" > 10570)) AND
* ('gever gever' \"Extent3\".\"Country\") ) \"Filter1\"\r\n\t\t
* LEFT OUTER JOIN
MEMBERSHIP.\"Customers\" \"Extent4\" ON \"Filter1\".\"CustomerID1\" = \"Extent4\".\"CustomerID\"\r\n\t\t
* INNER JOIN
MEMBERSHIP.\"Shippers\" \"Extent5\" ON \"Filter1\".\"ShipVia\" = \"Extent5\".\"ShipperID\"\r\n\t\t
WHERE ('entity framework makes very bad SQL' \"Extent4\"
.\"ContactName\") AND ('Federal Shipping' = \"Extent5\".\"CompanyName\") ) \"Filter2\"\r\n\t
* INNER JOIN
MEMBERSHIP.\"Shippers\" \"Extent6\" ON \"Filter2\".\"ShipVia\" = \"Extent6\".\"ShipperID\"\r\n\t
* INNER JOIN
MEMBERSHIP.\"Shippers\" \"Extent7\" ON \"Filter2\".\"ShipVia\" = \"Extent7\".\"ShipperID\"\r\n\t
* WHERE ('Federal Shipping' = \"Extent6\".\"CompanyName\") AND
* ('Federal Shipping' = \"Extent7\".\"CompanyName\") ) \"Filter3\"\r\n
* LEFT OUTER JOIN
MEMBERSHIP.\"Customers\" \"Extent8\" ON \"Filter3\".\"CustomerID1\" = \"Extent8\".\"CustomerID\"\r\n
* LEFT OUTER JOIN
MEMBERSHIP.\"Shippers\" \"Extent9\" ON \"Filter3\".\"ShipVia\" = \"Extent9\".\"ShipperID\""
*/

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 10 Nov 2009 11:14

Entity Framework basing on LINQ to Entities creates an expresssion tree, which is converted to SQL by our provider. There is a number of optimizations in the expression tree generation in Entity Framework 4 Beta 2 comparing to Entity Framework v1 to make the generated SQL size smaller. Probably, additional optimization will be implemented in the release of Entity Framework 4.
It is better to minimize the number of the Include entries in your queries - possible side effects were discussed at http://www.devart.com/forums/viewtopic.php?t=16014.

Post Reply