• State Suggested Answer
  • Replies 13 replies
  • Answers 4 answers
  • Subscribers 14 subscribers
  • Views 11157 views
  • Users 0 members are here
Related Discussions
Home » Discussions » General

Returning large data from query in small batch

joh4

Hello, looking for some help.

 

I have a requirement to query a database for a customer that has over 7,000 rows. 

When I run the query, to return 7000 rows, it fails (obviously too much)

Is there a way to query for the 7000 rows but only query and load 15 or so at a time? And when the user clicks the next arrow on the paging grid, it only loads 15-30? So that the interface isn't overloaded?

They aren't going to page through 7,000 records, 15 at a time, but the requirements still want to be able to have an overall customer view.

Thanks,

  Discussion posts and replies are publicly visible

Parents
  • 0
    Create a query entity rule like
    a!queryEntity(
    entity: cons!TEST_ENTITY,
    query:
    a!query(
    filters:
    {
    Filters you want to add
    },
    pagingInfo:ri!!pagingInfo
    )
    )

    Create a new rule in which we will iterate the loop according to the total count of the datasubset

    with(
    local!batchSize: 1000,
    local!filters: a!fromJson(
    ri!input
    ),
    local!pagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: - 1
    ),
    local!iterations: roundup(
    cast(
    typeof(
    1
    ),
    local!filters.totalCount
    ) / local!batchSize,
    0
    ),
    local!dt: apply(
    rule!TestReportViewDataOnly(
    pagingInfo: _,
    filters: local!filters
    ),
    apply(
    topagingInfo(
    _,
    local!batchSize
    ),
    1 + enumerate(
    local!iterations
    ) * local!batchSize
    )
    ),
    .
    .
    .
    .
    )

    I hope this will help you
  • 0
    Certified Senior Developer
     in reply to Arpit Bhargava

    Hi Arpit,

    Thanks for sharing a rule to call the query enity recursively with variable batchsize. But the problem is that it is return as list of datasubset as output. For example

    Suppose if there are 790 records and batchsize is 100, we are getting an single output as  list of 8 datasubsets. How to merge the output of 8 datasubset into 1 datasubset. Tried many functions but not able to merge the data at the end.

    Actual output is

    local!datasubset1 - List of 8 datasubset with batchsize of 100

    Expecting an output as

    local!datasubset1 - List of 1 datasubset with 790 rows

    Thanks in advance for reply.

Reply
  • 0
    Certified Senior Developer
     in reply to Arpit Bhargava

    Hi Arpit,

    Thanks for sharing a rule to call the query enity recursively with variable batchsize. But the problem is that it is return as list of datasubset as output. For example

    Suppose if there are 790 records and batchsize is 100, we are getting an single output as  list of 8 datasubsets. How to merge the output of 8 datasubset into 1 datasubset. Tried many functions but not able to merge the data at the end.

    Actual output is

    local!datasubset1 - List of 8 datasubset with batchsize of 100

    Expecting an output as

    local!datasubset1 - List of 1 datasubset with 790 rows

    Thanks in advance for reply.

Children
© 2024 Appian. All rights reserved.
We want to hear from you! Submit a review on Gartner or G2.