invantive documentation
Forums d'Invantive SupportConnectez-vous/inscrivez-vous

AFAS documenten: itgenclr007 OutOfMemoryException

Les réponses sont publiées sur le forum.

Aller à la source

Question

Een klant van mij heeft de onderstaande foutmelding op 1 tabel. Ze proberen documenten te downloaden met dit SQL-script:

-- In case any document to which access seems impossible: ignore it.
--
-- Raises 404 Not Found when not accessible:
--
-- The requested OData URL could not be found on the AFAS Online server.
-- Please contact Invantive Support or use another table.
-- (https://93926.rest.afas.online/profitrestservices/subjectconnector/9520/90b21cfe240a4213805a164886ba6927?take=50000).
--
-- The 404 can not be answered from document cache on Subject connector, so it will consume
-- approximately 125 ms for each row on every run.
--
set ignore-http-404-errors@afas true
2026-10-13 04:59:43.017 Information itgensql264: Invantive UniversalSQL statement started.
2026-10-13 04:59:43.035 Information itgendhb017: 1 Rows Affected (19 ms).
2026-10-13 04:59:43.035 Information itgendhb050: Creation date: 13-10-2026 04:59:43 (UTC)
2026-10-13 04:59:43.035 Information itgendhb053: Fill CPU time (ms):
2026-10-13 04:59:43.035 Information itgendhb056: Fingerprint:
2026-10-13 04:59:43.035 Information itgendhb057: SQL statement: --
-- In case any document to which access seems impossible: ignore it.
--
-- Raises 404 Not Found when not accessible:
--
-- The requested OData URL could not be found on the AFAS Online server.
-- Please contact Invantive Support or use another table.
-- (https://93926.rest.afas.online/profitrestservices/subjectconnector/9520/90b21cfe240a4213805a164886ba6927?take=50000).
--
-- The 404 can not be answered from document cache on Subject connector, so it will consume
-- approximately 125 ms for each row on every run.
--
set ignore-http-404-errors@afas true
2026-10-13 04:59:43.035 Information itgendhb058: Parameter values:
2026-10-13 04:59:43.035 Information itgendhb059: User full name:
2026-10-13 04:59:43.035 Information itgendhb060: User email address:
2026-10-13 04:59:43.035 Information itgendhb061: User log on code:
2026-10-13 04:59:43.035 Information itgendhb063: Active data container provider name: Invantive SQL 25.0.95-PROD+5629
2026-10-13 04:59:43.035 Information itgendhb064: Execution steps: 0
2026-10-13 04:59:43.035 Information itgendhb044: Run SQL statement: create or replace table tstdoc@sql
as
select m.attachment_id
,      m.subject_id
,      m.file_id
,      m.file_name
,      s.filedata
,      s.mimetype
from   AfasOnline.GetConnector.profit_subject_attachments@afas m
join   Subject@afas(m.subject_id, m.file_id) s
--
-- Possibly forwardable server-side filter.
--
where  m.file_name like 'I________.pdf'
--
-- Fine-tuned check that the invoice file name pattern is matched.
--
and    regexp_instr(m.file_name, '^I[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9].pdf$') = 1
2026-10-13 04:59:43.036 Information itgensql264: Invantive UniversalSQL statement started.
2026-10-13 05:17:34.925 Warning itgendhb173: Context: c:\jobs\sql\repl.sql: create or replace table tstdoc@sql
as
select m.attachment_id...le_name, '^I[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9].pdf$') = 1
2026-10-13 05:17:53.255 Error itgencun016: Error itgenclr007: External error in Invantive Data Hub.

Exception of type 'System.OutOfMemoryException' was thrown.
2026-10-13 05:17:55.405 Error itgenclr007: System.OutOfMemoryException
ValidationException
   at Invantive.Data.ParallelSelectManyEnumerator`2.System.Collections.IEnumerator.MoveNext()
   at Invantive.Sql.V1.DataSourceOrFunctionWithRotatorTree.GetDataEnumerator(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, SparseArrayPool sparseArrayPool, QueryObject queryObject, ParameterList parameters)+MoveNext()
   at Invantive.Data.CompressedEnumerable`1.GetEnumerator()+MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.Iterator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SparseArrayPool sparseArrayPool, SqlExecutionStep parentStep, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.GetDataEnumerator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SqlExecutionStep parentStep, SparseArrayPool sparseArrayPool, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Data.CompressedEnumerable`1.GetEnumerator()+MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.Iterator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SparseArrayPool sparseArrayPool, SqlExecutionStep parentStep, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Sql.V1.CleanSparseArrayIterator.Iterator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SparseArrayPool sparseArrayPool, SqlExecutionStep parentStep, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.GetDataEnumerator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SqlExecutionStep parentStep, SparseArrayPool sparseArrayPool, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Data.CompressedEnumerable`1.GetEnumerator()+MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.Iterator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SparseArrayPool sparseArrayPool, SqlExecutionStep parentStep, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.GetDataEnumerator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SqlExecutionStep parentStep, SparseArrayPool sparseArrayPool, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Data.ExtensionMethods.IteratorWithFinally`1.IteratorWithFinallyEnumerator.MoveNext()
   at Invantive.Data.ExtensionMethods.CacheFirstEnumerable`1.GetEnumerator()+MoveNext()
   at Invantive.Sql.V1.JoinIterator.InnerLeftRightOuterJoinRegular(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, IQueryStatePerExecution queryStatePerExecution, SparseArrayPool sparseArrayPool, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow, IEnumerable`1 outerRows, IEnumerable`1 innerRows, Boolean leftIsOuter, Boolean isRhsTableFunctionOrNestedSql, SparseArray emptyInnerRow, Int64 outerRowsCount, JoinStatistics joinStatistics)+MoveNext()
   at Invantive.Sql.V1.JoinIterator.InnerLeftRightOuterJoin(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, IQueryStatePerExecution queryStatePerExecution, SparseArrayPool sparseArrayPool, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow, JoinStatistics joinStatistics, Boolean isRhsTableFunctionOrNestedSql)+MoveNext()
   at Invantive.Sql.V1.JoinIterator.JoinInternal(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SqlExecutionStep parentStep, SparseArrayPool sparseArrayPool, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Sql.V1.JoinIterator.Iterator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SparseArrayPool sparseArrayPool, SqlExecutionStep parentStep, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.GetDataEnumerator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SqlExecutionStep parentStep, SparseArrayPool sparseArrayPool, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Sql.V1.FilterIterator.Iterator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SparseArrayPool sparseArrayPool, SqlExecutionStep parentStep, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.GetDataEnumerator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SqlExecutionStep parentStep, SparseArrayPool sparseArrayPool, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Sql.V1.ChainedFirehose.Iterator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SparseArrayPool sparseArrayPool, SqlExecutionStep parentStep, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.GetDataEnumerator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SqlExecutionStep parentStep, SparseArrayPool sparseArrayPool, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Sql.V1.SelectListIterator.Iterator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SparseArrayPool sparseArrayPool, SqlExecutionStep parentStep, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.GetDataEnumerator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SqlExecutionStep parentStep, SparseArrayPool sparseArrayPool, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Data.CompressedEnumerable`1.GetEnumerator()+MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.Iterator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SparseArrayPool sparseArrayPool, SqlExecutionStep parentStep, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Sql.V1.MaterializeSparseArrayIterator.Iterator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SparseArrayPool sparseArrayPool, SqlExecutionStep parentStep, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Data.ResultSet..ctor(GlobalState owner, ExecutionOptions executionOptions, IProviderManager manager, String originCode, Nullable`1 queryTreeType, IColumnDefinitionCollection fields, IEnumerable`1 rows, ResultSetAuditInformation auditInformation)
   at Invantive.Sql.V1.DdlStatementTree.CreateTable(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, IQueryStatePerExecution queryStatePerExecution, SparseArrayPool sparseArrayPool, FirehoseResultSet results, IDdlProvider ddlProvider, Int64 rowsAffectedStatement, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow, Nullable`1 maxBatchSizeRows)+MoveNext()
   at Invantive.Data.CompressedEnumerable`1.GetEnumerator()+MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.Iterator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SparseArrayPool sparseArrayPool, SqlExecutionStep parentStep, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Sql.V1.QueryPlan.CorrectAndCheckData(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, SparseArrayPool sparseArrayPool, IEnumerable`1 data, String sqlStatement)+MoveNext()
   at Invantive.Data.ExtensionMethods.IteratorWithFinally`1.IteratorWithFinallyEnumerator.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Invantive.Data.ConnectionManager.ExecuteAndCombineQueryPlansV1(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, QueryPlan[] queryPlans, String sqlStatement, ParameterList parameters)
   at Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, SqlExecutionStep sqlExecutionStep, String callSafeNameOverrule)
--- End of stack trace from previous location ---
   at Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, SqlExecutionStep sqlExecutionStep, String callSafeNameOverrule)
   at Invantive.Data.ConnectionManager.b(GlobalState owner, ExecutionOptions, SqlExecutionStep, String actionSql, ParameterList, String)
--- End of stack trace from previous location ---
   at Invantive.Data.ConnectionManager.b(GlobalState owner, ExecutionOptions, SqlExecutionStep, String actionSql, ParameterList, String)
   at Invantive.Data.ConnectionManager.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
   at Invantive.Producer.Data.Hub.QueryEngine.ProcessStatement(GlobalState owner, ExecutionOptions executionOptions, String statement, ResultSet& resultSet, Action`3 fetchMoreRows, Action`9 executeStatement, Boolean isInInteractiveMode, ScriptingLanguageNextStepSpecification& nextStepSpecification) in c:\smoke\ws-25.0\Invantive.Data.Hub\src\Invantive.Producer.Data.Hub\QueryEngine.cs:line 2582
   at Invantive.Producer.Data.Hub.QueryEngine.ProcessStatements(GlobalState owner, ExecutionOptions executionOptions, Action`3 fetchMoreRows, Action`9 executeStatement, IEnumerable`1 statements, String sourceFileName, ScriptingLanguageNextStepSpecification& exitSpecification) in c:\smoke\ws-25.0\Invantive.Data.Hub\src\Invantive.Producer.Data.Hub\QueryEngine.cs:line 1582
2026-10-13 05:17:56.076 Information itgenqtl137: Exit program. (0)
2026-10-13 05:17:56.218 Information itgendhb105: Executed statement to exit application.

Réponse acceptée

Une analyse a permis de formuler l'hypothèse suivante.

L'create or replace table, sans l'bulk insert, et sur l'ensemble du jeu de données, nécessitera environ 8 ou 16 Go de mémoire de travail sous la version 25.0. Cela ne tiendra probablement pas dans la mémoire vive.

Le document le plus volumineux pèse environ 41 MB.

Il est recommandé de passer à la version 27.0. Celle-ci intègre des optimisations permettant de traiter le streaming du jeu de données même avec create or replace. Cela réduit considérablement la pression sur la mémoire.

Paginagrootte van de AFAS-driver heeft geen invloed op het probleem; paginagrootte is alleen relevant aan de zijde richting AFAS en de Subject-connector gaat per bestand.

Solutions de contournement :

  • Doublez la mémoire vive du serveur (solution temporaire).
  • Utilisez un algorithme incrémental.

Répondre 6

Cette question a été automatiquement clôturée après 1 semaine d'inactivité. La dernière réponse fournie a été marquée comme solution.

Veuillez poser une nouvelle question dans un sujet distinct si le problème se reproduit. Veuillez inclure un lien vers ce sujet dans votre nouvelle question en copiant-collant son URL dans le texte.

Répondre 2

Il se peut que 1 des documents soit très volumineux. In totaal wordt grofweg 7,6 GB aan JSON-data verwerkt.

Quelques questions d'approfondissement :

De plus, la taille de la page semble être réglée sur une valeur trop élevée.

  • A-t-elle été modifiée manuellement ?
  • Quelles valeurs sont utilisées ?
  • Est-ce que cela permet de réduire la valeur ?

Répondre 3

  • Version : 25.0.95-PROD+5630, 64-bits
  • Alleen Invantive Data Hub.
  • Le message d'erreur est apparu aujourd'hui

À ma connaissance, la taille des pages n’a pas été modifiée.

Où pourrais-je éventuellement modifier cela ?

Répondre 4

Meestal via een set requested-page-size SQL-instructie aan het begin van het script.

Hoewel er niet veel is veranderd aan de AFAS-implementatie, is er veel werk verricht aan de SQL-engine, waaronder veel geheugenoptimalisaties. Vous pourriez effectuer un test avec la dernière version, disponible sur https://download.invantive.eu.

Répondre sur le forum