>>>>>I have trust in this
>>>>>
>>>>>SELECT TI.cTrans_fk, TI.cInsurances_fk;
>>>>> FROM (select curTemp.cTrans_pk , max(T.iSequence_Number) iSequence_Number;
>>>>> from curTemp JOIN Trans_Insurances T ON curTemp.cTrans_pk = T.cTrans_fk ;
>>>>> GROUP BY 1) X ;
>>>>> JOIN Trans_Insurances TI ;
>>>>> ON TI.cTrans_fk = X.cTrans_pk AND TI.iSequence_Number = X.iSequence_Number;
>>>>> into cursor curTemp1
>>>>>
>>>>
>>>>Seems to be a little bit faster, but still quite slow...
>>>
>>>post sys(3054) and current exec time
>>
>>Time: 3.804
>>SELECT TI.cTrans_fk as cTrans_pk, ;
>>TI.cInsurances_fk ;
>>FROM (select curTemp.cTrans_pk, max(T.iSequence_Number) iSequence_Number ;
>>from curTemp JOIN Trans_Insurances T ;
>>ON curTemp.cTrans_pk = T.cTrans_fk GROUP BY 1) X ;
>>JOIN Trans_Insurances TI ;
>>ON TI.cTrans_fk = X.cTrans_pk AND ;
>>TI.iSequence_Number = X.iSequence_Number into cursor curTemp1
>>
>>Rushmore optimization level for intermediate result: none
>>Rushmore optimization level for table t: none
>>Joining intermediate result and table t using index tag Ctrans_fk
>>Rushmore optimization level for intermediate result: none
>>Rushmore optimization level for table ti: none
>
>
>Joining intermediate result and table ti using temp index
>
>
>>
>>I'm running everything locally with SET DELETED ON.
>
>for testing try and post sys(3054)
>
>
>SELECT TI.cTrans_fk as cTrans_pk, ;
>TI.cInsurances_fk ;
>FROM (select curTemp.cTrans_pk, max(T.iSequence_Number) iSequence_Number ;
>from curTemp JOIN Trans_Insurances T ;
>ON curTemp.cTrans_pk = T.cTrans_fk GROUP BY 1) X ;
>JOIN Trans_Insurances TI ;
>ON TI.cTrans_fk = X.cTrans_pk into cursor curTemp1
>
>*expected
>Rushmore optimization level for intermediate result: none
>Rushmore optimization level for table t: none
>Joining intermediate result and table t using index tag Ctrans_fk
>Rushmore optimization level for intermediate result: none
>Rushmore optimization level for table ti: none
>Joining intermediate result and table ti using index tag Ctrans_fk
>
>
Time: 0.260
Rushmore optimization level for intermediate result: none
Rushmore optimization level for table t: none
Joining intermediate result and table t using index tag Ctrans_fk
Rushmore optimization level for intermediate result: none
Rushmore optimization level for table ti: none
Joining intermediate result and table ti using index tag Ctrans_fk
If it's not broken, fix it until it is.
My Blog