Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Is there a difference ...
Finance | Project Operations, Human Resources, ...
Suggested answer

Is there a difference in performance between an inline while select in X++ and using a Query() object?

(0) ShareShare
ReportReport
Posted on by Brandt Fundak 2,005

Hi all.

I'm trying to optimize some queries that we have written in order to populate some temp tables that provide the data for a form we created for our planning department to gauge demand for certain items in AX. Currently these queries are written as while select statements and I was wondering if there was a performance gain from constructing them in a Query() object instead. I'm certain that when it comes to executing the query in SQL the performance is likely the same, but I am wondering if by employing a Query() object if I can somehow cut down on RPC chatter between AX and SQL when executing the query. I have seen other posts in relation to this topic, but not concerning performance. If anyone has any insight on this I would love to hear it.

Thanks.

Brandt

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,156 on at
    RE: Is there a difference in performance between an inline while select in X++ and using a Query() object?

    The performance of the database call is no different between x++ select statement or Query object. Both are converted to a single T-SQL statement by the AOS and then this statement is queried against the database. So the communication between the AOS and SQL server is exactly the same, assuming your select statement and query object are asking for the same data.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Is there a difference in performance between an inline while select in X++ and using a Query() object?

    Hi Brandt,

    I guess you're wondering the difference of interaction times of ax and sqlserver between query object and while select statement.

    Sorry I never tried that.

    In your senario, i suggest using the query as the form datasource instead of temp table so you don't need loop the result set one by one and insert in to the temp table which is the main pain point of performance.

  • Blue Wang Profile Picture
    Blue Wang on at
    RE: Is there a difference in performance between an inline while select in X++ and using a Query() object?

    Hi Brandt,

    Query:

    Can be changed at runtime and can be packed and have more features than select statement such as union queries. can be added to AOT。

    allows you to modify the query dynamically based on business logic.

    Query framework gets worse compiler control than select statements.

    Select:

    More easier to read, it's hard code and it's much less flexble.

    Other functions are not much different in nature. What about your query structure and data?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans