Handling Large Datasets Returned by the Query Service [AX 2012]

Updated: December 9, 2011

Applies To: Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

The Microsoft Dynamics AX query service provides two ways for handling the return of large datasets:

  • Paging – The client application uses a paging object to return all query result records up to the maximum record limit. You can use paging when you call a static, a user-defined, or a dynamic query.

  • Streaming – The client application reads all the query result records until the end of file (EOF) is reached. You can use streaming only when you call a user–defined or a dynamic query.

Query type

Supports paging

Query service method

Supports streaming

Query service method

Static

Yes

ExecuteStaticQuery

No

User-defined

Yes

ExecuteQuery

Yes

ExecuteStreamedQuery

Dynamic

Yes

ExecuteDynamicQuery

Yes

ExecuteStreamedDynamicQuery

In order to implement paging, you must pass a paging object parameter together with the query specification. The paging object enables you to write code to handle a query that returns large amounts of data. The query service paging functionality includes two types of paging:

  • Position-based paging – You supply a start position and the record limit for each query data source. The start position is a numeric value that specifies the record position (in the results) from which you want to start paging.

  • Value-based paging – You provide a bookmark and the record limit for each query data source. The bookmark is an identifier that is derived from the value of the record that specifies the record position (in the results) from which you want to start paging.

If you pass a NULL paging object to the query service, it will not use any paging and will return all records for the specified query up to the maximum record limit. For more information about paging and the query service, see Paging and Query Service Results.

Paging is useful when you may or may not want to read all of the data returned by a query. For example, you may want to use paging with an interactive user interface where user action determines whether more data is displayed.

When you call a query that returns a large amount of data, you can use streaming to read all of the data that is returned. If you want to use streaming, you must use one of the following query service methods:

  • ExecuteStreamedQuery – Call this method when you are using the query service with a user-defined query.

  • ExecuteStreamedDynamicQuery – Call this method when you are using the query service with a dynamic query.

NoteNote

You cannot use streaming when you call the query service and use a static query.

For more information about how to implement streaming, see Streaming and Query Service Results.

Streaming is useful for any scenario in which you want to read all of the data returned by a query, for example, when you are retrieving data for a report.

From a single call to the query service, the default maximum number of rows that can be returned from a query data source is 25,000. This means that each data table in the dataset returned by the query service can have up to 25,000 records if you do not specify a record limit.

Community Additions

ADD
Show: