[firebird-support] Though the query became faster, stats IMO went south

[venussof...@gmail.com [firebird-support]]

Hi all

Learning from one of the replies from Set on my other thread I tried to replace 
my SP with an improved one.  One that would return two results (date range) in 
one row instead of two calls to the older SP.

I can't use CTEs in the Remote Views where the final query is used, so that is 
the reason why I could not use the original suggestion by Set.

So with the new stats but a faster query, it that okay?  Or should I stick with 
the older SPs which were tad slower but better stats.  Do I need indexes with 
the new SP?

Please advise

Thanks and regards
Bhavbhuti

Older query:

SELECT ...
    FROM tSupport
        JOINs...
    WHERE CAST(tSupport.tDt AS DATE) BETWEEN (SELECT US1.dValue
    FROM dSetValue('Current Year', 'From') US1)
        AND (SELECT US2.dValue
    FROM dSetValue('Current Year', 'To') US2)
    ORDER BY...

has this stats:
PLAN JOIN (US1 NATURAL, US2 INDEX (PK_USETTINGS_IID))
PLAN JOIN (US1 NATURAL, US2 INDEX (PK_USETTINGS_IID))
PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (SORT (JOIN (TSUPPORT NATURAL, CLIENT 
INDEX (PK_MACCOUNTS_IID))), CLIENTCITY INDEX (PK_MLOOKUPS_IID)), MSRNOS INDEX 
(PK_MSRNOS_IID)), SOFTWARE INDEX (PK_MITEMS_IID)), SOFTWAREHSCODE INDEX 
(PK_MLOOKUPS_IID)), SR INDEX (PK_MACCOUNTS_IID)), SRCITY INDEX 
(PK_MLOOKUPS_IID))


Executing statement...
Statement executed (elapsed time: 0.000s).
1462 fetches, 8 marks, 0 reads, 8 writes.
0 inserts, 0 updates, 0 deletes, 266 index, 133 seq.
Delta memory: 65060 bytes.
Total execution time: 0.106s
Script execution finished.


New query:

SELECT ...
    FROM tSupport
        JOINs...
        JOIN (SELECT dFromDt, dToDt
                    FROM dSetRange('Current Year', 'From', 'To')) dSR  
                        ON tSupport.tDT BETWEEN dSR.dFromDt AND dSR.dToDt
    ORDER BY ...

now has this stats:
PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (TSUPPORT NATURAL, 
CLIENT INDEX (PK_MACCOUNTS_IID)), CLIENTCITY INDEX (PK_MLOOKUPS_IID)), MSRNOS 
INDEX (PK_MSRNOS_IID)), SOFTWARE INDEX (PK_MITEMS_IID)), SOFTWAREHSCODE INDEX 
(PK_MLOOKUPS_IID)), SR INDEX (PK_MACCOUNTS_IID)), SRCITY INDEX 
(PK_MLOOKUPS_IID)), SORT (JOIN (US1 NATURAL, US2 INDEX (PK_USETTINGS_IID)))))


Executing statement...
Statement executed (elapsed time: 0.000s).
45026 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 7323 index, 7245 seq.
Delta memory: 58340 bytes.
Total execution time: 0.087s
Script execution finished.