Error in Query Entity due to large data in Database

Certified Senior Developer

Hi All,

I tried to fetch data from Database using queryEntity in expression rule.

But due to large data in database(6278),it shows me the below error.

Could you please help me to sort this issue.

Thanks in advance.

  Discussion posts and replies are publicly visible

    1.  That error message is generic and there are other causes. Are you sure it's related to the volume of data?
    2. If it is, then you can use the paging capability to fetch the data in smaller batches. Setting the 'batchSize' to -1 will indeed attempt to fetch all of the data, and you can mitigate this by adding one or more filters, and/or change the 'batchSize' to a number like 10, or 50, or whatever makes sense in the context of your solution
  • As an example for Stewart's #2, this is typically how I will batch from a!queryEntity() when I need to return a data set larger than a single query can handle:

    a!localVariables(
      local!batchSize: 3000,
      local!totalCount: 
      a!queryEntity(
        entity: cons!YOUR_DS_HERE,
        fetchtotalcount: true,
        query: a!query(
          pagingInfo: a!pagingInfo(1,1)
        )
      ).totalCount,
      local!batchCount: ceiling(local!totalCount/local!batchSize),
    
      a!flatten(
        a!forEach(
          items: 1+enumerate(local!batchCount),
          expression: a!localVariables(
            local!startIndex: fv!item+((fv!index-1)*local!batchSize)-(fv!index-1),
            a!queryEntity(
              entity: cons!YOUR_DS_HERE,
              fetchtotalcount: false,
              query: a!query(
                pagingInfo: a!pagingInfo(local!startIndex,local!batchSize)
              )
            ).data
          )
        )
      )
    )

    With average amounts of columns I can typically batch 5000 rows at a time, however if your column count grows you may have to lessen this value since the maximum is related to data size, not row count.

    This example is designed for a database view which contains the necessary filters built in, however you can add your filters to the a!query() calls on lines 7 and 21 (add the same filter sets for each).

  • 0
    Certified Lead Developer
    in reply to Chris

    Interesting example. There is a pretty hard limit on how much memory an expression can eat up. I highly recommend to never try to load "EVERYTHING" into memory. It will break sooner or later. Rethink your design.

  • Agree to avoid this scenario whenever possible (loading ALL data into memory)! 

    In my use case, this builds a CDT to provide a daily Excel export for executive leadership which needs to contain 30k rows.  Since the Excel file must contain formatting (charts), we are prevented from utilizing the Export DSE to Excel service and unfortunately (until I have another way), using the deprecated Export CDT to Excel service.

  • 0
    Certified Lead Developer
    in reply to Chris
    Since the Excel file must contain formatting (charts), we are prevented from utilizing the Export DSE to Excel service

    Interestingly, in 21.4 they've apparently upgraded Export DSE to Excel such that it no longer overwrites formatting (to some extent at least).  Mind bogglingly, they seem to still have missed the issue that there's no direct way to run it against a standard template file without overwriting that template file, minus using additional process nodes (and a plug-in, afaik) to make a new separate copy of it first.  I have no idea why this still isn't built-in to the node since it's how the deprecated Excel Tools nodes worked as well as OOB tools like Docx from Template and HTML Doc from Template.

  • Did not know they updated, but yea, not quite there yet!  Also note that the documentation states:

    Any cells above the Starting Cell will now maintain the formatting from the original spreadsheet, including alignment, font, border, fill, and more.

    So, guess any cells we export into (or below?) will still clear formatting..  Not useful enough for many of my cases yet..

  • 0
    Certified Lead Developer
    in reply to Chris
    So, guess any cells we export into (or below?) will still clear formatting

    Yeah, that's why I slipped in "to some extent"... though doesn't the Excel Tools function also act somewhat destructively to cells that get written into?  Disclaimer, I haven't tried in a while.

  • Yeah, that's why I slipped in "to some extent"... though doesn't the Excel Tools function also act somewhat destructively to cells that get written into?  Disclaimer, I haven't tried in a while.

    I haven't run into any issues with that [deprecated] Export CDT to Excel node removing formatting - works great!  Alignment, borders, shading, etc all stay - the node works in a true data-only, retain-destination-formatting fashion.  We generate some nice, clean, styled reports for executives with it.  Including excel templates that have pre-defined bar and pie charts, exports fill certain cells to drive the charts etc.