Microsoft Dynamics AX (Archived)

Performance Issue with insert_recordset

(0) Share

Report

Posted on by Gautam

3,945

Hi experts,

We have a X++ query , which we are using to fetch data and inserting into temp Table.

In this query we are using insert_recordset to insert in temp table and 6 joins to extract data but we are facing performance issue with the same.

So is there any way to optimize this query.

Kindly suggest me , Thanks

*This post is locked for comments

I have the same question (0)

All responses (11)

Answers (1)

Sort by

Martin Dráb 239,084 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

(Un)fortunately you're not the first one trying to improve performance of a query, therefore you'll find previous discussions on this topic. What I recommend the most is running the query in SSMS and collect statistics and the actual execution plan, which will tell you how exactly is your query executed, which part it the slowest etc.

Also, make sure that your insert_recordset isn't turned to row-by-row processing for some reason (they're explained in documentation, if you're not familiar with them).

By the way, isn't is an InMemory temporary table?

Was this reply helpful? Yes No
Gautam 3,945 on at

Like (0)

Report
Copy link

Link copied!

Hi Martin ,

Thanks for your reply

Table is tempdb, not InMemory.

Was this reply helpful? Yes No
Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report
Copy link

Link copied!

You probably have not eliminated the root causes of poor performance for queries, which are some of the following:

- use a field list for every table

- selection criteria in the where clause have covering indexes on the tables

- sorting and grouping fields have covering indexes too

- use exists joins wherever possible if values are not used from the table or from child tables

- make sure the set-based operations do not fall back to row-based operations to speed up processing in SQL

- make sure your SQL Database statistics and indexes have a maintenance for fragmentation and keeping them up-to-date

Was this reply helpful? Yes No
Gautam 3,945 on at

Like (0)

Report
Copy link

Link copied!

Thanks Vilmos for prompt reply, I will cross check my query.

Was this reply helpful? Yes No
Martin Dráb 239,084 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

The advantage of analyzing the execution plans and statistics is that you don't have to guess what's causing the problem (fetching unnecessary data, performing a full scan because of a missing index etc.) and trying random fixes; you can see the problem in the data and you can focus on the right thing.

Was this reply helpful? Yes No
Vilmos Kintera 46,149 on at

Like (0)

Report
Copy link

Link copied!

Please make sure you mark helpful answers as verified to resolve the topic.

Was this reply helpful? Yes No
Manmohan Mehta 1,185 on at

Like (0)

Report
Copy link

Link copied!

Hi Gautam,

Please ensure that you don't have insert method overridden on you temptable as this may also lead to performance issues...

Was this reply helpful? Yes No
Verified answer

Vilmos Kintera 46,149 on at

Like (0)

Report
Copy link

Link copied!

Can you please make sure you tick the checkboxes for helpful answers next to each post, so the topic gets resolved? Thank you.

Was this reply helpful? Yes No
Gautam 3,945 on at

Like (0)

Report
Copy link

Link copied!

Sure Vilmos.

Thanks for your suggestion.

Was this reply helpful? Yes No
Vilmos Kintera 46,149 on at

Like (0)

Report
Copy link

Link copied!

If you have been able to resolve the issue, please give us some summary on what was the problem, so others with similar problems would know where to look. Thank you

Was this reply helpful? Yes No