Many to Many and slow queries

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
Posts: 123
Joined: Tue 19 Feb 2008 19:12

Many to Many and slow queries

Post by KW » Sun 25 Apr 2010 20:50

Why is the generated sql painfully slow? I believe I have the latest version of everything right now. I upgraded Devart version last week along with fresh install of wcf ria services, visual studio 2010 rtm.

Here is the query. I am asking for the first 100 Reservation records and their related information.

Code: Select all

 var query = from c in this.ObjectContext.Reservation1.Top("100").Include("Customer_Reservation.Customer")
                        select c;

            return query;

Code: Select all

Table Definitions:

CREATE TABLE  `Test`.`Reservation` (
  `ReservationID` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT 'EMPTY',
  `OwnedBy` varchar(128) DEFAULT 'Current_User()',
  `DateCreated` datetime DEFAULT NULL,
  `CreatedBy` varchar(128) DEFAULT NULL,
  `GroupLeader` varchar(64) DEFAULT NULL,
  `GroupLeader_ReservationID` varchar(64) DEFAULT NULL,
  `Cancelled` tinyint(1) DEFAULT NULL,
  `Type` varchar(50) DEFAULT '',
  `CheckInDate` datetime DEFAULT NULL,
  `CheckOutDate` datetime DEFAULT NULL,
  `Confirmation_Num` varchar(50) DEFAULT '',
  `Arrival Time` varchar(50) DEFAULT NULL,
  `Notes` mediumtext,
  `RoomPrice` decimal(19,2) NOT NULL DEFAULT '0.00',
  `Extras` decimal(19,2) NOT NULL DEFAULT '0.00',
  `Deposit` decimal(19,2) DEFAULT '0.00',
  `IsLocked` tinyint(4) NOT NULL,
  `LockedBy` varchar(64) DEFAULT NULL,
  `RoomTax` decimal(10,2) NOT NULL DEFAULT '0.00',
  `Rate` decimal(10,2) NOT NULL DEFAULT '0.00',
  `MiscItems` decimal(10,2) NOT NULL DEFAULT '0.00',
  `TotalRentalCar` decimal(10,2) NOT NULL DEFAULT '0.00',
  `TotalPackagePrice` decimal(10,2) NOT NULL DEFAULT '0.00',
  `BalanceDue` decimal(10,2) NOT NULL DEFAULT '0.00',
  `Source` varchar(64) DEFAULT NULL,
  `InitialNotes` mediumtext,
  `GolfandRoomNotes` mediumtext,
  `ClosingNotes` mediumtext,
  `AdvertiseRooms` tinyint(4) DEFAULT NULL,
  `AdvertiseGolf` tinyint(4) DEFAULT NULL,
  `AdvertiseRentalCar` tinyint(4) DEFAULT NULL,
  `SpecialInfo` tinyint(4) DEFAULT NULL,
  `SpecialInfoYear` varchar(48) DEFAULT NULL,
  `CompleteListCourses` tinyint(4) DEFAULT NULL,
  `CompleteListCoursesList` varchar(48) DEFAULT NULL,
  `ParentReservation` varchar(64) DEFAULT NULL,
  `RedDate` datetime DEFAULT NULL,
  `FollowUp` int(11) DEFAULT NULL,
  `CreatedByQuoteID` varchar(64) DEFAULT NULL,
  `SalesTax_RoomPrice` decimal(10,2) NOT NULL DEFAULT '0.00',
  `SalesTax_RoomTax` decimal(10,2) NOT NULL DEFAULT '0.00',
  `SalesTax_Rate` decimal(10,2) NOT NULL DEFAULT '0.00',
  `SalesTax_Extras` decimal(10,2) NOT NULL DEFAULT '0.00',
  `SalesTax_TotalRentalCar` decimal(10,2) NOT NULL DEFAULT '0.00',
  `SalesTax_TotalPackagePrice` decimal(10,2) NOT NULL DEFAULT '0.00',
  `SalesTax_Deposit` decimal(10,2) NOT NULL DEFAULT '0.00',
  `SalesTax_Balance` decimal(10,2) NOT NULL DEFAULT '0.00',
  `IsCharted` tinyint(4) NOT NULL DEFAULT '0',
  `DateCharted` datetime DEFAULT NULL,
  `ChartedBy` varchar(64) NOT NULL,
  `CancelledDate` datetime DEFAULT NULL,
  PRIMARY KEY (`ReservationID`)

CREATE TABLE  `Test`.`Customer_Reservation` (
  `ReservationID` varchar(64) CHARACTER SET utf8 NOT NULL,
  `CustomerID` varchar(64) CHARACTER SET utf8 NOT NULL,
  `Amount` decimal(19,4) NOT NULL,
  PRIMARY KEY (`ReservationID`,`CustomerID`),
  KEY `FK_Customer_Reservation_2` (`CustomerID`),
  KEY `FK_Customer_Reservation_1` (`ReservationID`),
  CONSTRAINT `FK_Customer_Reservation_1` FOREIGN KEY (`ReservationID`) REFERENCES `Reservation` (`ReservationID`),
  CONSTRAINT `FK_Customer_Reservation_2` FOREIGN KEY (`CustomerID`) REFERENCES `Customer` (`CustomerID`)

CREATE TABLE  `Test`.`Customer`(
  `CustomerID` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT 'new',
  `Sal` varchar(64) DEFAULT NULL,
  `First Name` varchar(50) DEFAULT NULL,
  `Last Name` varchar(50) DEFAULT NULL,
  `Country` varchar(50) DEFAULT NULL,
  `City` varchar(500) DEFAULT NULL,
  `State` varchar(50) DEFAULT NULL,
  `Street` varchar(50) DEFAULT NULL,
  `Zip` varchar(50) DEFAULT NULL,
  `House Number` varchar(50) DEFAULT NULL,
  `Email` varchar(100) DEFAULT NULL,
  `Email2` varchar(50) DEFAULT NULL,
  `Phone` varchar(50) DEFAULT NULL,
  `Cell Phone` varchar(50) DEFAULT NULL,
  `Work Phone` varchar(50) DEFAULT NULL,
  `Fax` varchar(50) DEFAULT NULL,
  `Source` varchar(64) DEFAULT NULL,
  `Type` varchar(45) NOT NULL DEFAULT 'Consumer',
  `Active` tinyint(3) unsigned NOT NULL,
  `Company Name` varchar(200) DEFAULT '',
  PRIMARY KEY (`CustomerID`)

Code: Select all

Query Explain extended SELECT Project1.C1 AS C1, Project1.ReservationID AS ReservationID, Project1.OwnedBy AS OwnedBy, Project1.DateCreated AS DateCreated, Project1.CreatedBy AS CreatedBy, Project1.GroupLeader AS GroupLeader, Project1.GroupLeader_ReservationID AS GroupLeader_ReservationID, Project1.Cancelled AS Cancelled, Project1.Type AS Type, Project1.CheckInDate AS CheckInDate, Project1.CheckOutDate AS CheckOutDate, Project1.Confirmation_Num AS Confirmation_Num, Project1.`Arrival Time` AS `Arrival Time`, Project1.Notes AS Notes, Project1.RoomPrice AS RoomPrice, Project1.Extras AS Extras, Project1.Deposit AS Deposit, Project1.IsLocked AS IsLocked, Project1.LockedBy AS LockedBy, Project1.RoomTax AS RoomTax, Project1.Rate AS Rate, Project1.MiscItems AS MiscItems, Project1.TotalRentalCar AS TotalRentalCar, Project1.TotalPackagePrice AS TotalPackagePrice, Project1.BalanceDue AS BalanceDue, Project1.Source AS Source, Project1.InitialNotes AS InitialNotes, Project1.GolfandRoomNotes AS GolfandRoomNotes, Project1.ClosingNotes AS ClosingNotes, Project1.AdvertiseRooms AS AdvertiseRooms, Project1.AdvertiseGolf AS AdvertiseGolf, Project1.AdvertiseRentalCar AS AdvertiseRentalCar, Project1.SpecialInfo AS SpecialInfo, Project1.SpecialInfoYear AS SpecialInfoYear, Project1.CompleteListCourses AS CompleteListCourses, Project1.CompleteListCoursesList AS CompleteListCoursesList, Project1.ParentReservation AS ParentReservation, Project1.RedDate AS RedDate, Project1.FollowUp AS FollowUp, Project1.CreatedByQuoteID AS CreatedByQuoteID, Project1.DateTimeCreated AS DateTimeCreated, Project1.SalesTax_RoomPrice AS SalesTax_RoomPrice, Project1.SalesTax_RoomTax AS SalesTax_RoomTax, Project1.SalesTax_Rate AS SalesTax_Rate, Project1.SalesTax_Extras AS SalesTax_Extras, Project1.SalesTax_TotalRentalCar AS SalesTax_TotalRentalCar, Project1.SalesTax_TotalPackagePrice AS SalesTax_TotalPackagePrice, Project1.SalesTax_Deposit AS SalesTax_Deposit, Project1.SalesTax_Balance AS SalesTax_Balance, Project1.IsCharted AS IsCharted, Project1.DateCharted AS DateCharted, Project1.ChartedBy AS ChartedBy, Project1.CancelledDate AS CancelledDate, Project1.C2 AS C2, Project1.ReservationID1 AS ReservationID1, Project1.CustomerID AS CustomerID, Project1.Amount AS Amount, Project1.CustomerID1 AS CustomerID1, Project1.Sal AS Sal, Project1.`First Name` AS `First Name`, Project1.`Last Name` AS `Last Name`, Project1.Country AS Country, Project1.City AS City, Project1.State AS State, Project1.Street AS Street, Project1.Zip AS Zip, Project1.`House Number` AS `House Number`, Project1.Email AS Email, Project1.Email2 AS Email2, Project1.Phone AS Phone, Project1.`Cell Phone` AS `Cell Phone`, Project1.`Work Phone` AS `Work Phone`, Project1.Fax AS Fax, Project1.Source1 AS Source1, Project1.Type1 AS Type1, Project1.Active AS Active, Project1.`Company Name` AS `Company Name` FROM ( SELECT Limit1.ReservationID AS ReservationID, Limit1.OwnedBy AS OwnedBy, Limit1.DateCreated AS DateCreated, Limit1.CreatedBy AS CreatedBy, Limit1.GroupLeader AS GroupLeader, Limit1.GroupLeader_ReservationID AS GroupLeader_ReservationID, Limit1.Cancelled AS Cancelled, Limit1.Type AS Type, Limit1.CheckInDate AS CheckInDate, Limit1.CheckOutDate AS CheckOutDate, Limit1.Confirmation_Num AS Confirmation_Num, Limit1.`Arrival Time` AS `Arrival Time`, Limit1.Notes AS Notes, Limit1.RoomPrice AS RoomPrice, Limit1.Extras AS Extras, Limit1.Deposit AS Deposit, Limit1.IsLocked AS IsLocked, Limit1.LockedBy AS LockedBy, Limit1.RoomTax AS RoomTax, Limit1.Rate AS Rate, Limit1.MiscItems AS MiscItems, Limit1.TotalRentalCar AS TotalRentalCar, Limit1.TotalPackagePrice AS TotalPackagePrice, Limit1.BalanceDue AS BalanceDue, Limit1.Source AS Source, Limit1.InitialNotes AS InitialNotes, Limit1.GolfandRoomNotes AS GolfandRoomNotes, Limit1.ClosingNotes AS ClosingNotes, Limit1.AdvertiseRooms AS AdvertiseRooms, Limit1.AdvertiseGolf AS AdvertiseGolf, Limit1.AdvertiseRentalCar AS AdvertiseRentalCar, Limit1.SpecialInfo AS SpecialInfo, Limit1.SpecialInfoYear AS SpecialInfoYear, Limit1.CompleteListCourses AS CompleteListCourses, Limit1.CompleteListCoursesList AS CompleteListCoursesList, Limit1.ParentReservation AS ParentReservation, Limit1.RedDate AS RedDate, Limit1.FollowUp AS FollowUp, Limit1.CreatedByQuoteID AS CreatedByQuoteID, Limit1.DateTimeCreated AS DateTimeCreated, Limit1.SalesTax_RoomPrice AS SalesTax_RoomPrice, Limit1.SalesTax_RoomTax AS SalesTax_RoomTax, Limit1.SalesTax_Rate AS SalesTax_Rate, Limit1.SalesTax_Extras AS SalesTax_Extras, Limit1.SalesTax_TotalRentalCar AS SalesTax_TotalRentalCar, Limit1.SalesTax_TotalPackagePrice AS SalesTax_TotalPackagePrice, Limit1.SalesTax_Deposit AS SalesTax_Deposit, Limit1.SalesTax_Balance AS SalesTax_Balance, Limit1.IsCharted AS IsCharted, Limit1.DateCharted AS DateCharted, Limit1.ChartedBy AS ChartedBy, Limit1.CancelledDate AS CancelledDate, 1 AS C1, Join1.ReservationID AS ReservationID1, Join1.CustomerID1 AS CustomerID, Join1.Amount AS Amount, Join1.CustomerID2 AS CustomerID1, Join1.Sal AS Sal, Join1.`First Name` AS `First Name`, Join1.`Last Name` AS `Last Name`, Join1.Country AS Country, Join1.City AS City, Join1.State AS State, Join1.Street AS Street, Join1.Zip AS Zip, Join1.`House Number` AS `House Number`, Join1.Email AS Email, Join1.Email2 AS Email2, Join1.Phone AS Phone, Join1.`Cell Phone` AS `Cell Phone`, Join1.`Work Phone` AS `Work Phone`, Join1.Fax AS Fax, Join1.Source AS Source1, Join1.Type AS Type1, Join1.Active AS Active, Join1.`Company Name` AS `Company Name`, CASE WHEN (Join1.ReservationID IS NULL) THEN cast(NULL as signed) ELSE 1 END AS C2 FROM (SELECT c.ReservationID AS ReservationID, c.OwnedBy AS OwnedBy, c.DateCreated AS DateCreated, c.CreatedBy AS CreatedBy, c.GroupLeader AS GroupLeader, c.GroupLeader_ReservationID AS GroupLeader_ReservationID, c.Cancelled AS Cancelled, c.Type AS Type, c.CheckInDate AS CheckInDate, c.CheckOutDate AS CheckOutDate, c.Confirmation_Num AS Confirmation_Num, c.`Arrival Time` AS `Arrival Time`, c.Notes AS Notes, c.RoomPrice AS RoomPrice, c.Extras AS Extras, c.Deposit AS Deposit, c.IsLocked AS IsLocked, c.LockedBy AS LockedBy, c.RoomTax AS RoomTax, c.Rate AS Rate, c.MiscItems AS MiscItems, c.TotalRentalCar AS TotalRentalCar, c.TotalPackagePrice AS TotalPackagePrice, c.BalanceDue AS BalanceDue, c.Source AS Source, c.InitialNotes AS InitialNotes, c.GolfandRoomNotes AS GolfandRoomNotes, c.ClosingNotes AS ClosingNotes, c.AdvertiseRooms AS AdvertiseRooms, c.AdvertiseGolf AS AdvertiseGolf, c.AdvertiseRentalCar AS AdvertiseRentalCar, c.SpecialInfo AS SpecialInfo, c.SpecialInfoYear AS SpecialInfoYear, c.CompleteListCourses AS CompleteListCourses, c.CompleteListCoursesList AS CompleteListCoursesList, c.ParentReservation AS ParentReservation, c.RedDate AS RedDate, c.FollowUp AS FollowUp, c.CreatedByQuoteID AS CreatedByQuoteID, c.DateTimeCreated AS DateTimeCreated, c.SalesTax_RoomPrice AS SalesTax_RoomPrice, c.SalesTax_RoomTax AS SalesTax_RoomTax, c.SalesTax_Rate AS SalesTax_Rate, c.SalesTax_Extras AS SalesTax_Extras, c.SalesTax_TotalRentalCar AS SalesTax_TotalRentalCar, c.SalesTax_TotalPackagePrice AS SalesTax_TotalPackagePrice, c.SalesTax_Deposit AS SalesTax_Deposit, c.SalesTax_Balance AS SalesTax_Balance, c.IsCharted AS IsCharted, c.DateCharted AS DateCharted, c.ChartedBy AS ChartedBy, c.CancelledDate AS CancelledDate FROM Test.Reservation AS c limit 100 ) AS Limit1 LEFT OUTER JOIN (SELECT Extent2.ReservationID AS ReservationID, Extent2.CustomerID AS CustomerID1, Extent2.Amount AS Amount, Extent3.CustomerID AS CustomerID2, Extent3.Sal AS Sal, Extent3.`First Name` AS `First Name`, Extent3.`Last Name` AS `Last Name`, Extent3.Country AS Country, Extent3.City AS City, Extent3.State AS State, Extent3.Street AS Street, Extent3.Zip AS Zip, Extent3.`House Number` AS `House Number`, Extent3.Email AS Email, Extent3.Email2 AS Email2, Extent3.Phone AS Phone, Extent3.`Cell Phone` AS `Cell Phone`, Extent3.`Work Phone` AS `Work Phone`, Extent3.Fax AS Fax, Extent3.Source AS Source, Extent3.Type AS Type, Extent3.Active AS Active, Extent3.`Company Name` AS `Company Name` FROM Test.Customer_Reservation AS Extent2 INNER JOIN Test.Customer AS Extent3 ON Extent2.CustomerID = Extent3.CustomerID ) AS Join1 ON Limit1.ReservationID = Join1.ReservationID ) AS Project1 ORDER BY Project1.ReservationID ASC, Project1.C2 ASC -- 0 row(s) affected., Sun Apr 25 12:04:40 2010 

id select_type table type possible_keys key key_len ref rows filtered Extra 
1 PRIMARY  ALL     121 100.00 Using filesort 
2 DERIVED  ALL     100 100.00 (null) 
2 DERIVED  ALL     22111 100.00 (null) 
4 DERIVED Extent2 ALL FK_Customer_Reservation_2    22202 100.00 (null) 
4 DERIVED Extent3 eq_ref PRIMARY PRIMARY 194 Test.Extent2.CustomerID 1 100.00 (null) 
3 DERIVED c ALL     15880 100.00 (null) 



1PRIMARY<derived2>ALL121100.00Using filesort







Is it a problem with the query being generated? This is just terribly slow. Any One to many Include is fast, but trying to naviagate further down the relationship is terribly slow. Am I doing something wrong? Is the query being generated by devart just not good?

Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Wed 28 Apr 2010 12:53

The generated SQL query just represents your LINQ to Entities query
There are many records in the tables you are loading using Include, the query is expected to be rather slow.
Unfortunately, there is no easy way to filter the child collection in LINQ to Entites, unlike LINQ to SQL.

Posts: 123
Joined: Tue 19 Feb 2008 19:12

But Why...

Post by KW » Thu 29 Apr 2010 17:35

it doesn't make any sense. If you want to traverse your object graph, the sql being generated is not good.

If I want all Reservations that include its children why is it so slow?

If you select top 100 Reservations, you should then only be joining the 100 reservations to its child table Customer_Reservation, then you should find maybe 200 childrecords which you will match one to one with the Customer table.

And, this query is extremely fast:

var query = from c in Reservation.Include("Customer_Reservation")

Super fast, happens in seconds. The adding another table just destroys the query.

If I do the same query, simply joining them with a known list of reservationids the query happens in seconds. This just can't be right.

Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Thu 06 May 2010 13:33

We don't perform any additional actions, only ExpressionTree translation to SQL.
The performance issue is EF-specific.
We have much better performance control in our LINQ to Oracle support, it is integrated in the LinqConnect product.

Posts: 25
Joined: Wed 27 Jan 2010 13:20

Post by fni » Mon 10 May 2010 14:18

Is it therefore recommend to use Linq to Oracle instead of Entity Framework with dotConnect?

It would be nice to see recommendations and guidelines.

Posts: 123
Joined: Tue 19 Feb 2008 19:12

Is there any alternatives to query Iqueryable?

Post by KW » Mon 10 May 2010 19:59

First, I am using mysql dotconnect and entity framework for the data layer on top of wcf ria services.

Second, is there another way to query related items instead of using the Include key word? There has got to be a better way to do this.

There has got to be a better way to customerize the query to get related records and have it properly populate wcf ria service entities.

Posts: 123
Joined: Tue 19 Feb 2008 19:12

For example,

Post by KW » Tue 11 May 2010 00:16

Code: Select all

  System.Data.Objects.ObjectSet Reservations = this.ObjectContext.Reservation1;

  System.Data.Objects.ObjectSet Customer_Reservations = this.ObjectContext.Customer_Reservation1;

  System.Data.Objects.ObjectSet Customers = this.ObjectContext.Customer1;

            var query = from Resevation in Reservations.Top("20")
                        join dete in Customer_Reservations
                        on Resevation.ReservationID
                        equals dete.ReservationID
                        join cust in Customers
                        on dete.CustomerID
                        equals cust.CustomerID into CustGroup
                        select new { Resevation, CustGroup };

This is very fast. Is there anyway to convert this into Iqueryable without using the Include keyword, because that is not usable.

Posts: 123
Joined: Tue 19 Feb 2008 19:12

Another update

Post by KW » Tue 11 May 2010 23:07
Using query paths can result in complex commands being executed against the data source from seemingly simple object queries. This occurs because one or more joins are required to return related objects in a single query, which results in redundant data for each related entities being returned from the data source. This complexity is greater in queries against a complex model, such as an entity with inheritance or a path that includes many-to-many relationships.
Here it says that pretty much the Includes on Many to Many relationships blow chunks.

I've have looked this document over and there is nothing in here that says how to use a custom query just direct sql would be fine to load the entities.

Posts: 25
Joined: Wed 27 Jan 2010 13:20

Post by fni » Wed 12 May 2010 06:24

How about making a SQL view? You can then map that to an new entity object.

Posts: 123
Joined: Tue 19 Feb 2008 19:12

Sql view

Post by KW » Wed 12 May 2010 16:37

As far as I know views are for viewing, not for Updating. But if you can expand your thoughts on the matter maybe you can convince me otherwise.

For now I am just making 3 calls to the server instead, which kinda sucks because it doesn't populate all your data in a single call. But, you cannot use .Include with a many to many relationship. It works fine for a one to many, but NOT many to many. 30 seconds isn't tolerable for a query.

Posts: 123
Joined: Tue 19 Feb 2008 19:12

Not your problem

Post by KW » Thu 13 May 2010 16:46 ... 78673.aspx

You are right Andrey, it is EF specific problem.

Post Reply