QueryRun.next() taking too long to be completed

(0) Share

Report

Posted on by ALAhmed

916

Hello,

Yesterday , i build batch that export data to excel using query with x++(the query is based on TaxTrans and TaxTrans_In tables).It worked fine i can export 60000 line on 10minute.

Today, i run the export and ax client freeze and take a long time. when i debug the system blo on the ling QueryRun.next()

I do not why QueryRun.next() take too long to be completed.

Any regard in this point.

Thank you in advance

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (0)

Suggested answer

Community Member on at

Like (0)

Report

RE: AX 2009 - QueryRun.next() taking too long to be completed.

Hello Alex

You can try run your query in SQL and see if there is any missing indexes that create the slowness.

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

Like (1)

Report

RE: QueryRun.next() taking too long to be completed

I agree that you should focus on how the query gets executed inside SQL Server. But it doesn't look like a case of a missing index if the same query (on, presumably, similar data) worked fine recently.

You can obtain the SQL query by using tracing (see How to Monitor for Long Running Queries in AX). Then you can run it inside SSMS to see the execution plan.

Also, check if your index maintenance routines runs as expected. You may have the right indexes, but the optimizer may decide not to use them if the statistics are wrong.

Was this reply helpful? Yes No
ALAhmed 916 on at

Like (0)

Report

RE: QueryRun.next() taking too long to be completed

Hello ,

Thank you for the feedback.

I tried move the classe in other environment and it's work(both environnement have the same buffer size configuration)

I synchronize database and index and compile ax => not work

I tried to delete usage data , .auc file and axapd.aoi => not work

I get the sql script of the query and i run it in ssms => works.

In my query , i select x fields and when remove one field(any field) => it's work

When i trace sql , i get this error from the table SYSTRACETABLESQL

[Microsoft][SQL Native Client][SQL Server]Could not complete cursor operation because the table schema changed after the cursor was declared.

I don't know why the ax freeze and take too long to be completed.

Thank you in advance

Was this reply helpful? Yes No
manasa133 110 on at

Like (0)

Report

RE: QueryRun.next() taking too long to be completed

I'm facing the same issue. Any suggestions would be appreciated.

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

Like (0)

Report

RE: QueryRun.next() taking too long to be completed

Alex, what do you mean by "not work" and "it's work"? That "it's works" means it runs faster than "not work"?

manasa133, please see the advice how you can analyze the query. But it's unlikely that your query is the same as Alex's and there is no universal cause and universal solutions for all slow queries. If you want to discuss your particular situation (your query, your execution plan...), please create a new thread and do it there.

Was this reply helpful? Yes No
ALAhmed 916 on at

Like (0)

Report

RE: QueryRun.next() taking too long to be completed

Hello,

Martin Dráb Thank you for the reply.

Work means that i can run the extract with better performance.

Not work means that i can not run the report , it take a long time and ax freeze

Regards,

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

Like (0)

Report

RE: QueryRun.next() taking too long to be completed

Can you please elaborate your points "I get the sql script of the query and i run it in ssms" (how you've got it) and "i select x fields and when remove one field(any field)"?

Was this reply helpful? Yes No
ALAhmed 916 on at

Like (0)

Report

RE: QueryRun.next() taking too long to be completed

Hello,

"I get the sql script of the query and i run it in ssms" : I get the sql script by the code info(this.query().dataSourceNo(1).toString()); I get the reseult in 1 seconde when i run it in sql

"i select x fields and when remove one field(any field)"? : I select 80 field in taxTrans and taxTrans_IN(tables of my query) and when i select 79 field i can export in 60second.

Thank you in advance

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

Like (0)

Report

RE: QueryRun.next() taking too long to be completed

What QueryBuildDataSource.toString() returns isn't T-SQL code. It usually can't be even executed as T-SQL and it doesn't contain various things handled by AX kernel (e.g. automatic application of DataAreaId filters, dealing with data type conversions, interpreting expressions and so on). In short, it's not the query which is actually sent to database and therefore your test doesn't prove anything. Please refer to my previous reply to learn a way how to capture the real T-SQL query. You can also use Extended Events, Trace Parser and a few other techniques.

Regarding the other part, can you be a bit more specific, please? How do you select the fields? Please don't forget that we can't help you if you don't tell us enough about your problem.

Was this reply helpful? Yes No
Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report

RE: QueryRun.next() taking too long to be completed

Your technical team should consider deploying DynamicsPerf tool for pulling data from SQL Server DMVs involving query execution. Then you can use the data stored in DynamicsPerf to further analyze your query, like checking index fragmentation, old statistics, missing covering index, what kind of query execution plans are used for the specific SQL query in case it is just a badly optimized query plan, etc. Also you could monitor blocking on those tables, or find weak spots in your deployment like a disk/IO bottleneck on SQL.

It goes along the lines on what Martin has suggested: check what is running against your database specific to the data you are trying to access, and take action depending on that.

Was this reply helpful? Yes No