One to One mapping is creating multiple queries

Question

As there are 100 000 records so I need to fetch top 10 records by joining two tables, am using mssql server,i have written @Query, but its making 11 queries for the same which is causing performance issue.

It is taking around 9-10 seconds.

@Getter
@Setter
@Entity
@Table(name = "TelevisionSource")
public class MyTelevisionSource {
    @Id
    private Long SourceId;

    @Column(columnDefinition = "nvarchar2 (2000)")
    private String TvsourceLongName;

    @Column(columnDefinition = "nvarchar2 (2000)")
    private String TvsourceDisplayName;

    @OneToOne(fetch=FetchType.LAZY)
    @JoinColumn(name = "SourceId")
    private RCMSource rcmSource;

    @Override
    public String toString() {
        return "MyTelevisionSource [SourceId=" + SourceId + ", TvsourceLongName=" + TvsourceLongName
                + ", TvsourceDisplayName=" + TvsourceDisplayName + ", rcmSource=" + rcmSource + "]";
    }

}

@Getter
@Setter
@ToString
@Entity
@Table(name = "Source")
public class RCMSource {

    @Id
    private Long SourceId;

    @Column(columnDefinition = "nvarchar2 (2000)")
    private String SourceName;

}


Service layer
***************

@Service
public class TelevisionSourceService {

    @Autowired
    private TelevisionSourceRepository televisionSourceRepo;

    public List<MyTelevisionSource> getTelevisionSource(){
        Pageable pageable = PageRequest.of(0, 10);
        Page<MyTelevisionSource> tvSource =  televisionSourceRepo.findAll(pageable);

        List<MyTelevisionSource> sourceList= tvSource.getContent();

        sourceList.forEach(System.out::println);

        return sourceList;

    }

}


Repo
****
public interface TelevisionSourceRepository extends JpaRepository<MyTelevisionSource, Long> {
    Page<MyTelevisionSource> findAll(Pageable pageable);

}

and also i tried by writing @Query for the same, but it is also making 11 queries,

@Query("select t from MyTelevisionSource t inner join RCMSource s ON s.SourceId = t.SourceId")
    Page<MyTelevisionSource> findAll(Pageable pageable);

without using pageable, is there any other way to fetch top 10 records, by joining two tables

Hibernate:

select
    TOP(?) mytelevisi0_.SourceId as SourceId1_1_,
    mytelevisi0_.TvsourceDisplayName as Tvsource2_1_,
    mytelevisi0_.TvsourceLongName as Tvsource3_1_ 
from
    dbo.TelevisionSource mytelevisi0_ 
inner join
    dbo.Source rcmsource1_ 
        on (
            rcmsource1_.SourceId=mytelevisi0_.SourceId
        )

Hibernate:

select
    rcmsource0_.SourceId as SourceId1_0_0_,
    rcmsource0_.SourceName as SourceNa2_0_0_ 
from
    dbo.Source rcmsource0_ 
where
    rcmsource0_.SourceId=?

Hibernate:

select
    rcmsource0_.SourceId as SourceId1_0_0_,
    rcmsource0_.SourceName as SourceNa2_0_0_ 
from
    dbo.Source rcmsource0_ 
where
    rcmsource0_.SourceId=?

Hibernate:

select
    rcmsource0_.SourceId as SourceId1_0_0_,
    rcmsource0_.SourceName as SourceNa2_0_0_ 
from
    dbo.Source rcmsource0_ 
where
    rcmsource0_.SourceId=?

Hibernate:

select
    rcmsource0_.SourceId as SourceId1_0_0_,
    rcmsource0_.SourceName as SourceNa2_0_0_ 
from
    dbo.Source rcmsource0_ 
where
    rcmsource0_.SourceId=?

Hibernate:

select
    rcmsource0_.SourceId as SourceId1_0_0_,
    rcmsource0_.SourceName as SourceNa2_0_0_ 
from
    dbo.Source rcmsource0_ 
where
    rcmsource0_.SourceId=?

Hibernate:

select
    rcmsource0_.SourceId as SourceId1_0_0_,
    rcmsource0_.SourceName as SourceNa2_0_0_ 
from
    dbo.Source rcmsource0_ 
where
    rcmsource0_.SourceId=?

Hibernate:

select
    rcmsource0_.SourceId as SourceId1_0_0_,
    rcmsource0_.SourceName as SourceNa2_0_0_ 
from
    dbo.Source rcmsource0_ 
where
    rcmsource0_.SourceId=?

Hibernate:

select
    rcmsource0_.SourceId as SourceId1_0_0_,
    rcmsource0_.SourceName as SourceNa2_0_0_ 
from
    dbo.Source rcmsource0_ 
where
    rcmsource0_.SourceId=?

Hibernate:

select
    rcmsource0_.SourceId as SourceId1_0_0_,
    rcmsource0_.SourceName as SourceNa2_0_0_ 
from
    dbo.Source rcmsource0_ 
where
    rcmsource0_.SourceId=?

Hibernate:

select
    rcmsource0_.SourceId as SourceId1_0_0_,
    rcmsource0_.SourceName as SourceNa2_0_0_ 
from
    dbo.Source rcmsource0_ 
where
    rcmsource0_.SourceId=?

Hibernate:

select
    count(mytelevisi0_.SourceId) as col_0_0_ 
from
    dbo.TelevisionSource mytelevisi0_ 
inner join
    dbo.Source rcmsource1_ 
        on (
            rcmsource1_.SourceId=mytelevisi0_.SourceId
        )

vishnu · Answer 1 · 2019-07-24T08:07:22.660

0

Since your toString() method of MyTelevisionSource entity have the RCMSource, it will make a query to get the object.

You can use fetch in your query to avoid this call

@Query("select t from MyTelevisionSource t join fetch t.rcmSource s ON s.SourceId = t.SourceId",
countQuery = "select count(t) from MyTelevisionSource t join t.rcmSource s ON s.SourceId = t.SourceId")
Page<MyTelevisionSource> findAll(Pageable pageable);

edited Jul 24 '19 at 08:07

answered Jul 24 '19 at 04:48

vishnu

1,961
2
7
11

this also creating 11 queries, – mohan Jul 24 '19 at 04:57
Can you provide some additional information on the query – vishnu Jul 24 '19 at 06:50
i have pasted the queries in my question – mohan Jul 24 '19 at 07:56

score 0 · Answer 2 · answered Jul 24 '19 at 06:14

0

Of course it produces 11 queries - one query for the initial load and each one query for each lazily initialized @OneToOne relationship you access in toString. You can reduce the number of queries by fetching the relationship EAGER (which is its actual default) but which might produce additional side effects later, so please be aware:

@OneToOne
@JoinColumn(name = "SourceId")
private RCMSource rcmSource;

answered Jul 24 '19 at 06:14

Smutje

17,733
4
24
41

i tried that also, still it is producing 11 queries. As i am using pageable, is this making that issue. How can fetch 10 records without using pageable?? I tried Limit and rowNum, both are not allowed in @Query – mohan Jul 24 '19 at 06:28
Please append the actual 11 queries to your question so we can see which one might be superfluous. – Smutje Jul 24 '19 at 06:33
check, i have added in my question – mohan Jul 24 '19 at 06:41
Are these the same 11 queries when having `EAGER` loading set? – Smutje Jul 24 '19 at 06:43
OK, this seems a Spring Data related problem, please check out https://stackoverflow.com/questions/29602386/how-does-the-fetchmode-work-in-spring-data-jpa – Smutje Jul 24 '19 at 08:01
what should be the next approach? – mohan Jul 24 '19 at 08:11

One to One mapping is creating multiple queries

2 Answers2