> <SL2> What is the PLAN which is generated by your revised view query?
>
>
> <SL2> 80ms is not long, have you tested the 2 views from a fresh start?
>
> It is possible that the performance numbers are being skewed by file/data caching...
>
>
> <SL2> Have you tested "my view" with the original query?
>
> It is possible that "my view" could have been slower on it's own but when used in the query, "my view" could be faster, by allowing the optimizer to consider the inner joins, instead of the left joins in your "faster" view.
>

Hi and thanks,
I've tested again on backup/restored file and restarted server.
Because both view sql's resulted in similar results, i tested the problem query. Results:

With the original view:

Plan
PLAN (TSG TP1 RDB$DATABASE NATURAL)
PLAN (TSG TP2 RDB$DATABASE NATURAL)
PLAN (TSG DTP RDB$DATABASE NATURAL)
PLAN (TSG STO RDB$DATABASE NATURAL)
PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (TSG PTP tPaymentType NATURAL, TSG TPTM INDEX (PK_tPaymentTypeMain), TSG SUP INDEX (FK_tSupplyGRPPayType), TSG OWN tOwner INDEX (RDB$PRIMARY53), TSG OTP INDEX (RDB$PRIMARY52)), JOIN (TSG TP1 P INDEX (RDB$PRIMARY55), JOIN (JOIN (TSG TP1 KU INDEX (FK_tKeyUser_Key), TSG TP1 K INDEX (PK_tKey)), TSG TP1 U INDEX (UNQ1_UCS_USERS)))), JOIN (TSG TP2 P INDEX (RDB$PRIMARY55), JOIN (JOIN (TSG TP2 KU INDEX (FK_tKeyUser_Key), TSG TP2 K INDEX (PK_tKey)), TSG TP2 U INDEX (UNQ1_UCS_USERS)))), JOIN (TSG DTP T INDEX (RDB$3), JOIN (TSG DTP R INDEX (FK_tDocTypeUserRightsDok), TSG DTP U INDEX (UNQ1_UCS_USERS)))), JOIN (TSG STO T INDEX (RDB$PRIMARY72), JOIN (TSG STO R INDEX (FK_tStorageUserRightsStor), TSG STO U INDEX (UNQ1_UCS_USERS)))), TOP INDEX (RDB$PRIMARY52), TSD INDEX (RDB$FOREIGN177)))

Adapted Plan
PLAN (TSG TP1 RDB$DATABASE NATURAL) PLAN (TSG TP2 RDB$DATABASE NATURAL) PLAN (TSG DTP RDB$DATABASE NATURAL) PLAN (TSG STO RDB$DATABASE NATURAL) PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (TSG PTP tPaymentType NATURAL, TSG TPTM INDEX (PK_tPaymentTypeMain), TSG SUP INDEX (FK_tSupplyGRPPayType), TSG OWN tOwner INDEX (INTEG_363), TSG OTP INDEX (INTEG_362)), JOIN (TSG TP1 P INDEX (INTEG_365), JOIN (JOIN (TSG TP1 KU INDEX (FK_tKeyUser_Key), TSG TP1 K INDEX (PK_tKey)), TSG TP1 U INDEX (UNQ1_UCS_USERS)))), JOIN (TSG TP2 P INDEX (INTEG_365), JOIN (JOIN (TSG TP2 KU INDEX (FK_tKeyUser_Key), TSG TP2 K INDEX (PK_tKey)), TSG TP2 U INDEX (UNQ1_UCS_USERS)))), JOIN (TSG DTP T INDEX (INTEG_313), JOIN (TSG DTP R INDEX (FK_tDocTypeUserRightsDok), TSG DTP U INDEX (UNQ1_UCS_USERS)))), JOIN (TSG STO T INDEX (INTEG_382), JOIN (TSG STO R INDEX (FK_tStorageUserRightsStor), TSG STO U INDEX (UNQ1_UCS_USERS)))), TOP INDEX (INTEG_362), TSD INDEX (INTEG_487)))

------ Performance info ------
Prepare time = 0ms
Execute time = 577ms
Avg fetch time = 577.00 ms
Current memory = 9 714 532
Max memory = 10 881 708
Memory buffers = 2 048
Reads from disk to cache = 5 707
Writes from cache to disk = 0
Fetches from cache = 446 209

IR 110324
NIR 2

===================================

With new view (with inner instead of left joins):

PLAN (TSG STO RDB$DATABASE NATURAL)
PLAN (TSG DTP RDB$DATABASE NATURAL)
PLAN (TSG TP1 RDB$DATABASE NATURAL)
PLAN (TSG TP2 RDB$DATABASE NATURAL)
PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (TSG TP1 P NATURAL, JOIN (JOIN (TSG TP1 KU INDEX (FK_tKeyUser_Key), TSG TP1 K INDEX (PK_tKey)), TSG TP1 U INDEX (UNQ1_UCS_USERS))), JOIN (TSG DTP T NATURAL, JOIN (TSG DTP R INDEX (FK_tDocTypeUserRightsDok), TSG DTP U INDEX (UNQ1_UCS_USERS))), JOIN (TSG STO T NATURAL, JOIN (TSG STO R INDEX (FK_tStorageUserRightsStor), TSG STO U INDEX (UNQ1_UCS_USERS))), TSG SUP INDEX (RDB$FOREIGN182, RDB$FOREIGN183)), TSG PTP tPaymentType INDEX (PK_tPaymentType), TSG OTP INDEX (RDB$PRIMARY52), TSG OWN tOwner INDEX (RDB$PRIMARY53)), TSG TPTM INDEX (PK_tPaymentTypeMain)), JOIN (TSG TP2 P INDEX (RDB$PRIMARY55), JOIN (JOIN (TSG TP2 KU INDEX (FK_tKeyUser_Key), TSG TP2 K INDEX (PK_tKey)), TSG TP2 U INDEX (UNQ1_UCS_USERS)))), TOP INDEX (RDB$PRIMARY52), TSD INDEX (RDB$FOREIGN177)))

Adapted Plan
PLAN (TSG STO RDB$DATABASE NATURAL) PLAN (TSG DTP RDB$DATABASE NATURAL) PLAN (TSG TP1 RDB$DATABASE NATURAL) PLAN (TSG TP2 RDB$DATABASE NATURAL) PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (TSG TP1 P NATURAL, JOIN (JOIN (TSG TP1 KU INDEX (FK_tKeyUser_Key), TSG TP1 K INDEX (PK_tKey)), TSG TP1 U INDEX (UNQ1_UCS_USERS))), JOIN (TSG DTP T NATURAL, JOIN (TSG DTP R INDEX (FK_tDocTypeUserRightsDok), TSG DTP U INDEX (UNQ1_UCS_USERS))), JOIN (TSG STO T NATURAL, JOIN (TSG STO R INDEX (FK_tStorageUserRightsStor), TSG STO U INDEX (UNQ1_UCS_USERS))), TSG SUP INDEX (INTEG_492, INTEG_493)), TSG PTP tPaymentType INDEX (PK_tPaymentType), TSG OTP INDEX (INTEG_362), TSG OWN tOwner INDEX (INTEG_363)), TSG TPTM INDEX (PK_tPaymentTypeMain)), JOIN (TSG TP2 P INDEX (INTEG_365), JOIN (JOIN (TSG TP2 KU INDEX (FK_tKeyUser_Key), TSG TP2 K INDEX (PK_tKey)), TSG TP2 U INDEX (UNQ1_UCS_USERS)))), TOP INDEX (INTEG_362), TSD INDEX (INTEG_487)))

------ Performance info ------
Prepare time = 0ms
Execute time = 1s 14ms
Avg fetch time = 1 014.00 ms
Current memory = 10 055 072
Max memory = 11 234 724
Memory buffers = 2 048
Reads from disk to cache = 7 189
Writes from cache to disk = 5
Fetches from cache = 619 697

IR 120368
NIR 28836

IF you want i can sent you the database (20mb compressed).