none
帮忙看下这个sql。 RRS feed

  • 问题

  • 新接手一个数据库。发现其中存在很多这种查询。

     select lot_no,s_operation.operation,oper_desc,oper_desc_cn,EMP_NO_IN,emp_in.emp_name EMP_NAME_IN,QTY_IN,EMP_NO_OUT,emp_out.emp_name EMP_NAME_OUT,QTY_PASS,qty_fail  ,cast(0 as decimal) zero_in,cast(0 as decimal) zero_out,cast(0 as decimal) zero_fail,cast(0 as decimal) zero_wip  from waa_lot with(nolock index(waa_lot3))  inner join s_operation with(nolock) on waa_lot.facility = s_operation.facility and waa_lot.site = s_operation.site and waa_lot.operation = s_operation.operation  inner join s_employee emp_in with(nolock) on  waa_lot.facility = emp_in.facility and waa_lot.site = emp_in.site and waa_lot.EMP_NO_IN = emp_in.emp_no  inner join s_employee emp_out with(nolock) on waa_lot.facility = emp_out.facility and waa_lot.site = emp_out.site and waa_lot.EMP_NO_OUT = emp_out.emp_no  where waa_lot.facility = 'NEO' and waa_lot.site = 'SZ'   and date_tm between '2009-04-03 16:01:25.693' and '2011-04-03 16:01:25.693'    and LINE_ID = 4200    and (emp_no_in = '0027' or EMP_NO_OUT = '0027')      union all   select lot_no,s_operation.operation,oper_desc,oper_desc_cn,EMP_NO_IN,emp_in.emp_name EMP_NAME_IN,QTY_IN,EMP_NO_OUT,emp_out.emp_name EMP_NAME_OUT,QTY_PASS,qty_fail  ,cast(0 as decimal) zero_in,cast(0 as decimal) zero_out,cast(0 as decimal) zero_fail,cast(0 as decimal) zero_wip  from WAC_lot with(nolock index(WAC_lot3))  inner join s_operation with(nolock) on WAC_lot.facility = s_operation.facility and WAC_lot.site = s_operation.site and WAC_lot.operation = s_operation.operation  inner join s_employee emp_in with(nolock) on  WAC_lot.facility = emp_in.facility and WAC_lot.site = emp_in.site and WAC_lot.EMP_NO_IN = emp_in.emp_no  inner join s_employee emp_out with(nolock) on WAC_lot.facility = emp_out.facility and WAC_lot.site = emp_out.site and WAC_lot.EMP_NO_OUT = emp_out.emp_no  where WAC_lot.facility = 'NEO' and WAC_lot.site = 'SZ'   and date_tm between '2009-04-03 16:01:25.693' and '2011-04-03 16:01:25.693'    and LINE_ID = 4200    and (emp_no_in = '0027' or EMP_NO_OUT = '0027')    

    其中

    s_operation                只包含PK,PK栏位为FACILITY, SITE, OPERATION    共913笔数据


    waa_lot  包含三个非聚簇索引  数据量为4345956  ,第一个为LOT_NO上的索引,第二个为FACILITY,SITE,DATE_SYS和PROD的联合索引,第三个是FACILITY,SITE,LOT_NO和DATE_TIME的索引

    LOT_NO(唯一值有526757个) 

    FACILITY(当前只有一个唯一值,后续会有3个唯一值), SITE(当前只有一个唯一值,后续会存在3个值), DATE_SYS(时间栏位), PROD(805个唯一值)

    s_employee  只包含一个PK,PK栏位包含 FACILITY, SITE, EMP_NO   ,仅有5396笔数据


    WAC_lot  LOT_NO  FACILITY, SITE , DATE_SYS, PROD   FACILITY, SITE, LOT_NO(-), DATE_TM(-)

     

    wac_lot包含4112861笔数据,索引与WAA_LOT一样。

    包含227511个唯一的LOT_NO值   只有一个facility值 只有一个site值 DATE_SYS时间栏位  301个唯一的PROD值

    大家有什么好的建议吗?

     

     


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年5月19日 8:29

答案

全部回复

  • What kind of suggestion you are looking for? Did you check execution plan?
    2011年5月19日 15:19
  • What kind of suggestion you are looking for? Did you check execution plan?


    是的 我有检查过执行计划。全都是走的index seek或者clustered index seek。

    我想要的建议是:这些索引是否合理呢?另外类似这种sql的写法是否可以优化下。

    因为对优化部分不太在行,所以有点晕


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年5月20日 0:36
  • Plan of that sp sounds ok, and you have to understand business logica/requiremenl to modify it. Regarding necessary indices in tables, can't tell with single sp. You have to look at all kind of queries used, then check index usage dm.
    2011年5月20日 2:01
  • Plan of that sp sounds ok, and you have to understand business logica/requiremenl to modify it. Regarding necessary indices in tables, can't tell with single sp. You have to look at all kind of queries used, then check index usage dm.

     

    像那种基本上值都一样的栏位,有必要和其他栏位一起建立索引吗?


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年5月20日 2:09
  • Depends on how to query tables.
    2011年5月20日 3:25
  • 大部分都是上面那种的写法。~
    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年5月20日 3:27
  • This is big topic, almost no way to tell what to do just based on info you posted. Have to spend time to review db and queries to get the picture.
    2011年5月20日 3:35