Performance Problem with "Or"-Statements in Form-Q

axafrank

Our environment: Axapta 3.0 SP2, Oracle-Database, around 320 000 records in purchline-table

We have big performance troubles to execute Querys from Form (in ExecuteQuery-Methode on DataSource).
We don't have general problems, only with "OR-Statements" in the Query.

Sample:
Form with one DataSource (PurchLine) and one Grid in the Design. In the Grid are only the Fields PurchId, LineNum and ItemId.

Only the methode purchLine.executequery() ist modified from us.

public void executeQuery()
{

    Query                   	queryLine;
    QueryBuildDataSource    	queryBuildDataSourceLine;
    QueryBuildRange     	qbrPurchId;
    ;

    purchLine_ds.autoQuery(false);
    queryLine   = new Query();
    QueryBuildDataSourceLine    =   queryLine.addDataSource(tableNum(PurchLine));
    qbrPurchId     =   QueryBuildDataSourceLine.addRange(fieldnum(PurchLine, PurchId));
    purchLine_ds.query(queryLine);
    qbrPurchId.value(strfmt('%1,%2', sysQuery::value("5"), sysQuery::value("6")));
    super();

}

If we open the form, the super()-Call of the executeQuery-methode needs approximately 100 seconds.

If we change the QueryBuildRange-Line to

qbrPurchId.value(strfmt('%1', sysQuery::value("5")));

then it needs only like one second.


If we use a job to execute the same statement (two orders) it needs also only one second.

static void BnQueryTest(Args _args)
{
    PurchLine              	purchLine;
    Query                  	query   =   new Query();
    QueryBuildDataSource   	qbds;
    queryrun               	queryRun;
    ;

    qbds    =   query.addDataSource(tablenum(PurchLine));
    qbds.addRange(fieldnum(purchLine, purchId)).value(strfmt("%1,%2", sysQuery::value("6"), sysQuery::value("7")));
    queryRun    =    new queryrun(query);

    while(queryrun.next())
    {
        purchline = queryrun.get(tablenum(purchline));
        info(purchLine.PurchId);
    }
}

The oracle-statistik shows that the form-query causes the readin of approximatly 80 000 logical blocks, the job-query 1 000 logical blocks.
The axapta-tracelog shows the same sql-statement for both calls. The only difference is the hint /*+ FIRST_ROWS */ in the statement from the form-query.

What is our problem with the query from form?

please help us!