OData Performance Issue

[alexneville911cool]

Hi

OData 8.2.0

We execute the following OData query:
https://localhost:44394/odata/EntitySet('BSL24OMNEH2TCP0')?$expand=observations

Which generates following SQL, which executes normally and we get results as expected:
SQL QUERY 1

SELECT o.source_id, o.col1, o.col2, o.col3, o.col4, o.col5, o.col6, o0.source_id, o0.col1, o0.col2
FROM ParentTableName AS o
LEFT JOIN ChildTableName AS o0 ON o.source_id = o0.source_id
WHERE o.source_id = @__source_id_0
ORDER BY o.source_id, o0.source_id

BUT

When changing the OData query structure to the following:
https://localhost:44394/odata/EntitySet?$expand=observations&$filter=source_id eq 'BSL24OMNEH2TCP0'

The generated SQL is totally different, which causes significantly poor database performance (SQL inner selects, no filtering on the ChildTableName dramatically increase SQL query cost):
SQL QUERY 2

SELECT o.source_id, o.col1, o.col2, o.col3, o.col4, o.col5, o.col6, o0.source_id, o0.col1, o0.col2
FROM (
SELECT o.source_id, o.col1, o.col2, o.col3, o.col4, o.col5, o.col6
FROM ParentTableName AS o
WHERE o.source_id = @__TypedProperty_0
ORDER BY o.source_id
LIMIT @__TypedProperty_4
) AS t
LEFT JOIN (
SELECT t1.source_id, t1.col1, t1.col2
FROM (
SELECT o0.source_id, o0.col1, o0.col2, ROW_NUMBER() OVER(PARTITION BY o0.source_id ORDER BY o0.col1) AS row
FROM ChildTableName AS o0
) AS t1
WHERE t1.row <= @__TypedProperty_2
) AS t0 ON t.source_id = t0.source_id
ORDER BY t.source_id, t0.source_id, t0.col1

QUESTION:
Is there any way to influence the process of generating SQL query from the second OData request to increase the performance on the database side? Alternate suggestions are more than welcome, thanks!

[julealgon]

Alternate suggestions are more than welcome, thanks!

Is there a particular reason why you can't just use the first form for the query?

I realize this doesn't fix the issue with the second, but if you can just avoid it, wouldn't that be enough?

[xuzhg]

@alexneville911cool The second SQL statement looks weird to me. It's a little bit complex comparing to my understanding for $expand and $filter. Would you please share your repro for us to investigate?

By the way, you can "customize" the filter and expand binder to build your own linq expression if the default implementation can't meet your perf requirement.

[alexneville911cool]

Alternate suggestions are more than welcome, thanks!

Is there a particular reason why you can't just use the first form for the query?

I realize this doesn't fix the issue with the second, but if you can just avoid it, wouldn't that be enough?

@julealgon Thanks, well the reason is that we cannot influence the clients to avoid using the second form of the OData query as it had worked for their client apps before with a legacy API gateway.

[alexneville911cool]

Would you please share your repro for us to investigate?

@xuzhg The reproduction steps are simple actually, please correct me from wrong, they are mentioned above, with OData 8.2.0 the OData query by default is transformed into the mentioned SQLs.

By the way, you can "customize" the filter and expand binder to build your own linq expression if the default implementation can't meet your perf requirement.

This is quite interesting; would you please share some example code snippets for the mentioned OData queries to somehow enforce OData lib generate the Query1 structure in both the cases? Thank you!

[ElizabethOkerio]

@alexneville911cool. Here is a link with an example on how to override the default binders and build your own implementation. Let us know if it helps: https://devblogs.microsoft.com/odata/customizing-filter-for-spatial-data-in-asp-net-core-odata-8/

[alexneville911cool]

@xuzhg Hello, sorry for bother, no pushing, just really curious about the customization you had proposed. Would you be so kind to advice some example code or an article where I could apply it specifically to my case? Thank you.

[alexneville911cool]

Thank you @ElizabethOkerio looked through the article. Well, custom binders are not an easy one challenge, though might you be aware of the binders specifically related to $expand behaviour of OData and that is enforcing it not to use inner selects but rather direct joins in SQL between tables with common columns. Our observation show that the SQL queries with inner select statements are being analyzed differently by Postgres database and this is where the bottle neck in performance. The problem is that we cannot control the OData queries, so the only way is to somehow reconfigure the library that we use to deploy a bit different architecture of the SQL while generating one from the OData query.

[ElizabethOkerio]

@alexneville911cool for select and expand use these binders: https://github.com/OData/AspNetCoreOData/blob/12d6080519571f65c4a68ac6e536a07a28b2c5e5/src/Microsoft.AspNetCore.OData/Query/Expressions/ISelectExpandBinder.cs
and
src/Microsoft.AspNetCore.OData/Query/Expressions/SelectExpandBinder.cs

[JustinasVisakav]

@alexneville911cool The second SQL statement looks weird to me. It's a little bit complex comparing to my understanding for $expand and $filter. Would you please share your repro for us to investigate?

By the way, you can "customize" the filter and expand binder to build your own linq expression if the default implementation can't meet your perf requirement.

Hello @xuzhg, this is the simplified repo for this specific issue:
https://github.com/JustinasVisakav/ODataExample

For this example query that generates normal sql is :
https://localhost:7048/odata/Capacity_By_Company_Int_Databrowser('ABC')?$expand=observations
Abnormal :
https://localhost:7048/odata/Capacity_By_Company_Int_Databrowser?$expand=observations&$filter=source_id eq 'ABC'

I've included sql script to create schema for models.