Converting expression trees to sql

Idsa · Post by **Idsa** » Thu 21 Jul 2011 09:11

I'm migrating my application from MySQL .NET Connectro to MySQL DotConnector. One of the motivations was that .NET Connector generates very inefficient queries. I hoped your products are much better at this as you have a lot of experience in this field. But looks like DotConnector is also not ideal.

For example, for this expression:

ctx.EnumParameterValues.Where(epv => epv.Parameter.IsAutogenerated == 0 && epv.Parameter.IsDeleted == 0);

there will be generated two joins. But if I replace it to

ctx.EnumParameterValues.Where(epv => epv.ItemId == itemId).Join(ctx.Parameters, epv => epv.ParameterId, p => p.Id, (epv, p) => new { epv, Parameter = p }).Where(at => at.Parameter.IsAutogenerated == 0 && at.Parameter.IsDeleted == 0)

I get the query with one Join as expected.

Is there any documentation or advices of which constructs are better to use? I will post some more examples in this post later.

Idsa · Post by **Idsa** » Thu 21 Jul 2011 18:39

One more example. The following lambda:

ctx.Items.Where(i => i.DevTitle.Length == "string".Length).First();

is converted to

SELECT Extent1.devTitle AS devTitle,
Extent1.id AS id,
Extent1.isAutogenerated AS isAutogenerated
FROM ef.items AS Extent1
WHERE (Length(Extent1.devTitle)) = (Length('string'))
LIMIT 1

So Length('string') wasn't computed at query building time.

Shalex · Post by **Shalex** » Fri 22 Jul 2011 08:25

We are continuously and operatively fixing and improving our EF-providers. New features of the Entity Framework support in dotConnect for MySQL is described at http://www.devart.com/blogs/dotconnect/ ... iders.html.

The 1-st sample. This issue is rather complicated. Indeed, explicit JOIN is more optimal in some cases as you have demonstrated in your code snippet. We will investigate the possibility of optimization of similar queries, but there is no timeframe at the moment.

The 2-nd sample. We will try to solve this issue as soon as possible to include the fix in the nearest build.

If you encounter any other not optimal queries, we will appreciate your reports.

Idsa · Post by **Idsa** » Sat 23 Jul 2011 12:17

One more very inefficient query.

Here is lambda:

ctx.Items.
Include("ItemCategories.Category").
Where(i => itemsIds.Contains(i.Id)).
ToList();

and here is a generated SQL:

SELECT Project1.C1 AS C1,
Project1.devTitle AS devTitle,
Project1.id AS id,
Project1.isAutogenerated AS isAutogenerated,
Project1.C2 AS C2,
Project1.CategoryId AS CategoryId,
Project1.id1 AS id1,
Project1.ItemId AS ItemId,
Project1.description AS description,
Project1.id2 AS id2,
Project1.parentCategory AS parentCategory,
Project1.title AS title,
Project1.autogenerated AS autogenerated,
Project1.path AS path,
Project1.deleted AS deleted
FROM (SELECT Extent1.id AS id,
Extent1.devTitle AS devTitle,
Extent1.isAutogenerated AS isAutogenerated,
1 AS C1,
Join1.Id1 AS id1,
Join1.ItemId AS ItemId,
Join1.CategoryId AS CategoryId,
Join1.id2 AS id2,
Join1.parentCategory AS parentCategory,
Join1.title AS title,
Join1.description AS description,
Join1.autogenerated AS autogenerated,
Join1.path AS path,
Join1.deleted AS deleted,
CASE
WHEN Join1.Id1 IS NULL THEN cast(NULL as signed)
ELSE 1
END AS C2
FROM ef.items AS Extent1
LEFT OUTER JOIN (SELECT Extent2.Id AS Id1,
Extent2.ItemId AS ItemId,
Extent2.CategoryId AS CategoryId,
Extent3.id AS id2,
Extent3.parentCategory AS parentCategory,
Extent3.title AS title,
Extent3.description AS description,
Extent3.autogenerated AS autogenerated,
Extent3.path AS path,
Extent3.deleted AS deleted
FROM ef.itemcategories AS Extent2
INNER JOIN ef.categories AS Extent3
ON Extent2.CategoryId = Extent3.id) AS Join1
ON Extent1.id = Join1.ItemId
WHERE Extent1.id IN (1,2,3,4,
5,6,7,9,
10,11)) AS Project1
ORDER BY Project1.id ASC,
Project1.C2 ASC

The query is very inefficient (I event didn't have a chance to wait till it is executed). Here is on the contrary what MySQL .NET Connector generates:

explain
SELECT `Project1`.`C1`,
`Project1`.`devTitle`,
`Project1`.`id`,
`Project1`.`isAutogenerated`,
`Project1`.`C2`,
`Project1`.`CategoryId`,
`Project1`.`id1`,
`Project1`.`ItemId`,
`Project1`.`autogenerated`,
`Project1`.`deleted`,
`Project1`.`description`,
`Project1`.`id2`,
`Project1`.`parentCategory`,
`Project1`.`path`,
`Project1`.`title`
FROM (SELECT `Extent1`.`devTitle`,
`Extent1`.`id`,
`Extent1`.`isAutogenerated`,
1 AS `C1`,
`Extent2`.`CategoryId`,
`Extent2`.`Id` AS `id1`,
`Extent2`.`ItemId`,
`Extent3`.`autogenerated`,
`Extent3`.`deleted`,
`Extent3`.`description`,
`Extent3`.`id` AS `id2`,
`Extent3`.`parentCategory`,
`Extent3`.`path`,
`Extent3`.`title`,
CASE
WHEN (`Extent2`.`CategoryId` IS NULL) THEN (NULL)
ELSE (1)
END AS `C2`
FROM `items` AS `Extent1`
LEFT OUTER JOIN (`itemcategories` AS `Extent2`
INNER JOIN `categories` AS `Extent3`
ON `Extent2`.`CategoryId` = `Extent3`.`id`)
ON `Extent1`.`id` = `Extent2`.`ItemId`
WHERE (((1 = `Extent1`.`id`)
OR (2 = `Extent1`.`id`))
OR ((3 = `Extent1`.`id`)
OR (4 = `Extent1`.`id`)))
OR ((((5 = `Extent1`.`id`)
OR (6 = `Extent1`.`id`))
OR ((7 = `Extent1`.`id`)
OR (8 = `Extent1`.`id`)))
OR ((9 = `Extent1`.`id`)
OR (10 = `Extent1`.`id`)))) AS `Project1`
ORDER BY `id` ASC,
`C2` ASC

Yep, it doesn't use IN in the query... but it is much more efficient.

Idsa · Post by **Idsa** » Sat 23 Jul 2011 13:21

The simplest query:

return ctx.Categories.Where(c => c.Id == categoryId).Select(c => c.Path).Single();

results in:

SELECT Limit1.path AS path
FROM (SELECT Extent1.path AS path
FROM ef.categories AS Extent1
WHERE Extent1.id = 43 /* @p__linq__0 */
LIMIT 2) AS Limit1

Shalex · Post by **Shalex** » Mon 25 Jul 2011 12:16

Thank you for your reports.

1. The 2-nd sample. The processing of LINQ expressions which work with literals is improved. The fix will be included in the next build of dotConnect for MySQL. We will post here when it is available for download.

2. Could you please send us your entity model and the script of your database objects which can be used to reproduce the issues reported in the 1-st, 3-d, and 4-th samples?

3. The 3-d sample. Is a bad performance the only problem here? What should we change in the generation to create a more efficient query?

4. The 4-th sample. What should we change in the generation to create a more efficient query?

Idsa · Post by **Idsa** » Mon 25 Jul 2011 15:33

2. I'm too lazy for that

I would better provider you connection string to my test server. You can generate a model, look at indexes, etc. I will send it to you by email. I hope you won't modify there anything.

3. I have provided you what query MySQL .NET Connector generates for the same lambda. And this query works very efficient (and looks reasonable). So I guess you should generate something similar

4. In my opinion, it should be query like

select path from categories where categoryId = 43 limit 2

I don't see any reason for nested query here

Idsa · Post by **Idsa** » Mon 25 Jul 2011 15:35

Hm... I have found out PM is disabled here. Please write me to [email protected] and I will send you connection string.

Shalex · Post by **Shalex** » Mon 25 Jul 2011 15:48

Please send your connection string to us via our contact form: http://www.devart.com/company/contact.html. We recommend you to remove your e-mail address from your previous post to avoid the spam attacks.

Idsa · Post by **Idsa** » Mon 25 Jul 2011 15:53

Shalex · Post by **Shalex** » Fri 29 Jul 2011 09:25

New build of dotConnect for MySQL 6.30.196 is available for download!
It can be downloaded from http://www.devart.com/dotconnect/mysql/download.html (trial version) or from Registered Users' Area (for users with valid subscription only): http://secure.devart.com/ .

For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=21606 (this build includes the fix for the second sample).