我有两个表分别名为Header和Detail。这两个表都有一个InvoiceNumber(双精度)列和YearMonthCode(char4)列。

标题表中有一列LastChangedDate(char(10),详细信息表中有一列ItemNumber(varchar(16))和OrderedQty(integer)

在继续之前,我要说的是我没有设计这些表,除了添加或删除索引或存储过程外,不能更改它们。我不会将日期存储为字符串,但必须以以下格式(mm-dd-yyyy)处理

标题表大约有900,000条记录,详细表大约有2,500,000条记录
yearmonthcode列从1301(2013年1月)到1312(2013年12月)

我查询的目的是在设置的lastdatechage范围内返回特定项目的sum(OrderedQty)。

这是我提出的查询,它确实返回了我需要的信息,但每个项目大约需要30到45秒,整个项目报告600到100个项目.....

我将其作为存储过程
GetItemTotal(在varchar(16)中,在ymcode中(char(4),在SDate中,在EDate中,总int)

Select sum(D.OrderedQty) into total
FROM Header H use index (index_YMC)
Inner Join Detail D use index (index_YMC)
on H.invoicenumber = D.InvoiceNumber
   AND H.YearMonthCode = D.YearMonthCode
Where H.YearMonthCode = ymcode
  AND D.ItemNumber = itm
  AND STR_TO_DATE(H.LastChangeDate,'%m/%d/%YYYY')
      Between Sdate AND Edate;


我这样调用程序:

Call GetItemTotal('6458-20115','1311', '2013-11-15', '2013-11-30', @total)


实际上,这些全都是变量,但是我对测试值进行了硬编码。

这两个表在yearmonthcode列上都有PRIMARY索引和index_YMC。

说明如下

id select_type table type possible_keys key       key_len ref   rows    extra

1  SIMPLE      H     ref  Index_YMC     Index_YMC 5       const 100408  Using where

1  SIMPLE      D     ALL  Index_YMC     null      null    null  2032249 Using where;
                                                                        Using join buffer


我对数据库编程非常陌生,如果有人可以给我一些有关如何使此查询更快的想法,我将不胜感激。

最佳答案

如果您已经在对整个年度的值求和(在临时表的总体中),那么建议您在临时表的总体查询中添加另一列,得出类似以下内容的结果:

sum(case when STR_TO_DATE(LastChangeDate,'%m/%d/%YYYY') Between Sdate AND Edate
         then OrderedQty
    end) as DateRangeTotal


这应该完全不需要GetItemTotal存储过程。

(您可以通过使用基于集合的操作而不是循环遍历600个项目来提高其余处理的效率,但这超出了问题的范围。)

10-06 03:21
查看更多