我有两个表分别名为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个项目来提高其余处理的效率,但这超出了问题的范围。)