SDR queries all associations even if they are not in the requested projection [DATAREST-1089]

[spring-projects-issues]

Josh Wand opened DATAREST-1089 and commented

I have noticed that even when specifying a lightweight projection, SDR nonetheless queries every single association on my entity, without checking to see if it's in the projection. This results in many extra DB queries that are dramatically slowing down my application.

I've traced the code to this line:

https://github.com/spring-projects/spring-data-rest/blob/master/spring-data-rest-webmvc/src/main/java/org/springframework/data/rest/webmvc/EmbeddedResourcesAssembler.java#L77

There are checks to see if the association is linkable, or if itself has an excerpt projection, but not to see if the requested association is specified in the projection itself.

Can this check be added? This is a major performance issue.

(see also https://stackoverflow.com/questions/44379069/how-to-avoid-n1-queries-with-spring-data-rest-projections )

---

3 votes, 5 watchers

[spring-projects-issues]

Josh Wand commented

I have created a POC for a fix here:

https://github.com/joshwand/spring-data-rest/commit/3e085703f3d79f05db42c33d1165e3d98d2ad90e#diff-bd6adb65a9054351d2c26ee935d37932R84

Alas my skills/time are insufficient to figure out how to write a good test for this, but my manual testing showed dramatically reduced numbers of SQL queries hitting the DB (no lazy associations get queried, instead of ALL of them)

[spring-projects-issues]

Josh Wand commented

Incidentally, wouldn't it be awesome if I could specify the JPA EntityGraph on the projection definition (see DATAJPA-749) so I could fetch just the properties I need for this usecase in one query?

[spring-projects-issues]

Josh Wand commented

reading the comments in DATAJPA-749, what if the various RepositoryInvoker s checked to see if a query method is defined on the Repository that has the requested Projection as the defined return type? This would seem to be more in line with your desired abstractions...

[spring-projects-issues]

Oliver Drotbohm commented

Do you have a sample project reproducing the issue? I fear we can't just skip those properties as — looking at the code — the values are indeed added to the resource to be created. I.e. i fear that aborting the processing here will change the representation in a backwards incompatible way. A tiny Boot app showing the problem would be perfect.

Generally speaking, I think we have to slightly shift our look at this from "let's fix a JPA performance problem" to "what does the representations look like and what are the means to control what's appearing in them"

[spring-projects-issues]

Josh Wand commented

It seems the behavior only shows itself when an excerptProjection is defined on the association's repository.

Here's a repro case.. check AddressRepository and try commenting/uncommenting the excerptProjection and running the various testcases.

https://github.com/joshwand/jpaprojectionassociationtest

Sorry that I was unable to assert on the sql queries being issued... you'll have to look at the logs. (the test cases fail but it'll help to show the sql logs).

(again being able to tune the fetch plan per projection is really what's needed to be able to make these performant without writing custom controllers)

[spring-projects-issues]

Oliver Drotbohm commented

Thanks, Josh, I'll have a look

[spring-projects-issues]

Israel José Boiko commented

To avoid this in my project I will stop using:

@RepositoryRestResource(excerptProjection = DefaultProjection.class)

So I will always use a standard projection, eg.:

?projection=default

in my REST queries, solving this problem.

One solution that I imagine would be to create a proxy for the collection, being with late resolution

[spring-projects-issues]

If you would like us to look at this issue, please provide the requested information. If the information is not provided within the next 7 days this issue will be closed.