Hi, We have upgraded llblgen from ver 2.5 to ver 2.6. T he same code that work before now throws an exeption: "ORA-01013: user requested cancel of current operation". C#:

// Filter
RelationPredicateBucket filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(QualityMeasureFields.IsActive == true);
// Prefetch
PrefetchPath2 prefetchPath = new PrefetchPath2(EntityType.QualityMeasureEntity);

IPrefetchPathElement2 groups = prefetchPath.Add(QualityMeasureEntity.PrefetchPathQmNumeratorGroupCollection);
groups.Sorter.Add(QmNumeratorGroupFields.SortOrder | SortOperator.Ascending);

IPrefetchPathElement2 items = groups.SubPath.Add(QmNumeratorGroupEntity.PrefetchPathQmNumeratorItemCollection);
items.Sorter.Add(QmNumeratorItemFields.SortOrder | SortOperator.Ascending);

IPrefetchPathElement2 codes = items.SubPath.Add(QmNumeratorItemEntity.PrefetchPathQmNumeratorItemCodeCollection);
codes.Sorter.Add(QmNumeratorItemCodeFields.SortOrder | SortOperator.Ascending);

IPrefetchPathElement2 modifiers = codes.SubPath.Add(QmNumeratorItemCodeEntity.PrefetchPathQmNumerItemCodeModifierCollection);
modifiers.Sorter.Add(QmNumerItemCodeModifierFields.ExclusionModifierId | SortOperator.Ascending);

In addition, SQL, that was generated by ver 2.5 run fast :

SELECT "PMDS"."QM_NUMERATOR_ITEM_CODE"."ITEM_ID" AS "ItemId"
  FROM "PMDS"."QM_NUMERATOR_ITEM_CODE"
 WHERE (("PMDS"."QM_NUMERATOR_ITEM_CODE"."QUALITY_MEASURE_ID" IN
       (SELECT "PMDS"."QM_NUMERATOR_ITEM"."QUALITY_MEASURE_ID" AS "QualityMeasureId"
            FROM "PMDS"."QM_NUMERATOR_ITEM"
           WHERE (("PMDS"."QM_NUMERATOR_ITEM"."QUALITY_MEASURE_ID" IN
                 (SELECT "PMDS"."QM_NUMERATOR_GROUP"."QUALITY_MEASURE_ID" AS "QualityMeasureId"
                      FROM "PMDS"."QM_NUMERATOR_GROUP"
                     WHERE (("PMDS"."QM_NUMERATOR_GROUP"."QUALITY_MEASURE_ID" IN
                           (SELECT "PMDS"."QUALITY_MEASURE"."QUALITY_MEASURE_ID" AS "QualityMeasureId"
                                FROM "PMDS"."QUALITY_MEASURE"
                               WHERE ((("PMDS"."QUALITY_MEASURE"."IS_ACTIVE" =
                                     :IsActive5))))))) AND
                 "PMDS"."QM_NUMERATOR_ITEM"."GROUP_ID" IN
                 (SELECT "PMDS"."QM_NUMERATOR_GROUP"."GROUP_ID" AS "GroupId"
                      FROM "PMDS"."QM_NUMERATOR_GROUP"
                     WHERE (("PMDS"."QM_NUMERATOR_GROUP"."QUALITY_MEASURE_ID" IN
                           (SELECT "PMDS"."QUALITY_MEASURE"."QUALITY_MEASURE_ID" AS "QualityMeasureId"
                                FROM "PMDS"."QUALITY_MEASURE"
                               WHERE ((("PMDS"."QUALITY_MEASURE"."IS_ACTIVE" =
                                     :IsActive6)))))))))) AND
       "PMDS"."QM_NUMERATOR_ITEM_CODE"."ITEM_ID" IN
       (SELECT "PMDS"."QM_NUMERATOR_ITEM"."ITEM_ID" AS "ItemId"
            FROM "PMDS"."QM_NUMERATOR_ITEM"
           WHERE (("PMDS"."QM_NUMERATOR_ITEM"."QUALITY_MEASURE_ID" IN
                 (SELECT "PMDS"."QM_NUMERATOR_GROUP"."QUALITY_MEASURE_ID" AS "QualityMeasureId"
                      FROM "PMDS"."QM_NUMERATOR_GROUP"
                     WHERE (("PMDS"."QM_NUMERATOR_GROUP"."QUALITY_MEASURE_ID" IN
                           (SELECT "PMDS"."QUALITY_MEASURE"."QUALITY_MEASURE_ID" AS "QualityMeasureId"
                                FROM "PMDS"."QUALITY_MEASURE"
                               WHERE ((("PMDS"."QUALITY_MEASURE"."IS_ACTIVE" =
                                     :IsActive7))))))) AND
                 "PMDS"."QM_NUMERATOR_ITEM"."GROUP_ID" IN
                 (SELECT "PMDS"."QM_NUMERATOR_GROUP"."GROUP_ID" AS "GroupId"
                      FROM "PMDS"."QM_NUMERATOR_GROUP"
                     WHERE (("PMDS"."QM_NUMERATOR_GROUP"."QUALITY_MEASURE_ID" IN
                           (SELECT "PMDS"."QUALITY_MEASURE"."QUALITY_MEASURE_ID" AS "QualityMeasureId"
                                FROM "PMDS"."QUALITY_MEASURE"
                               WHERE ((("PMDS"."QUALITY_MEASURE"."IS_ACTIVE" =
                                     :IsActive8))))))))))))

But the following SQL, generated by ver 2.6 take a long time, without ending.

SELECT DISTINCT "PMDS"."QM_NUMERATOR_ITEM_CODE"."QUALITY_MEASURE_ID" AS "QualityMeasureId",
                "PMDS"."QM_NUMERATOR_ITEM_CODE"."ITEM_ID"           AS "ItemId",
                "PMDS"."QM_NUMERATOR_ITEM_CODE"."ITEM_CODE_ID"     AS "ItemCodeId",
                "PMDS"."QM_NUMERATOR_ITEM_CODE"."CODE"             AS "Code",
                "PMDS"."QM_NUMERATOR_ITEM_CODE"."CODE_SYSTEM_ID"     AS "CodeSystemId",
                "PMDS"."QM_NUMERATOR_ITEM_CODE"."PHRASE_ID"       AS "PhraseId",
                "PMDS"."QM_NUMERATOR_ITEM_CODE"."SORT_ORDER"         AS "SortOrder",
                "PMDS"."QM_NUMERATOR_ITEM_CODE"."CREATE_DATE"       AS "CreateDate",
                "PMDS"."QM_NUMERATOR_ITEM_CODE"."CREATE_BY"       AS "CreateBy",
                "PMDS"."QM_NUMERATOR_ITEM_CODE"."UPDATE_DATE"       AS "UpdateDate",
                "PMDS"."QM_NUMERATOR_ITEM_CODE"."UPDATE_BY"       AS "UpdateBy",
                "PMDS"."QM_NUMERATOR_ITEM_CODE"."TRANSACTION_ID"     AS "TransactionId",
                "PMDS"."QM_NUMERATOR_ITEM_CODE"."LOGICAL_NAME"     AS "LogicalName"
  FROM "PMDS"."QM_NUMERATOR_ITEM_CODE"
 WHERE ("PMDS"."QM_NUMERATOR_ITEM_CODE"."QUALITY_MEASURE_ID" IN
       (SELECT "PMDS"."QM_NUMERATOR_ITEM"."QUALITY_MEASURE_ID" AS "QualityMeasureId"
           FROM "PMDS"."QM_NUMERATOR_ITEM"
          WHERE (("PMDS"."QM_NUMERATOR_ITEM"."QUALITY_MEASURE_ID" IN
                (SELECT "PMDS"."QM_NUMERATOR_GROUP"."QUALITY_MEASURE_ID" AS "QualityMeasureId"
                     FROM "PMDS"."QM_NUMERATOR_GROUP"
                    WHERE (("PMDS"."QM_NUMERATOR_GROUP"."QUALITY_MEASURE_ID" IN
                          (SELECT "PMDS"."QUALITY_MEASURE"."QUALITY_MEASURE_ID" AS "QualityMeasureId"
                               FROM "PMDS"."QUALITY_MEASURE"
                              WHERE ((("PMDS"."QUALITY_MEASURE"."IS_ACTIVE" =
                                    :IsActive1))))))) AND
                "PMDS"."QM_NUMERATOR_ITEM"."GROUP_ID" IN
                (SELECT "PMDS"."QM_NUMERATOR_GROUP"."GROUP_ID" AS "GroupId"
                     FROM "PMDS"."QM_NUMERATOR_GROUP"
                    WHERE (("PMDS"."QM_NUMERATOR_GROUP"."QUALITY_MEASURE_ID" IN
                          (SELECT "PMDS"."QUALITY_MEASURE"."QUALITY_MEASURE_ID" AS "QualityMeasureId"
                               FROM "PMDS"."QUALITY_MEASURE"
                              WHERE ((("PMDS"."QUALITY_MEASURE"."IS_ACTIVE" =
                                    :IsActive2)))))))))) AND
       "PMDS"."QM_NUMERATOR_ITEM_CODE"."ITEM_ID" IN
       (SELECT "PMDS"."QM_NUMERATOR_ITEM"."ITEM_ID" AS "ItemId"
           FROM "PMDS"."QM_NUMERATOR_ITEM"
          WHERE (("PMDS"."QM_NUMERATOR_ITEM"."QUALITY_MEASURE_ID" IN
                (SELECT "PMDS"."QM_NUMERATOR_GROUP"."QUALITY_MEASURE_ID" AS "QualityMeasureId"
                     FROM "PMDS"."QM_NUMERATOR_GROUP"
                    WHERE (("PMDS"."QM_NUMERATOR_GROUP"."QUALITY_MEASURE_ID" IN
                          (SELECT "PMDS"."QUALITY_MEASURE"."QUALITY_MEASURE_ID" AS "QualityMeasureId"
                               FROM "PMDS"."QUALITY_MEASURE"
                              WHERE ((("PMDS"."QUALITY_MEASURE"."IS_ACTIVE" =
                                    :IsActive3))))))) AND
                "PMDS"."QM_NUMERATOR_ITEM"."GROUP_ID" IN
                (SELECT "PMDS"."QM_NUMERATOR_GROUP"."GROUP_ID" AS "GroupId"
                     FROM "PMDS"."QM_NUMERATOR_GROUP"
                    WHERE (("PMDS"."QM_NUMERATOR_GROUP"."QUALITY_MEASURE_ID" IN
                          (SELECT "PMDS"."QUALITY_MEASURE"."QUALITY_MEASURE_ID" AS "QualityMeasureId"
                               FROM "PMDS"."QUALITY_MEASURE"
                              WHERE ((("PMDS"."QUALITY_MEASURE"."IS_ACTIVE" =
                                    :IsActive4)))))))))))
 ORDER BY "PMDS"."QM_NUMERATOR_ITEM_CODE"."SORT_ORDER" ASC

Most probably this is a time-out issue.

Please check the following threads: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=12661 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=12542

Hi, The problem is not timeout. Problem is the different generated query in the ver 2.5 and 2.6. I take the qury from the trace. Query generated by ver 2.5 run under 0.5 sec. Query genereted by ver 2.6 not finish run after 5 min(In sqlplus). In my previos post you can see that queries is diffrent.

I use oracle 10g

Hi, I think the problem is in the "distinct" operator in the query. Becouse cost of query with distinct = 866121013 and cost this query without distinct = 31 !!!.

Whay ver 2.6 generate query with "distinct" operator? Maybe I can manage it? I fetch entity with subentities - 4 levels.

You didn't post all the code. There fopr it's impossible to say what's wrong, the first query returns a single field, the second returns the entire entity.

Also the first query is chopped up, it's not the entire query, as the parameters should be numbered starting with 1, like in the second query.

You should first try without the prefetchPath and see what exactly makes the prefetchPath go slow. As the prefetchpath itself will take several queries and just 'it's slow' is not going to help. The exact slow prefetchPath should be determined.

Hi, You can understand what level from the code and query, becouse entity name like table name.

Levels:

1 - QualityMeasureEntity(table Quality_Measure) 2 - QmNumeratorGroupEntity (table Qm_Numerator_Group) 3 - QmNumeratorItemEntity(table Qm_Numerator_Item) 4 - QmNumeratorItemCodeEntity(table Qm_Numerator_Item_Code).

Haw you engin generate sql you know beter.

Thanks

Most probably you are comparing 2 different queries.

One fetching anm entity (that's why the distinct was generated), and another fetchin a list.

Distinct is always emitted for entity fetchs, this is true in v.2.5 too.

valery.k wrote:

Hi, You can understand what level from the code and query, becouse entity name like table name.

Levels:

1 - QualityMeasureEntity(table Quality_Measure) 2 - QmNumeratorGroupEntity (table Qm_Numerator_Group) 3 - QmNumeratorItemEntity(table Qm_Numerator_Item) 4 - QmNumeratorItemCodeEntity(table Qm_Numerator_Item_Code).

Haw you engin generate sql you know beter.

Thanks

Please provide the information that's being asked for. We don't have your code in front of us, and we don't experience slowdowns nor did we receive other reports about this, so we need detailed information to reproduce the issue. If we can't reproduce it, we can't help you.

The queries you posted aren't about the same thing: the first query fetches just 1 field and not an entire entity, nor is the query not complete: it's likely part of a bigger query, and as you didn't specify any fetch code whatsoever, we can't determine what's wrong exactly.

As the query generated apparently takes along time, the timeout will kick in. As Walaa described, DISTINCT is emitted in every entity fetch if duplicates could occur. This was already the case in v2.5, so it's beyond me why: 1) your v2.5 query fetches just 1 field and 2) has no DISTINCT.

Are you sure you've copied/pasted the EXACT query ? Using DQE tracing, I pressume?

Looking into the v2.5 design guidelines + source code I see DISTINCT isn't always emitted in the same situations.

Will look into it.

(edit) found it!

Stay tuned, it's a little bug in the runtime lib.

(edit) Fixed it. See the attached DLL for the fix.

The issue was only in Adapter and was caused due to the removal of a second code path for the case where 0 relations were passed to an entity collection fetch. The single codepath which was left was refactored to take both cases, however we forgot to port an optimization for the special case where there were no relations present: in that case there can't be any duplicates in the resultset as entities are fetched, so DISTINCT isn't necessary .

Hi, Thanks, with DLL that you sent me it work good.