Log in to start a topic

Dataset is executed twice if used in aggregate function

Posted by: jharrell on 15 May 2018, 2:36 am EST

  • jharrell

    Posted 15 May 2018, 2:36 am EST

    This is not something that used to occur but we just noticed recently in both AR11 and AR12.

    If a field from a dataset is used in a aggregate function the dataset is executed twice. This causes extra load on the server but even worse if the query modifies data it can cause undesirable results.

    It seem it is executed first on the thread that calls execute and as part of the rendering extension it is executed a second time on a background thread.

    If the field is just bound say in a table the dataset only executes once as expected.

    I have a attached a test rdlx that reproduces the issue, it calls waitfor in Sql server to simulate a long running query, its set for 5 seconds but the report will take 10 seconds to execute due to two executes. You can watch sql serve profiler to see two separate calls from the client for the exact same sql statement.

    Here are the stack traces I was able to capture showing executes happening in two separate places:

    System.Data.dll!SNINativeMethodWrapper.SNIReadSyncOverAsync(System.Runtime.InteropServices.SafeHandle pConn, ref System.IntPtr packet, int timeout) Unknown Non-user code. Skipped loading symbols.

    System.Data.dll!System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync() Unknown Non-user code. Skipped loading symbols.

    System.Data.dll!System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket() Unknown Non-user code. Skipped loading symbols.

    System.Data.dll!System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer() Unknown Non-user code. Skipped loading symbols.

    System.Data.dll!System.Data.SqlClient.TdsParserStateObject.TryReadByte(out byte value) Unknown Non-user code. Skipped loading symbols.

    System.Data.dll!System.Data.SqlClient.TdsParser.TryRun(System.Data.SqlClient.RunBehavior runBehavior, System.Data.SqlClient.SqlCommand cmdHandler, System.Data.SqlClient.SqlDataReader dataStream, System.Data.SqlClient.BulkCopySimpleResultSet bulkCopyHandler, System.Data.SqlClient.TdsParserStateObject stateObj, out bool dataReady) Unknown Non-user code. Skipped loading symbols.

    System.Data.dll!System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() Unknown Non-user code. Skipped loading symbols.

    System.Data.dll!System.Data.SqlClient.SqlDataReader.MetaData.get() Unknown Non-user code. Skipped loading symbols.

    System.Data.dll!System.Data.SqlClient.SqlCommand.FinishExecuteReader(System.Data.SqlClient.SqlDataReader ds, System.Data.SqlClient.RunBehavior runBehavior, string resetOptionsString, bool isInternal, bool forDescribeParameterEncryption, bool shouldCacheForAlwaysEncrypted) Unknown Non-user code. Skipped loading symbols.

    System.Data.dll!System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(System.Data.CommandBehavior cmdBehavior, System.Data.SqlClient.RunBehavior runBehavior, bool returnStream, bool async, int timeout, out System.Threading.Tasks.Task task, bool asyncWrite, bool inRetry, System.Data.SqlClient.SqlDataReader ds, bool describeParameterEncryptionRequest) Unknown Non-user code. Skipped loading symbols.

    System.Data.dll!System.Data.SqlClient.SqlCommand.RunExecuteReader(System.Data.CommandBehavior cmdBehavior, System.Data.SqlClient.RunBehavior runBehavior, bool returnStream, string method, System.Threading.Tasks.TaskCompletionSource completion, int timeout, out System.Threading.Tasks.Task task, out bool usedCache, bool asyncWrite, bool inRetry) Unknown Non-user code. Skipped loading symbols.

    System.Data.dll!System.Data.SqlClient.SqlCommand.RunExecuteReader(System.Data.CommandBehavior cmdBehavior, System.Data.SqlClient.RunBehavior runBehavior, bool returnStream, string method) Unknown Non-user code. Skipped loading symbols.

    System.Data.dll!System.Data.SqlClient.SqlCommand.ExecuteReader(System.Data.CommandBehavior behavior, string method) Unknown Non-user code. Skipped loading symbols.

    System.Data.dll!System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(System.Data.CommandBehavior behavior) Unknown Non-user code. Skipped loading symbols.

    System.Data.dll!System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() Unknown Non-user code. Skipped loading symbols.

    GrapeCity.ActiveReports.v12.dll!GrapeCity.BI.Data.DataProviders.SysCommandAdapter.ExecuteReader() Unknown Non-user code. Skipped loading symbols.

    GrapeCity.ActiveReports.v12.dll!#UwA.#uue.ExecuteReader(GrapeCity.ActiveReports.Extensibility.Data.IDbCommand command) Unknown Non-user code. Skipped loading symbols.

    GrapeCity.ActiveReports.v12.dll!GrapeCity.BI.Data.RecordScanner.RecordSet.d__5.MoveNext() Unknown Non-user code. Skipped loading symbols.

    GrapeCity.ActiveReports.v12.dll!GrapeCity.Enterprise.Data.DataEngine.Extensions.EnumerableExtensions.MemoizedBuffer<GrapeCity.Enterprise.Data.DataEngine.Record>.#7kY(out bool hasValue) Unknown Non-user code. Skipped loading symbols.

    GrapeCity.ActiveReports.v12.dll!GrapeCity.Enterprise.Data.DataEngine.Extensions.EnumerableExtensions.MemoizedBuffer<GrapeCity.Enterprise.Data.DataEngine.Record>.<GetEnumerator_>d__9.MoveNext() Unknown Non-user code. Skipped loading symbols.

    GrapeCity.ActiveReports.v12.dll!GrapeCity.Enterprise.Data.DataEngine.DataProcessing.AggregateImpl.#fj0(GrapeCity.Enterprise.Data.DataEngine.DataProcessing.ExecutionContext exc) Unknown Non-user code. Skipped loading symbols.

    GrapeCity.ActiveReports.v12.dll!GrapeCity.Enterprise.Data.DataEngine.DataProcessing.DataProcessor.#km2(GrapeCity.Enterprise.Data.DataEngine.DataProcessing.ExecutionContext ctx) Unknown Non-user code. Skipped loading symbols.

    GrapeCity.ActiveReports.v12.dll!GrapeCity.Enterprise.Data.DataEngine.DataProcessing.DataProcessor.#bm2(GrapeCity.Enterprise.Data.DataEngine.DataProcessing.ExecutionContext exc) Unknown Non-user code. Skipped loading symbols.

    GrapeCity.ActiveReports.v12.dll!GrapeCity.Enterprise.Data.DataEngine.DataProcessing.DataProcessor.#fm2(GrapeCity.Enterprise.Data.DataEngine.DataProcessing.ReportContext rc, GrapeCity.Enterprise.Data.DataEngine.DataProcessing.ExecutionContext ctx) Unknown Non-user code. Skipped loading symbols.

    GrapeCity.ActiveReports.v12.dll!GrapeCity.Enterprise.Data.DataEngine.DataProcessing.DataProcessor.#em2(GrapeCity.Enterprise.Data.DataEngine.DataProcessing.ExecutionContext exc) Unknown Non-user code. Skipped loading symbols.

    GrapeCity.ActiveReports.v12.dll!GrapeCity.Enterprise.Data.DataEngine.DataProcessing.DataProcessor.#sA0(GrapeCity.Enterprise.Data.DataEngine.Def.Report report, GrapeCity.Enterprise.Data.DataEngine.DataProcessing.ReportContextFn makeContext, GrapeCity.Enterprise.Data.DataEngine.ExpressionEvaluation.IExpressionContext ctx) Unknown Non-user code. Skipped loading symbols.

    GrapeCity.ActiveReports.v12.dll!GrapeCity.Enterprise.Data.DataEngine.DataProcessing.DataProcessor.Run(GrapeCity.Enterprise.Data.DataEngine.Def.Report report, GrapeCity.Enterprise.Data.DataEngine.DataProcessing.ReportContextFn makeContext, GrapeCity.Enterprise.Data.DataEngine.ExpressionEvaluation.IExpressionContext ctx, GrapeCity.Enterprise.Data.DataEngine.DataProcessing.InteractiveSortState sortState) Unknown Non-user code. Skipped loading symbols.

    GrapeCity.ActiveReports.v12.dll!GrapeCity.ActiveReports.ReportDataProcessor.FetchData(GrapeCity.ActiveReports.PageReport report, GrapeCity.Enterprise.Data.DataEngine.ExpressionEvaluation.IExpressionContext evaluationContext, GrapeCity.ActiveReports.ReportsCore.DataBuilder.ReportStore reportStore, GrapeCity.ActiveReports.ReportDataProcessor.#U.#ja0 makeDocument, System.Func<GrapeCity.ActiveReports.PageReport, GrapeCity.Enterprise.Data.DataEngine.ExpressionEvaluation.IExpressionContext, GrapeCity.BI.Data.CommandBuilder> getCommandBuilder, System.Threading.CancellationToken token, GrapeCity.Enterprise.Data.DataEngine.DataProcessing.InteractiveSortState sortState, bool handleEmptyGroups) Unknown Non-user code. Skipped loading symbols.

    GrapeCity.ActiveReports.v12.dll!GrapeCity.ActiveReports.ReportsCore.Rendering.ReportProcessor.#Tol(bool skipValidation, System.Threading.CancellationToken token) Unknown Non-user code. Skipped loading symbols.

    GrapeCity.ActiveReports.v12.dll!GrapeCity.ActiveReports.ReportsCore.Rendering.ReportProcessor.#fn(bool forceDataRefresh, bool forceParameterDataRefresh, System.Threading.CancellationToken token, bool skipValidation) Unknown Non-user code. Skipped loading symbols.

    GrapeCity.ActiveReports.v12.dll!GrapeCity.ActiveReports.Document.PageDocument.#0Kf(bool forceDataRefresh, bool forceParameterDataRefresh, System.Threading.CancellationToken token, bool skipValidation) Unknown Non-user code. Skipped loading symbols.

    GrapeCity.ActiveReports.v12.dll!GrapeCity.ActiveReports.Document.PageDocument.Render(GrapeCity.ActiveReports.Extensibility.Rendering.IRenderingExtension renderingExtension, GrapeCity.ActiveReports.Extensibility.Rendering.IO.StreamProvider streams, System.Collections.Specialized.NameValueCollection settings, bool forceDataRefresh, bool forceParameterDataRefresh, System.Threading.CancellationToken token) Unknown Non-user code. Skipped loading symbols.

    GrapeCity.ActiveReports.v12.dll!GrapeCity.ActiveReports.Document.PageDocument.Render(GrapeCity.ActiveReports.Extensibility.Rendering.IRenderingExtension renderingExtension, GrapeCity.ActiveReports.Extensibility.Rendering.IO.StreamProvider streams, System.Collections.Specialized.NameValueCollection settings, bool forceDataRefresh, bool forceParameterDataRefresh) Unknown Non-user code. Skipped loading symbols.

    **Second call

    System.Data.dll!SNINativeMethodWrapper.SNIReadSyncOverAsync(System.Runtime.InteropServices.SafeHandle pConn, ref System.IntPtr packet, int timeout) Unknown Non-user code. Skipped loading symbols.

    System.Data.dll!System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync() Unknown Non-user code. Skipped loading symbols.

    System.Data.dll!System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket() Unknown Non-user code. Skipped loading symbols.

    System.Data.dll!System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer() Unknown Non-user code. Skipped loading symbols.

    System.Data.dll!System.Data.SqlClient.TdsParserStateObject.TryReadByte(out byte value) Unknown Non-user code. Skipped loading symbols.

    System.Data.dll!System.Data.SqlClient.TdsParser.TryRun(System.Data.SqlClient.RunBehavior runBehavior, System.Data.SqlClient.SqlCommand cmdHandler, System.Data.SqlClient.SqlDataReader dataStream, System.Data.SqlClient.BulkCopySimpleResultSet bulkCopyHandler, System.Data.SqlClient.TdsParserStateObject stateObj, out bool dataReady) Unknown Non-user code. Skipped loading symbols.

    System.Data.dll!System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() Unknown Non-user code. Skipped loading symbols.

    System.Data.dll!System.Data.SqlClient.SqlDataReader.MetaData.get() Unknown Non-user code. Skipped loading symbols.

    System.Data.dll!System.Data.SqlClient.SqlCommand.FinishExecuteReader(System.Data.SqlClient.SqlDataReader ds, System.Data.SqlClient.RunBehavior runBehavior, string resetOptionsString, bool isInternal, bool forDescribeParameterEncryption, bool shouldCacheForAlwaysEncrypted) Unknown Non-user code. Skipped loading symbols.

    System.Data.dll!System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(System.Data.CommandBehavior cmdBehavior, System.Data.SqlClient.RunBehavior runBehavior, bool returnStream, bool async, int timeout, out System.Threading.Tasks.Task task, bool asyncWrite, bool inRetry, System.Data.SqlClient.SqlDataReader ds, bool describeParameterEncryptionRequest) Unknown Non-user code. Skipped loading symbols.

    System.Data.dll!System.Data.SqlClient.SqlCommand.RunExecuteReader(System.Data.CommandBehavior cmdBehavior, System.Data.SqlClient.RunBehavior runBehavior, bool returnStream, string method, System.Threading.Tasks.TaskCompletionSource completion, int timeout, out System.Threading.Tasks.Task task, out bool usedCache, bool asyncWrite, bool inRetry) Unknown Non-user code. Skipped loading symbols.

    System.Data.dll!System.Data.SqlClient.SqlCommand.RunExecuteReader(System.Data.CommandBehavior cmdBehavior, System.Data.SqlClient.RunBehavior runBehavior, bool returnStream, string method) Unknown Non-user code. Skipped loading symbols.

    System.Data.dll!System.Data.SqlClient.SqlCommand.ExecuteReader(System.Data.CommandBehavior behavior, string method) Unknown Non-user code. Skipped loading symbols.

    System.Data.dll!System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(System.Data.CommandBehavior behavior) Unknown Non-user code. Skipped loading symbols.

    System.Data.dll!System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() Unknown Non-user code. Skipped loading symbols.

    GrapeCity.ActiveReports.v12.dll!GrapeCity.BI.Data.DataProviders.SysCommandAdapter.ExecuteReader() Unknown Non-user code. Skipped loading symbols.

    GrapeCity.ActiveReports.v12.dll!#UwA.#uue.ExecuteReader(GrapeCity.ActiveReports.Extensibility.Data.IDbCommand command) Unknown Non-user code. Skipped loading symbols.

    GrapeCity.ActiveReports.v12.dll!GrapeCity.BI.Data.RecordScanner.RecordSet.d__5.MoveNext() Unknown Non-user code. Skipped loading symbols.

    GrapeCity.ActiveReports.v12.dll!GrapeCity.Enterprise.Data.DataEngine.Extensions.EnumerableExtensions.MemoizedBuffer<GrapeCity.Enterprise.Data.DataEngine.Record>.#7kY(out bool hasValue) Unknown Non-user code. Skipped loading symbols.

    GrapeCity.ActiveReports.v12.dll!GrapeCity.Enterprise.Data.DataEngine.Extensions.EnumerableExtensions.MemoizedBuffer<GrapeCity.Enterprise.Data.DataEngine.Record>.BufferizeAll() Unknown Non-user code. Skipped loading symbols.

    GrapeCity.ActiveReports.v12.dll!GrapeCity.ActiveReports.ReportDataProcessor.#8p2() Unknown Non-user code. Skipped loading symbols.

    GrapeCity.ActiveReports.v12.dll!GrapeCity.ActiveReports.ReportDataSnapshot.ReleaseDataSource() Unknown Non-user code. Skipped loading symbols.

    GrapeCity.ActiveReports.v12.dll!#sxA.#saR.#PfW() Unknown Non-user code. Skipped loading symbols.

    GrapeCity.ActiveReports.v12.dll!GrapeCity.ActiveReports.Layout.PageGenerator.#xyi(#sxA.#saR context, GrapeCity.ActiveReports.Layout.Point loc, GrapeCity.ActiveReports.Layout.PaginateResult pageFragment, bool isLastPage, bool isUpdatePageNumber, int limit) Unknown Non-user code. Skipped loading symbols.

    GrapeCity.ActiveReports.v12.dll!GrapeCity.ActiveReports.Layout.LayoutEngine.#fK1(GrapeCity.ActiveReports.Layout.PaginateResult page, bool _, bool isLast) Unknown Non-user code. Skipped loading symbols.

    GrapeCity.ActiveReports.v12.dll!GrapeCity.Enterprise.Data.DataEngine.Extensions.EnumerableExtensions.d__3<GrapeCity.ActiveReports.Layout.PaginateResult, GrapeCity.ActiveReports.Extensibility.Layout.ILayoutPage>.MoveNext() Unknown Non-user code. Skipped loading symbols.

    System.Core.dll!System.Linq.Enumerable.WhereEnumerableIterator<GrapeCity.ActiveReports.Extensibility.Layout.ILayoutPage>.MoveNext() Unknown Non-user code. Skipped loading symbols.

    GrapeCity.ActiveReports.Export.Image.v12.dll!GrapeCity.ActiveReports.Export.Image.Page.PageController.PageController.d__0.MoveNext() Unknown Non-user code. Skipped loading symbols.

    GrapeCity.ActiveReports.Export.Image.v12.dll!GrapeCity.ActiveReports.Export.Image.Page.PageController.PageController.d__7.MoveNext() Unknown Non-user code. Skipped loading symbols.

    GrapeCity.ActiveReports.Export.Pdf.v12.dll!GrapeCity.ActiveReports.Export.Pdf.Page.PdfRenderingExtension.#Sni(object _) Unknown Non-user code. Skipped loading symbols.

    mscorlib.dll!System.Threading.ThreadHelper.ThreadStart_Context(object state) Unknown Non-user code. Skipped loading symbols.

    mscorlib.dll!System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state, bool preserveSyncCtx) Unknown Non-user code. Skipped loading symbols.

    mscorlib.dll!System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state, bool preserveSyncCtx) Unknown Non-user code. Skipped loading symbols.

    mscorlib.dll!System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state) Unknown Non-user code. Skipped loading symbols.

    mscorlib.dll!System.Threading.ThreadHelper.ThreadStart(object obj) Unknown Non-user code. Skipped loading symbols.Test.zip

  • mohitg

    Posted 15 May 2018, 5:54 pm EST - Updated 30 September 2022, 9:18 am EST

    Hello Justin,

    It seems that report is not attached properly. Could you please reattached the report again in ZIP format.

    Also, I am not able to reproduce the problem with the attached report. I have set “Sum” function in Table footer and found that SQL query is executed only once. Please refer the screenshot of the SQL Profiler.

    Could you please try with the latest hotfix of AR12 as there is lots of change is done in this hotfix related to data engine. You can find the installer at the following link:

    http://cdn.grapecity.com/ActiveReports/ar12/hotfixes/ActiveReports-v12.1.13124.0.msi

    Hope it helps.

    Thanks,

    Mohit

    1184×554 65.1 KB


    test.zip

  • jharrell

    Posted 16 May 2018, 3:38 am EST - Updated 30 September 2022, 9:18 am EST

    Updated to 13124 same issue. My test just has a textbox in the body using the First aggregate function to get the value from the dataset:

    1276×184 18.5 KB

    Test.zip

  • mohitg

    Posted 17 May 2018, 1:26 am EST

    Hello,

    Thanks for the information.

    I am able to replicate the problem at our end. I have escalated this to the development team(Tracking ID 258397) and will revert you back once have any information from them.

    Thanks

    Mohit

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels

  • © 2023 MESCIUS inc. All Rights Reserved.  ·  1.800.858.2739

    All product and company names herein may be trademarks of their respective owners.