Why setting params for JpaRepo too slow for simple table with only 2 cols?

Question

I am using simple Entity with 2 cols only. In my table PK col is varchar in db but in actual values stored is numerical in that col and other col is int. DB is MS SQL Server. And this table has 165 million records.

Table structure:

SECURITY_TAB
varchar(30) SECID PK;
int VERSION;

Entity

@Entity
@Table(name = "SECURITY_TAB")
public class Security {

    @Id
    @Column
    private String secid;

    @Column
    private int version;

//... getter n setter n toString
}

Repository

public interface SecurityRepository extends JpaRepository<Security, String> {

    @Query("SELECT s from Security s where secid= :secid")
    Security findSecurityBySecid(@Param("secid") String secid)); //this is slow

    //below is fine.
    //@Query("SELECT s from Security s where secid='1111'")
    //Security findSecurityBySecid();

}

TestClass

@RunWith(SpringRunner.class)
@SpringBootTest
public class SecurityTests {

    @Autowired
    private SecurityRepository securityRepository;

    @Test
    public void testSecurityBySecid() {

        Instant start = Instant.now();
        Security security = securityRepository.findSecurityBySecid("1111");
        System.out.println(Duration.between(start, Instant.now()));
            System.out.println(security);
        System.out.println(Duration.between(start, Instant.now()));
    }

}

This simple query is taking more than 20 secs, While when I run similar query MS SQL Server Mgmt Studio or hard code the value in the query result is returned in mill seconds. So what is going wrong?

I have no idea what you are asking. Is it about the `findAll` or the single result. Your question is confusing, please clarify. Also add some configuration, especially your datasource. Also comparing plain query execution with JPA is comparing apples and oranges. — M. Deinum, May 13 '19 at 10:15
Earlier i thought it's about findAll, but now it appears it's to do with setting the parameter in the query. This works fine. `@Query("SELECT s from Security s where secid='1111'") Security findSecurityBySecid();` but not this `@Query("SELECT s from Security s where secid= :secid") Security findSecurityBySecid(@Param("secid") String secid));` — Dev, May 13 '19 at 15:50

score 1 · Accepted Answer · answered May 14 '19 at 14:29

was able to solve: The solution was to setup this property in the jdbc url : sendStringParametersAsUnicode=false

Full example if you are using MS SQL official driver : jdbc:sqlserver://yourserver;instanceName=yourInstance;databaseName=yourDBName;sendStringParametersAsUnicode=false;

Solution: sql server query running slow from java

Why setting params for JpaRepo too slow for simple table with only 2 cols?

1 Answers1