How to customize sql？

[usernameistom]

We used custom SQL, but the performance is worse than FindAll, can't use custom SQL?

    SqlQuerySpec sqlQuerySpec = new SqlQuerySpec();
    sqlQuerySpec.setQueryText("Select * from c");
    FeedResponse<Document> response = executeQuery(sqlQuerySpec, feedOptions, collectionName, database);

    Iterator<Document> it = response.getQueryIterator();

[Incarnation-p-lee]

@usernameistom Thanks for you issue. I may not get your point about the issue. You mean you code in above like SELECT * FROM C with sync cosmosdb-java-sdk get lower performance result comparing to findAll ?

[usernameistom]

I used custom SQL to solve the collection property query, so I tested the performance first. In order to make the test environment consistent, I got the DocumentClient in SpringBean, not using sync cosmosdb-java-sdk

public static DocumentClient getDocumentClient() {
    return (DocumentClient) SpringContextUtils.getBean("documentClient");
}
public static <T> Page<T> executePageQuery(Pageable pageable, Class<T> domainClass, String collectionName, String sql,String countsql, String database) {

    FeedOptions feedOptions = new FeedOptions();
    if (pageable instanceof DocumentDbPageRequest) {
        feedOptions.setRequestContinuation(((DocumentDbPageRequest) pageable).getRequestContinuation());
    }
    feedOptions.setPageSize(pageable.getPageSize());

    SqlQuerySpec sqlQuerySpec = new SqlQuerySpec();
    sqlQuerySpec.setQueryText(sql); //customize sql like SELECT * FROM C
    FeedResponse<Document> response = executeQuery(sqlQuerySpec, feedOptions, collectionName, database);

    Iterator<Document> it = response.getQueryIterator();

    List<T> result = new ArrayList<>();
    for (int index = 0; it.hasNext() && index < pageable.getPageSize(); index++) {
        // Limit iterator as inner iterator will automatically fetch the next page
        Document doc = it.next();
        if (doc == null) {
            continue;
        }
        T entity = getMappingDocumentDbConverter().read(domainClass, doc);
        result.add(entity);
    }
    DocumentDbPageRequest pageRequest = DocumentDbPageRequest.of(pageable.getPageNumber(),
            pageable.getPageSize(),
            response.getResponseContinuation());

    return new PageImpl<>(result, pageRequest, getCountValue(countsql, feedOptions, collectionName, database));
}

[usernameistom]

There is another problem here. We can't query the previous page. How can we implement the third page?

[Incarnation-p-lee]

@usernameistom From our repo's perspective, we do not recommend user use native sql, which looks almost the same way like leverage sync sdk directly instead of our repo.

But if you mean something like @Query, and it is not supported yet.

Finally, about the page issue. You mean if you stated in the middle of pages, and want to query the previous page ? As I know, it looks like a stream but cannot query previous page.

@sophiaso Could you please help to confirm the behavior about paging ? Thanks a lot.

[sophiaso]

@usernameistom We don't support query the previous page, currently if you need to query the third page, you have to query the first, then the second, then the third. For how to query, the integration test may help.

[usernameistom]

I use custom SQL, can solve the query by collection attribute, the third page query, but the performance is a bit bad. I don't know which direction to optimize, can you give some advice?

[sophiaso]

@usernameistom I'm not sure what kind of collection attribute and custom query you are using to do the third page query. If it's customized sql query with page number specified, is the continuation token still required in your FeedOptions? As you shared in your previous comment.

[usernameistom]

I used the “TOP” keyword and "order by c. _ts desc", not FeedOptions.

[sophiaso]

Not sure whether cosmosdb index will help in your case.

[usernameistom]

I increased the RU and put the WebApp and DB in an area to solve the current performance problems. Thank you for your support.

[kushagraThapar]

@usernameistom - Closing this issue as this is being tracked in @Query annotation feature.