Hey. used the trial and came into a very inefficient SQL
Posted: 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\""
*/
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\""
*/