大表关联后，SQL执行慢（大表数据都过万条）

Question

下记SQL执行很慢，因为数据量很大，4个大表关联，请各位帮忙，如何优化
SELECT 
                                     DISTINCT 
                                     EMPLOYEE.NAME AS NAME,
                                     CHAINSTORE.STORENAME AS STOREID,
                                     ORDERINFO.ORDERID AS ORDERID,
                                     ORDERINFO.ORDERNAME AS ORDERNAME,
                                     CONVERT(VARCHAR(10), COMMODITYFLOW.MODIFIEDDATE, 120 ) AS MODIFIEDDATE,
                                     HQPRODUCTSPECIFICATIONS.HQPRODUCTSPECIFICATIONS AS HQPRODUCTSPECIFICATIONS,
                                     HQPRODUCTSPECIFICATIONS.HQUNITPRICE AS HQUNITPRICE,
                                     SUM(COMMODITYENTRY.QUANTITY) OVER (PARTITION BY CONVERT(VARCHAR(10), COMMODITYFLOW.MODIFIEDDATE, 120 ),COMMODITYENTRY.ORDERID,HQPRODUCTSPECIFICATIONS.HQPRODUCTSPECIFICATIONS) AS QUANTITY                                 FROM
                                     COMMODITYFLOW, --数据过100万条左右
                                     ASSIGN_FINAL,  --数据过10万条左右
                                     COMMODITYENTRY,--数据过60万条左右
                                     ORDERINFO,     --数据10万条左右
                                     EMPLOYEE,      --数据不足1千条
                                     COMMODITY,     --数据不足1千条
                                     HQPRODUCTSPECIFICATIONS, --数据不足1百条
                                     CHAINSTORE --数据不足1百条
                             WHERE
                                  ASSIGN_FINAL.ISCURRENT = 1
                                  AND COMMODITYFLOW.STATEID = '35'
                                  AND ASSIGN_FINAL.ORDERID = COMMODITYENTRY.ZZORDERNO
                                  AND COMMODITYFLOW.COMMODITYENTRYID = COMMODITYENTRY.COMMODITYENTRYID
                                  AND COMMODITY.COMMODITYID = COMMODITYENTRY.COMMODITYID
                                  AND ORDERINFO.ORDERID = COMMODITYENTRY.ORDERID
                                  AND EMPLOYEE.EMPLOYEEID = ASSIGN_FINAL.EMPLOYEEID
                                  AND CHAINSTORE.STOREID = ISNULL(ORDERINFO.SZSTORE,ORDERINFO.STOREID)
                                  AND CHAINSTORE.INUSE = 1
                                  AND COMMODITY.HQPRODUCTSPECIFICATIONS = HQPRODUCTSPECIFICATIONS.HQPRODUCTSPECIFICATIONSID

Answer 1

不要吧 这么多表全部都关联起来
先找一张主表，
把次关键的字段，直接写在select 后面 

Answer 2

SELECT 
                                     DISTINCT 
                                     EMPLOYEE.NAME AS NAME,
                                     CHAINSTORE.STORENAME AS STOREID,
                                     (select 字段 from COMMODITYENTRY where YENTRYID = COMMODITYENTRY.COMMODITYENTRYID) as 字段2

Answer 3

SELECT  
  DISTINCT  
  EMPLOYEE.NAME AS NAME,
  CHAINSTORE.STORENAME AS STOREID,
  (select 字段 from COMMODITYENTRY where YENTRYID = COMMODITYENTRY.COMMODITYENTRYID) as 字段2 ......from 
......