SQL语句优化

--好多字查询啊！ 先帮整理，各位看看
SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY 生产号 ASC ) AS 序号 ,
                    *
          FROM      ( (SELECT   tr.packageserialnum 生产号 ,
                                tr.packagecode 包编号 ,
                                tr.packagecode 包名称 ,
                                tr.boilersequencecode 锅次 ,
                                tr.tryforoffice 发放科室 ,
                                tr.sterilizetime 灭菌日期 ,
                                ( SELECT    pa.patientname
                                  FROM      patient AS pa
                                  WHERE     tr.forpatient = pa.patientcode
                                ) 使用患者 ,
                                tr.operatetime 使用时间 ,
                                ( SELECT    pe.username
                                  FROM      personnel AS pe ,
                                            custompackage AS cp
                                  WHERE     cp.cleanoutperson = pe.personcode
                                            AND cp.packagelist LIKE '%'
                                            + tr.packageserialnum + '%'
                                ) 清洗人 ,
                                ( SELECT    pe.username
                                  FROM      personnel AS pe ,
                                            custompackage AS cp
                                  WHERE     cp.checkoutperson = pe.personcode
                                            AND cp.packagelist LIKE '%'
                                            + tr.packageserialnum + '%'
                                ) 核对人 ,
                                ( SELECT    pe.username
                                  FROM      personnel AS pe ,
                                            custompackage AS cp
                                  WHERE     cp.packperson = pe.personcode
                                            AND cp.packagelist LIKE '%'
                                            + tr.packageserialnum + '%'
                                ) 打包人 ,
                                ( SELECT    pe.username
                                  FROM      personnel AS pe
                                  WHERE     tr.sterilizeperson = pe.personcode
                                ) 灭菌人 ,
                                ( SELECT    pe.username
                                  FROM      personnel AS pe
                                  WHERE     tr.qualityperson = pe.personcode
                                ) 质检人 ,
                                CASE WHEN ( SELECT  pe.username
                                            FROM    personnel AS pe
                                            WHERE   tr.provideperson = pe.personcode
                                          ) IS NULL
                                     THEN LTRIM(tr.provideperson)
                                     ELSE ( SELECT  pe.username
                                            FROM    personnel AS pe
                                            WHERE   tr.provideperson = pe.personcode
                                          )
                                END AS 发放人 ,
                                tr.invalidatetime 失效日期
                       FROM     packagetrackrecord AS tr
                       WHERE    tr.sterilizeperson IS NOT NULL
                                AND packagecode NOT IN ( SELECT
                                                              packagecode
                                                         FROM packageinfo ))
                      UNION ALL
                      ( SELECT  tr.packageserialnum 生产号 ,
                                tr.packagecode 包编号 ,
                                ( SELECT    pt.typename
                                  FROM      packtype AS pt
                                            JOIN packageinfo AS pi ON ( pi.typecode = pt.typecode )
                                  WHERE     pi.packagecode = tr.packagecode
                                ) AS 包名称 ,
                                tr.boilersequencecode 锅次 ,
                                tr.tryforoffice 发放科室 ,
                                tr.sterilizetime 灭菌日期 ,
                                ( SELECT    pa.patientname
                                  FROM      patient AS pa
                                  WHERE     tr.forpatient = pa.patientcode
                                ) 使用患者 ,
                                tr.operatetime 使用时间 ,
                                ( SELECT    pe.username
                                  FROM      personnel AS pe
                                  WHERE     tr.cleanoutperson = pe.personcode
                                ) 清洗人 ,
                                ( SELECT    pe.username
                                  FROM      personnel AS pe
                                  WHERE     tr.checkoutperson = pe.personcode
                                ) 核对人 ,
                                ( SELECT    pe.username
                                  FROM      personnel AS pe
                                  WHERE     tr.packperson = pe.personcode
                                ) 打包人 ,
                                ( SELECT    pe.username
                                  FROM      personnel AS pe
                                  WHERE     tr.sterilizeperson = pe.personcode
                                ) 灭菌人 ,
                                ( SELECT    pe.username
                                  FROM      personnel AS pe
                                  WHERE     tr.qualityperson = pe.personcode
                                ) 质检人 ,
                                CASE WHEN ( SELECT  pe.username
                                            FROM    personnel AS pe
                                            WHERE   tr.provideperson = pe.personcode
                                          ) IS NULL
                                     THEN LTRIM(tr.provideperson)
                                     ELSE ( SELECT  pe.username
                                            FROM    personnel AS pe
                                            WHERE   tr.provideperson = pe.personcode
                                          )
                                END AS 发放人 ,
                                tr.invalidatetime 失效日期
                        FROM    packagetrackrecord AS tr
                        WHERE   tr.sterilizeperson IS NOT NULL
                                AND packagecode IN ( SELECT packagecode
                                                     FROM   packageinfo )
                      )
                    ) AS DATA
        ) AS data
WHERE   序号 BETWEEN 1 AND 25
ORDER BY 序号