The optimization process and recommendations:

1. Avoid Algebric Transformation On Indexable Expressions (modified query below): When algebric modifications are applied to an indexable expression, the database won't be able to utilize the index efficiently for search operations.
2. Mixed Order By Directions Prevents Index Use (query line: 85): The database will not use a sorting index (if exists) in cases where the query mixes ASC (the default if not specified) and DESC order. To avoid filesort, you may consider using the same order type for all columns. Another option that will allow you to switch one direction to another is to create a new reversed "sort" column (max_sort - sort) and index it instead.
3. Use UNION ALL instead of UNION (query line: 57): Always use UNION ALL unless you need to eliminate duplicate records. By using UNION ALL, you'll avoid the expensive distinct operation the database applies when using a UNION clause.

The optimized query:

SELECT
        IDAudit,
        TableName,
        dbo_Audit.RowID,
        ChangeDate,
        IDAPSCase 
    FROM
        dbo_APSCEI 
    INNER JOIN
        (
            dbo_Audit 
        INNER JOIN
            dbo_TableType 
                ON dbo_Audit.IDTableType = dbo_TableType.IDTableType
            ) 
                ON dbo_APSCEI.IDAPSCEI = dbo_Audit.RowID 
        WHERE
            (
                (
                    (
                        TableName
                    ) = "APSCEI"
                ) 
                AND (
                    IDAPSCase = 379017
                )
            ) 
        UNION
        SELECT
            IDAudit,
            TableName,
            RowID,
            ChangeDate,
            IDAPSCase 
        FROM
            dbo_APCSUN 
        INNER JOIN
            (
                dbo_Audit 
            INNER JOIN
                dbo_TableType 
                    ON dbo_Audit.IDTableType = dbo_TableType.IDTableType
                ) 
                    ON dbo_APCSUN.IDAPCSUN = dbo_Audit.RowID 
            WHERE
                (
                    (
                        (
                            TableName
                        ) = "APCSUN"
                    ) 
                    AND (
                        IDAPSCase = 379017
                    )
                ) 
            UNION
            SELECT
                IDAudit,
                TableName,
                RowID,
                ChangeDate,
                IDAPSCase 
            FROM
                dbo_APSCAI 
            INNER JOIN
                (
                    dbo_Audit 
                INNER JOIN
                    dbo_TableType 
                        ON dbo_Audit.IDTableType = dbo_TableType.IDTableType
                    ) 
                        ON dbo_APSCAI.IDAPSCAI = dbo_audit.RowID 
                WHERE
                    (
                        (
                            (
                                TableName
                            ) = "APSCAI"
                        ) 
                        AND (
                            IDAPSCase = 379017
                        )
                    ) 
                ORDER BY
                    ChangeDate DESC,
                    IDAPSCase