Devart Forums

Posted: **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

Posted: **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.

Posted: **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

Posted: **Fri 20 Jul 2018 16:44**

ole.tetzschner wrote: ↑Sat 14 Jul 2018 08:10But 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:10Is 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.

Posted: **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

Posted: **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;

Posted: **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

Posted: **Tue 06 Nov 2018 18:23**

ole.tetzschner wrote: ↑Thu 01 Nov 2018 20:56But 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.

Devart Forums

BUG or problem in earger loading grandchildren

BUG or problem in earger loading grandchildren

Re: BUG or problem in earger loading grandchildren

Re: BUG or problem in earger loading grandchildren

Re: BUG or problem in earger loading grandchildren

Re: BUG or problem in earger loading grandchildren

Re: BUG or problem in earger loading grandchildren

Re: BUG or problem in earger loading grandchildren

Re: BUG or problem in earger loading grandchildren