BUG or problem in earger loading grandchildren

[ole.tetzschner]

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]

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]

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]

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.

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]

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]

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]

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]

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.