Get vs filter

BIZ TMS XData
1

I have strange situation
I'm retrieving product entity with some subentities
this takes less than 100ms
https://..../product(4002515320228)?$expand=group,subgroup,department,supplier
but with filter
/product?$filter=Id eq '4002515320228'&$expand=group,subgroup,department,supplier
It takes about 2.5-3secs
Id is unique id.

These should be equal???

1 Like
2

Yes, it should be. Have you treaced the generated SQL?

1 Like
3

I wonder if it's in the way it handles the sub queries for the expand statement? In version 1 it knows it is querying a primary key, but in the second (unless it looks up whether Id is the primary) it wouldn't. Not that that should make a difference, but it might do.

4

Actually I found the difference
My product table is actually build with inheritance (tables are actually product and product2 so Tproduct2=class(tproduct) ... )
and query by Id places actual id filter by product2 and using filter creates query which filteres by product
so
Direct Get by id is

SELECT tons of fields
FROM product2 A
  INNER JOIN product B ON (B.id = A.id)
  few left join
WHERE  A.id = :p_0

and when using filter

SELECT tons of fields
FROM product A
  INNER JOIN product2 B ON (B.id = A.id)
  few left join
WHERE  B.id = :p_0    --<---  here B id

for some reason, our DB optimizes those queries differently.

2 Likes
5

interesting.

What database are you using?

6

Yes, the reason seems to be the inheritance. The Find uses a different algorithm to build the query.

7

I'm using Actian ZEN.

8

Interesting. I've not used that. Are you using Firedac?

9

Actually Native ODBC or Unidac using ODBC). We have been using Unidac elsewhere in our application Actian ZEN isn't officially supported, I made integration my self and with Wagners help. Thanks to his very long long nerves :grin:

2 Likes