Error in Query Entity due to large data in Database

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).

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.

Chris said:

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.

Chris said:

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..

Chris said:

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.

Mike Schmitt said:

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.