BUG or problem in earger loading grandchildren

Discussion of open issues, suggestions and bugs regarding LinqConnect – Devart's LINQ to SQL compatible ORM
Post Reply
ole.tetzschner
Posts: 6
Joined: Thu 12 Jul 2018 10:57

BUG or problem in earger loading grandchildren

Post by ole.tetzschner » Thu 12 Jul 2018 11:26

Hi guys

I've got a problem with eager-loading.

In short, there are 3 tables. tableA (parent), tableB (child) and tableC (grandchild).

After a SELECT and ToList, accessing tableC will cause a lot of no-needed SELECT's (=> getting slow).

Checking the generated first SQL, all the information from tableC are included. So it's confusing why all the subsequents SELECT's to tableC are initiated (and making the program SLOW)?

The (simplified) generated SQL:

Code: Select all

SELECT t1."Id", t1..., t3."Id" AS "Id1", t3..., t4."Id" AS "Id2", t4...
FROM (
    SELECT t2."Id", t2...
    FROM "tableA" t2
    WHERE (t2."someColumn" = :p0)
    ) t1
LEFT OUTER JOIN ("tableB" t3
    LEFT OUTER JOIN "tableC" t4 ON t3."Id" = t4."tableB_Id") ON t1."Id" = t3."tableA_Id"
ORDER BY t1."Id", t3."Id"
But subsequent SELECT's are slowing down the program:

Code: Select all

SELECT t1."Id", t1...
FROM "tableC" t1
WHERE :np0 = t1."tableB_Id"
-- np0: Input BigInt (Size = 0; DbType = Int64) [73361]
-- Context: Devart.Data.PostgreSql.Linq.Provider.PgSqlDataProvider Mapping: AttributeMappingSource Build: 4.8.1471.0

SELECT t1."Id", t1...
FROM "tableC" t1
WHERE :np0 = t1."tableB_Id"
-- np0: Input BigInt (Size = 0; DbType = Int64) [73362]
-- Context: Devart.Data.PostgreSql.Linq.Provider.PgSqlDataProvider Mapping: AttributeMappingSource Build: 4.8.1471.0
My query:

Code: Select all

var queryTableA = context.TableAs
	.LoadWith(a => a.TableBs
	.LoadWith(b => b.TableCs, FetchMode.Join), FetchMode.Join)
	.Where(a => a.someColumn==myCodeValue)
	.OrderBy(a => a.Id)
	.Select(a => a).ToList();
Any light on the problem would be appreciated :)

Kind regards, Ole

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

Re: BUG or problem in earger loading grandchildren

Post by Shalex » Fri 13 Jul 2018 17:05

You are right: in most cases only first level relations are loaded with JOINs, 2nd level and deeper relations are retrieved with additional SELECTs.

Improving the current LoadWith logic is a resource demanding task that requires redesigning LinqConnect ORM framework. We cannot provide any timeframe.

ole.tetzschner
Posts: 6
Joined: Thu 12 Jul 2018 10:57

Re: BUG or problem in earger loading grandchildren

Post by ole.tetzschner » Sat 14 Jul 2018 08:10

So my next thought was to settle with a maximum of two SELECT-server-roundtrips. The first SELECT that will fetch tableA and tableB eagerly. And a new second SELECT that will fetch tableC eagerly.

But that didn't turn out as expected. It's still lazy-loading, why? How do I speed up this SELECT? Is LinqConnect the wrong approach?

My test-code:

Code: Select all

var query2 = context.tableCs
	.Where( c => c.tableB.tableA.Id == 9988)
	.ToList();

With kind regards, Ole

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

Re: BUG or problem in earger loading grandchildren

Post by Shalex » Fri 20 Jul 2018 16:44

ole.tetzschner wrote:
Sat 14 Jul 2018 08:10
But that didn't turn out as expected. It's still lazy-loading, why? How do I speed up this SELECT?
There is no way with a current LinqConnect implementation.
ole.tetzschner wrote:
Sat 14 Jul 2018 08:10
Is LinqConnect the wrong approach?
You can try to perform your task via EF Core (suppoted by dotConnect for MySQL Professional). This ORM is actively developed at the moment.

ole.tetzschner
Posts: 6
Joined: Thu 12 Jul 2018 10:57

Re: BUG or problem in earger loading grandchildren

Post by ole.tetzschner » Sun 22 Jul 2018 09:46

Thanks shalex. The world might be spinning around mysql, but my world are spinning around postgresql 😉
EF Core rocks and solved the problem with two Include-statenents. The performance incresed 18x 👍👍👍
That said, the EF Core don't seem fit for bulk/batch INSERT's (in transaction) for the same table-structure. Any wise thoughts on this topic? All tables has auto-generated Id and Timestamp, which result in a RETURNING.

Kind regards, Ole

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

Re: BUG or problem in earger loading grandchildren

Post by Shalex » Wed 25 Jul 2018 17:19

You should turn on BatchUpdates explicitly:

1) on a per-context basis

Code: Select all

    optionbuilder.UsePostgreSql(connString , b => b.MaxBatchSize(100));
2) globally, for all DbContext instances

Code: Select all

    var config = PgSqlEntityProviderConfig.Instance;
    config.DmlOptions.BatchUpdates.Enabled = true;
    config.DmlOptions.BatchUpdates.BatchSize = 100;

ole.tetzschner
Posts: 6
Joined: Thu 12 Jul 2018 10:57

Re: BUG or problem in earger loading grandchildren

Post by ole.tetzschner » Thu 01 Nov 2018 20:56

Hi shalex

But the doc says that this is not supported on EF Core. Is the doc outdated or what are the ETA for this feature?

https://www.devart.com/dotconnect/postg ... tions.html

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

Re: BUG or problem in earger loading grandchildren

Post by Shalex » Tue 06 Nov 2018 18:23

ole.tetzschner wrote:
Thu 01 Nov 2018 20:56
But the doc says that this is not supported on EF Core. Is the doc outdated or what are the ETA for this feature?

https://www.devart.com/dotconnect/postg ... tions.html
The doc is outdated, we will update it soon.

Post Reply