Return to Question

EDIT 3:

 @Query(value = "SELECT REP_BAL_QTY\r\n" + "FROM ODH_INV.SBT_INVEN\r\n" + "WHERE CUST_NBR = RIGHT(CONCAT('0000000000', CAST(:customerNumber AS varchar)), 10) " + "AND EAN_UPC = RIGHT(CONCAT('0000000000000', CAST(:eanUpc AS varchar)), 13)", nativeQuery = true) Optional<Integer> findReportedBalanceQuantityByCustomerNumberAndEanUpc(String customerNumber, String eanUpc); 

So this is working fine inside a repository, but in this case it's an API I'm exposing, and I only need the REP_BAL_QTY column in the result, so I have to run this query for each row of the page I'm returning. Before I had this mapped in the entity:

 @MapsId @OneToOne @JoinColumns({ @JoinColumn(name = "EAN_UPC", referencedColumnName = "EAN_UPC"), @JoinColumn(name = "CUST_NBR", referencedColumnName = "CUST_NBR") }) @NotFound(action = NotFoundAction.IGNORE) private SbtInventory sbtInventory; 

This was called when the entity was loaded and the process is a bit faster, I think ( besides the obvious, avoiding the boilerplate code ) Using the join in the entity, the process to bring a page of 10 rows lasts:
SbtInventoryController.getData took 84113 ms
This is one of the executed queries for the join:

select sbtinvento0_.CUST_NBR as CUST_NBR1_19_0_, sbtinvento0_.EAN_UPC as EAN_UPC2_19_0_, sbtinvento0_.REP_BAL_QTY as REP_BAL_3_19_0_ from ODH_INV.SBT_INVEN sbtinvento0_ where sbtinvento0_.CUST_NBR=? and sbtinvento0_.EAN_UPC=? 

Using the query, the process lasts:
SbtInventoryController.getData took 14063 ms
So it's a huge performance jump. The downside is that by using the query, I have to drop sorting on the column holding REP_BAL_QTY :(

SELECT CUST_NBR, EAN_UPC, REP_BAL_QTY FROM ODH_INV.SBT_INVEN WHERE CUST_NBR = RIGHT(CONCAT('0000000000', CAST(0000100974, varchar)), 10) AND EAN_UPC = '0042823091698'; 

SELECT CUST_NBR, EAN_UPC, REP_BAL_QTY FROM ODH_INV.SBT_INVEN WHERE CUST_NBR = RIGHT(CONCAT('0000000000', CAST(0000100974 AS varchar)), 10) AND EAN_UPC = '0042823091698'; 

EDIT 2: I'm going to reply to some of gadget's questions here

Any customization in the hibernate mapping of customerNumber in your SbtInventory entity? Could you please post the mapping?

@Getter @Setter @AllArgsConstructor @NoArgsConstructor @ToString @Entity @Builder @Table(name = "SBT_INVEN", schema = "ODH_INV") public class SbtInventory { @EmbeddedId private SbtInventoryIdentity sbtInventoryId; @Column(name = "REP_BAL_QTY") private Integer reportedBalanceQuantity; } @Getter @Setter @AllArgsConstructor @NoArgsConstructor @ToString @EqualsAndHashCode(callSuper = false) @Embeddable public class SbtInventoryIdentity implements Serializable { /** * */ private static final long serialVersionUID = -892835625356278964L; @Column(name = "CUST_NBR") private String customerNumber; @Column(name = "EAN_UPC") private String eanUpc; } 

Also what dialect are you using for hibernate?

Full config properties for the database connection:

 datasource: url: jdbc:sqlserver://db-url:db-port;databaseName=db-name username: user password: pass driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver hikari: data-source-properties: cachePrepStmts: true prepStmtCacheSize: 250 prepStmtCacheSqlLimit: 2048 useServerPrepStmts: false maximum-pool-size: 50 jpa: database-platform: org.hibernate.dialect.SQLServer2008Dialect database: SQL_SERVER show-sql: true hibernate: ddl-auto: none properties: hibernate.dialect: org.hibernate.dialect.SQLServer2008Dialect hibernate.jdbc.batch_size: 20 hibernate.cache.use_second_level_cache: false hibernate.enable_lazy_load_no_trans: true 

Any particular reason for using char instead of varchar in the db?

That's a question I don't have the exact answer to. This is how the database was designed. Customer number is a foreign key to another table, where the primary key was created as Char. These are some old tables and in order to keep that standard, they keep going with char for legacy fields.