SpringBoot中JPARepository findAllById多列大表查询性能异常问询

Great question! Let's dig into why you're seeing such massive performance differences between Spring Data JPA's JpaRepository.findById() (I suspect you meant this instead of findAllById() since you're passing a single AccountsPk) and your custom EntityManager query—especially since the raw SQL runs in 1 second flat.

Key Reasons for the Performance Gap #

Spring Data JPA doesn't have a hard "limit" for wide entities, but its default implementation has subtle overhead that can blow up with 255 columns and large datasets:

1. Criteria API Overhead
   The default findById() method for embedded IDs uses JPA's Criteria API under the hood to dynamically build the query. For entities with hundreds of fields, the Criteria API spends extra time constructing query metadata and mapping rules—time that's skipped when you write a direct JPQL query like your EntityManager example. This overhead becomes noticeable when dealing with such a wide table.

2. Proxy Initialization & Post-Load Processing
   If you're using Hibernate (the most common JPA provider with Spring Boot), findById() returns a lazy-initialized proxy by default. Even without associations, Hibernate might run extra checks or post-load processing for all 255 columns when the proxy is first accessed. Your custom EntityManager query likely returns a fully initialized entity directly, skipping these proxy-related steps.

3. Spring Data Framework Overhead
   Spring Data adds abstraction layers for transaction management, method validation, and caching checks. While useful for most cases, these add small latency that compounds with a wide entity. Your manual EntityManager query skips much of this framework-level processing.

Fixes to Match EntityManager Performance #

You don't have to abandon Spring Data JPA—just bypass the slow default method:

1. Add a Custom JPQL Query to Your Repository
   Define a custom query in your AccountsRepository that mirrors your EntityManager logic. This uses direct JPQL instead of the Criteria API, cutting out the overhead:

   public interface AccountsRepository extends JpaRepository<Accounts, AccountsPk> {
       @Query("SELECT a FROM Accounts a WHERE a.id.applicationNumber = :appNumber AND a.id.accountNumber = :accNumber")
       Optional<Accounts> findByCompositeId(
           @Param("appNumber") String appNumber,
           @Param("accNumber") String accNumber
       );
   }
   

   This should perform nearly as fast as your manual EntityManager query.

2. Disable Proxy Creation (If Unneeded)
   If you still want to use findById(), turn off proxy creation for the Accounts entity (lazy loading isn't useful here anyway):

   @Entity
   @Proxy(lazy = false)
   public class Accounts {
       // ... your entity fields
   }
   

   This removes proxy-related overhead, though a custom query is cleaner.

3. Validate Transaction Boundaries
   Spring Data JPA methods run in a transaction by default. If your manual EntityManager query isn't using a transaction, transaction setup/teardown might add latency. Test wrapping your EntityManager code in a transaction to see if the gap narrows.

Final Takeaway #

Spring Data JPA's convenience comes with minor overhead that's negligible for most entities, but becomes problematic with extremely wide tables (255 columns is a lot!). The fix is to avoid Criteria API-backed default methods for these cases and use explicit JPQL queries instead.

内容的提问来源于stack exchange，提问作者v47