Microsoft Dynamics AX (Archived)

DMFInventTableEntity low performance

(0) Share

Report

Posted on by Diego Mancassola

272

Hi to all and happy new year!

This year start for me with a performance problem with DMF on DAX 2012 R3 CU10 version.

I need to import more than 430k items on the system, but, running on batch, the system import only 4k items/hour.

Analysis of the activity trace i note these are a lot of query performed to tables DMFTARGET*

These table has static data, why execute a lot of time these query, for each field i think?

On trace summary i have a lot of time spent on database side:

Thanks in advance,

Dieg

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

Martin Dráb 237,681 Most Valuable Professional on at

Like (1)

Report

RE: DMFInventTableEntity low performance

Could you please show us the query in question?

Also, how long period does your data represent? The query took just five milliseconds on average and even 13 seconds in total isn't critical if we're talking about a longer period.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (2)

Report

RE: DMFInventTableEntity low performance

Hi Diego

In my experience there are quite a few performance issues with DMF. MS-Support have this blog on a similar issue:

community.dynamics.com/.../data-management-import-performance-issue-with-cursor-fetch

Judging from your short description it may look like the same problem with nested loops.

Have a look at the article and let us know

Was this reply helpful? Yes No
Suggested answer

Denis Macchinetti 16,444 on at

Like (1)

Report

RE: DMFInventTableEntity low performance

Hi Diego

Happy New Year as well.

About DMF Performance take a look to this Post kaya-consulting.com/tips-about-data-import-export-framework-performance

Very Useful!

Also, use SQL Server Profiler in order to catch the slow queries and verify the Execution Plan used by SQL.

Hope help you!

Was this reply helpful? Yes No
Diego Mancassola 272 on at

Like (0)

Report

RE: DMFInventTableEntity low performance

Martin, this is the query, IMHO make not sense to get every time information of mapping between STAGING to TARGET

Every time will be the same.

SELECT TOP 1 T1.ENTITY,

T1.ENTITYFIELD,

T1.MAPPINGTYPE,

T1.METHODDATASOURCE,

T1.METHODNAME,

T1.METHODRETURNPOSITION,

T1.POSITION,

T1.RUNMODIFIEDFIELD,

T1.RUNVALIDATEFIELD,

T1.XMLFIELD,

T1.RECVERSION,

T1.PARTITION,

T1.RECID,

T2.ENTITY,

T2.ISAUTOMANUAL,

T2.TARGETDATASOURCENAME,

T2.TARGETFIELD,

T2.TARGETTABLE,

T2.XMLFIELD,

T2.RECVERSION,

T2.PARTITION,

T2.RECID

FROM DMFTARGETXMLTOENTITYMAP T1 CROSS

JOIN DMFTARGETXML T2

WHERE ((T1.PARTITION=?)

AND ((T1.ENTITY=?)

AND (T1.MAPPINGTYPE=?)))

AND ((T2.PARTITION=?)

AND ((((T2.ENTITY=T1.ENTITY)

AND (T2.XMLFIELD=T1.XMLFIELD))

AND (T2.TARGETDATASOURCENAME=?))

AND (T2.TARGETFIELD=?)))

Was this reply helpful? Yes No
Diego Mancassola 272 on at

Like (0)

Report

RE: DMFInventTableEntity low performance

@Steffen, not is not my problem now. But thanks a lot, i take it and check later if there is the same problem.

@Denis, thanks a lot, i'll check every suggested point. At this time i not have any a particular slow query.

Was this reply helpful? Yes No