Bug:Speedment does not generate proper queries in joins while applying count and sort

[prabha05081997]

Count

Join<Tuple2<VkycOkycTransactionIdMapping, User>> userDetailsJoin = getJoinComponent().from(VkycOkycTransactionIdMappingManager.IDENTIFIER)
.where(VkycOkycTransactionIdMapping.ENTERPRISE_ID.equal(enterpriseId))
.innerJoinOn(User.CLIENT_PARTNER_USER_ID).equal(VkycOkycTransactionIdMapping.PARTNER_UNIQUE_ID)
.build(Tuples::of);
Join<Tuple2<VkycOkycTransactionIdMapping, User>> userDetailsJoin
= getUserDetailsFromEnterpriseIdTupleJoin(enterpriseId);
long count = userDetailsJoin.stream()
.collect(Collectors.counting());

Should generate a query equivalent to

SELECT count(*) FROM digitap.vkyc_okyc_transaction_id_mapping AS A INNER JOIN digitap.user AS B ON (B.client_partner_user_id = A.partner_unique_id) WHERE (A.enterprise_id = 1);

But generated query is

SELECT A.id,A.ent_user_log_subscription_service_mapping_id,A.partner_unique_id,A.status,A.enterprise_id,A.url,A.skip_okyc,A.created_on,A.updated_on, B.id,B.mobile,B.email,B.password,B.fname,B.lname,B.palgo,B.salt,B.type,B.createdOn,B.updatedOn,B.partner_user_id,B.client_partner_user_id FROM digitap.vkyc_okyc_transaction_id_mapping AS A INNER JOIN digitap.user AS B ON (B.client_partner_user_id = A.partner_unique_id) WHERE (A.enterprise_id = ?) [Integer 1]

Which results in getting entire data from database to java layer and treats it has a collection and then stream the collection to find the count which results in huge performance impact

Order by and Limit

Join<Tuple2<VkycOkycTransactionIdMapping, User>> userDetailsJoin = getJoinComponent().from(VkycOkycTransactionIdMappingManager.IDENTIFIER)
.where(VkycOkycTransactionIdMapping.ENTERPRISE_ID.equal(enterpriseId))
.innerJoinOn(User.CLIENT_PARTNER_USER_ID).equal(VkycOkycTransactionIdMapping.PARTNER_UNIQUE_ID)
.where(User.MOBILE.contains(mobile))
.build(Tuples::of);
Comparator<Tuple2<VkycOkycTransactionIdMapping, User>> comparator = User.FNAME.asString().compose(Tuple2.getter0());
List userDetailsList = userDetailsTupleJoin.stream()
.sorted(genericComparator)
.skip(0)
.limit(25)
.map(UserDetails::new)
.collect(Collectors.toList());

Should have created

SELECT A.id,A.ent_user_log_subscription_service_mapping_id,A.partner_unique_id,A.status,A.enterprise_id,A.url,A.skip_okyc,A.created_on,A.updated_on, B.id,B.mobile,B.email,B.password,B.fname,B.lname,B.palgo,B.salt,B.type,B.createdOn,B.updatedOn,B.partner_user_id,B.client_partner_user_id FROM digitap.vkyc_okyc_transaction_id_mapping AS A INNER JOIN digitap.user AS B ON (B.client_partner_user_id = A.partner_unique_id) WHERE (A.enterprise_id = ?) [Integer 1] order by B.fname limit 0,25;

But generating

SELECT A.id,A.ent_user_log_subscription_service_mapping_id,A.partner_unique_id,A.status,A.enterprise_id,A.url,A.skip_okyc,A.created_on,A.updated_on, B.id,B.mobile,B.email,B.password,B.fname,B.lname,B.palgo,B.salt,B.type,B.createdOn,B.updatedOn,B.partner_user_id,B.client_partner_user_id FROM digitap.vkyc_okyc_transaction_id_mapping AS A INNER JOIN digitap.user AS B ON (B.client_partner_user_id = A.partner_unique_id) WHERE (A.enterprise_id = ?) [Integer 1]

Without order by and limit clause again this results in a major impact, it selects all the rows from the database and applies sort on and group by on top of it

[minborg]

Speedment is not able to optimize all queries under all conditions. Which version are you using?

[minborg]

I know there have been updates to the rendering of joined streams in some of the recent versions. Are you able to test the most recent version 3.2.10?

[prabha05081997]

Hi minborg,
We've tried with version 3.2.10, but still, the issue is not yet resolved.
Basically, we're trying to achieve pagination, so as the number of records grows getting count in an above-mentioned way could be a huge performance impact for us.
can you please take this as a high priority?

[minborg]

Please send a mail to info@speedment.com and reference this issue and we will get back to you.