I wrote this SQL Query and using it as a native query in hibernate.

@Query(
        value = "SELECT DISTINCT tp.* FROM TWITTER_POST AS tp "  
                "JOIN TWITTER_LIST AS tl "  
                    "ON tl.owner_id = ?1 "  
                "JOIN REL_TWITTER_LIST__ACCOUNTS_TRACKED_BY_LIST AS atbl "  
                    "ON tl.id = atbl.twitter_list_id "  
                "JOIN TWITTER_ACCOUNT AS ta "  
                    "ON ta.id = atbl.accounts_tracked_by_list_id "  
                "LEFT OUTER JOIN REL_TWITTER_POST__TWITTER_USERS_HIDING_POST uhp "  
                    "ON tp.id = uhp.twitter_post_id "  
                    "AND uhp.TWITTER_USERS_HIDING_POST_ID = ?1 "  
                "WHERE uhp.twitter_post_id is NULL AND ta.id = tp.author_id",
        countQuery = "SELECT DISTINCT count(tp.*) FROM TWITTER_POST AS tp "  
                     "JOIN TWITTER_LIST AS tl "  
                        "ON tl.owner_id = ?1 "  
                     "JOIN REL_TWITTER_LIST__ACCOUNTS_TRACKED_BY_LIST AS atbl "  
                        "ON tl.id = atbl.twitter_list_id "  
                     "JOIN TWITTER_ACCOUNT AS ta "  
                        "ON ta.id = atbl.accounts_tracked_by_list_id "  
                     "LEFT OUTER JOIN REL_TWITTER_POST__TWITTER_USERS_HIDING_POST uhp "  
                        "ON tp.id = uhp.twitter_post_id "  
                        "AND uhp.TWITTER_USERS_HIDING_POST_ID = ?1 "  
                     "WHERE uhp.twitter_post_id is NULL AND ta.id = tp.author_id",
        nativeQuery = true
    )
    Page<TwitterPost> findAllNonHiddenForListsFromTwitterAccountId(Long twitterAccountId, Pageable pageable);

I noticed that the query executes very slowly when I'm running it through hibernate as opposed to a SQL tool. I assumed it was because I am using a native query as opposed to JQPL, which (from what I read) immediately does caching and pagination without requiring a definition for "count". Trying to convert it to JQPL failed, because I cannot find a good tutorial for more complicated queries on JQPL across join tables.

@Query(
    value = "SELECT DISTINCT twitterPost "  
            "FROM TwitterPost twitterPost "  
            "JOIN TwitterList twitterList "  
                "ON twitterList.owner.id = ?1 "  
            "JOIN TwitterAccount tweetAuthorFromList "  
                "ON tweetAuthorFromList IN twitterList.accountsTrackedByLists "  
            "WHERE twitterPost.author = tweetAuthorFromList "  
                "AND twitterList.owner NOT IN twitterPost.twitterUsersHidingPosts"
)
Page<TwitterPost> findAllNonHiddenPostsFromListsForTwitterAccountId(Long twitterAccountId, Pageable pageable);

Apparently my Syntax is off

org.hibernate.exception.SQLGrammarException: could not prepare statement

but the compiler only shows me problems with the generated SQL, not the JQPL so I'm left in the dark.

Also checked for typical bad performance culprits i.e. eager fetching of entities which I set to lazy everywhere.

Any help regarding whether my performance problem assumptions are correct, or converting the query, are highly appreciated - thanks in advance!

CodePudding user response：

There are many things that are wrong here:

• Using SELECT DISTINCT with JOIN indicates that you should have used a Semi Join instead. Check out this video course for more details about this optimization.
• The ON tl.owner_id = ?1 is done for filtering, not for the projection, hence you are better off doing an EXISTS query instead.
• Assuming why the query runs slow instead of profiling it. The reason why it runs faster in the DB tool is that DB tools usually truncate the result set while Spring Data consumes the entire result set. Or, if you run EXPLAIN, the output might come from the Optimizer without even running the query.

So, here's what you can do:

1. Use Semi Joins instead of Joins for filtering.
2. Use Blaze Persistence to write better entity queries dynamically.
3. Configure Statement Caching as I explained in my High-Performance Java Persistence book.
4. Use the slow query log to log the execution plan when the query is slower than N seconds.