我有两个名为Header和Detail的表。这两个表都有InvoiceNumber(双)列和YearMonthCode(char4)列。
在标题表中有一列LastChangedDate(char(10),在详细信息表中有列ItemNumber(varchar(16))和OrderedQty(integer)
在我进一步讨论之前,让我说一下,我没有设计这些表,除了添加或删除索引或存储过程之外,不能对它们进行更改。我不会将日期存储为字符串,但我必须以以下形式(mm-dd-yyyy)处理它
表头约有900000条记录,明细表约有2500000条记录年-月代码列从1301(2013年1月)到1312(2013年12月)
我的查询目的是返回在设置的lastdatechage范围内的特定项目的总和(OrderedQty)。
这是我提出的查询,它确实返回了我需要的信息,但每个项目大约需要30秒到45秒,整个项目报告有600到100个项目。。。。。
我将此作为存储过程GetItemTotal(在itm-varchar(16),在ymcode(char(4),在SDate Date,在EDate Date,out total 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个项目,您可能会使其余的处理更加高效,但这超出了问题的范围。)