我用平台开发了公司里面自己用的生产管理系统,好几个说有一个模块数据显示慢,其实就是查询生成上年同期原矿和上年同期金属两列的时候慢了。我今天改进了一下,由原来的DLOOKUP函数改为子查询,速度快了不少,在这里分享一下,猿友们自己体会:
原查询:
Select TMP_tbl原矿产量.日期, TMP_tbl原矿产量.日产量, TMP_tbl原矿产量.日品位, TMP_tbl原矿产量.日金属, TMP_tbl原矿产量.月产量, TMP_tbl原矿产量.月品位, TMP_tbl原矿产量.月金属, TMP_tbl原矿产量.年产量, TMP_tbl原矿产量.年金属, DLookUp("年产量","TMP_tbl原矿产量","日期=" & SQLDate(DateAdd("yyyy",-1,TMP_tbl原矿产量.日期))) AS 上年同期原矿, DLookUp("年金属","TMP_tbl原矿产量","日期=" & SQLDate(DateAdd("yyyy",-1,TMP_tbl原矿产量.日期))) AS 上年同期金属
FROM TMP_tbl原矿产量
Where TMP_tbl原矿产量.日期>=FirstDayOfYear(Date())
orDER BY TMP_tbl原矿产量.日期 DESC;
改进后的查询:
Select a.日期, a.日产量, a.日品位, a.日金属, a.月产量, a.月品位, a.月金属, a.年产量, a.年金属, (Select b.年产量 FROM TMP_tbl原矿产量 as b Where b.日期=dateadd("yyyy",-1,a.日期)) AS 上年同期原矿, (Select b.年金属 FROM TMP_tbl原矿产量 as b Where b.日期=dateadd("yyyy",-1,a.日期)) AS 上年同期金属
FROM TMP_tbl原矿产量 AS a
Where a.日期>=FirstDayOfYear(Date())
orDER BY a.日期 DESC;