Another case of mixing implicit and explicit joins and jumbling JOIN
criteria up in the WHERE clause...

SELECT DI.Reference_No_1,
DI.Reference_No_2,
DI.Document_Name,
DI.Document_No,
DI.Document_Date,
DI.Create_User,
DI.Create_Date,
DI.Ref_App_ID,
A.Full_Name,
A.First_Name,
A.Middle_Name,
A.Last_Name,
US.Name
FROM Document_Index DI
JOIN Account A on A.Reference_No_1 = DI.Reference_No_1
and A.APP_ID = DI.REF_APP_ID
JOIN USER_SECURITY US on US.User_Code = DI.Create_User
where
DI.DELETED = 0
AND DI.App_ID = 1
AND upper(A.Last_Name) Like 'A%'
Order By A.Full_Name

From the plan:
PLAN SORT (JOIN (A NATURAL,DI INDEX (RDB$FOREIGN86,RDB$FOREIGN55),US INDEX
(RDB$PRIMARY30)))

This is slow because Table A has no indexes that can be used for the
sort. You have two columns joining it to table DI, which references it
through a Foreign key so (hopefully) App_ID is the primary key of table
DI. But it could be that you have two FKs referring to the same table and
neither refers to the PK. Could be a pipeline that needs cleaning up.

Since you have Full_Name as a column in addition to the three individual
columns, if it is "Last_Name first" then index that column and use it for
the sort - but don't rely on this if there's a likelihood you have NULLs in
Full_Name arising from a defective concatenation formula in a trigger, like

new.Full_Name = new.Last_Name|| ', ' ||new.First_Name|| ' '||new.Middle_Name

This will store NULL in all cases where any single element is missing.

If you can't use Full_Name, index the other three name columns, either as a
single index in the right order left-to-right (if they are short enough) or
individually; or index US.Name and use that for the sort.

heLen


All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________