数据报表运行速度狂慢，寻求好方法以提高程序性能！

kinmax 2005-08-17 03:55:51

前几天我在CSDN里提了个问题，请教高手帮忙做个报表，现在问题基本已经解决，现在碰到的问题是程序运行的速度很蛮，一万条不到的记录，需运行1分钟左右的时间。不知道有什么方法可以提高程序的运行效率，特请教。

原始贴子联结如下：http://community.csdn.net/Expert/topic/4206/4206056.xml?temp=.9261896

我是采用aw511（点点星灯）朋友提供的方法来做的，可以实现预期目的，并且运行速度也可以忍受。但问题是，用那种方法取出来的数据是每日汇总数据，而我要的是每月的汇总数据。我把每日的汇总数据取出来后，放在数组里，然后通过循环用SQL语句来判断数据是否存在，如果存在则更新数据，否则，数据插入。在这中间，就会大量涉及到数据库的联结和查询、更新及数据插入操作，比较耗时，不知道有哪位高手能帮我想个好的办法，最好能用一条SQL语句直接取出我想要的数据，那就不需要我频繁的去判断并操作数据库了。

我的SQL语句如下：

SELECT * FROM (
SELECT b.sTorihikisakicd,sKyaku=a.sKyaku0,dcKyakuKin=a.dcKyakuKin0,dcSyohiKin=a.dcSyohiKin0
FROM bmgnb10 a LEFT JOIN T_DMTORIHISAKISAKI b ON ltrim(rtrim(a.sOdrNcd))=LTRIM(RTRIM(b.sOdrNcd)) WHERE a.sKyaku0 between '20020800' and '20050899' AND b.sTorihikisakicd IS NOT NULL
UNION ALL SELECT b.sTorihikisakicd,sKyaku=a.sKyaku1,dcKyakuKin=a.dcKyakuKin1,dcSyohiKin=a.dcSyohiKin1
FROM bmgnb10 a LEFT JOIN T_DMTORIHISAKISAKI b ON ltrim(rtrim(a.sOdrNcd))=LTRIM(RTRIM(b.sOdrNcd)) WHERE a.sKyaku1 BETWEEN '20020800' AND '20050899' AND b.sTorihikisakicd IS NOT NULL
UNION ALL SELECT b.sTorihikisakicd,sKyaku=a.sKyaku2,dcKyakuKin=a.dcKyakuKin2,dcSyohiKin=a.dcSyohiKin2
FROM bmgnb10 a LEFT JOIN T_DMTORIHISAKISAKI b ON ltrim(rtrim(a.sOdrNcd))=LTRIM(RTRIM(b.sOdrNcd)) WHERE a.sKyaku2 BETWEEN '20020800' AND '20050899' AND b.sTorihikisakicd IS NOT NULL
UNION ALL SELECT b.sTorihikisakicd,sKyaku=a.sKyaku3,dcKyakuKin=a.dcKyakuKin3,dcSyohiKin=a.dcSyohiKin3
FROM bmgnb10 a LEFT JOIN T_DMTORIHISAKISAKI b ON ltrim(rtrim(a.sOdrNcd))=LTRIM(RTRIM(b.sOdrNcd)) WHERE a.sKyaku3 BETWEEN '20020800' AND '20050899' AND b.sTorihikisakicd IS NOT NULL
UNION ALL SELECT b.sTorihikisakicd,sKyaku=a.sKyaku4,dcKyakuKin=a.dcKyakuKin4,dcSyohiKin=a.dcSyohiKin4
FROM bmgnb10 a LEFT JOIN T_DMTORIHISAKISAKI b ON ltrim(rtrim(a.sOdrNcd))=LTRIM(RTRIM(b.sOdrNcd)) WHERE a.sKyaku4 BETWEEN '20020800' AND '20050899' AND b.sTorihikisakicd IS NOT NULL
UNION ALL SELECT b.sTorihikisakicd,sKyaku=a.sKyaku5,dcKyakuKin=a.dcKyakuKin5,dcSyohiKin=a.dcSyohiKin5
FROM bmgnb10 a LEFT JOIN T_DMTORIHISAKISAKI b ON ltrim(rtrim(a.sOdrNcd))=LTRIM(RTRIM(b.sOdrNcd)) WHERE a.sKyaku5 BETWEEN '20020800' AND '20050899' AND b.sTorihikisakicd IS NOT NULL
UNION ALL SELECT b.sTorihikisakicd,sKyaku=a.sKyaku6,dcKyakuKin=a.dcKyakuKin6,dcSyohiKin=a.dcSyohiKin6
FROM bmgnb10 a LEFT JOIN T_DMTORIHISAKISAKI b ON ltrim(rtrim(a.sOdrNcd))=LTRIM(RTRIM(b.sOdrNcd)) WHERE a.sKyaku6 BETWEEN '20020800' AND '20050899' AND b.sTorihikisakicd IS NOT NULL
UNION ALL SELECT b.sTorihikisakicd,sKyaku=a.sKyaku7,dcKyakuKin=a.dcKyakuKin7,dcSyohiKin=a.dcSyohiKin7
FROM bmgnb10 a LEFT JOIN T_DMTORIHISAKISAKI b ON ltrim(rtrim(a.sOdrNcd))=LTRIM(RTRIM(b.sOdrNcd)) WHERE a.sKyaku7 BETWEEN '20020800' AND '20050899' AND b.sTorihikisakicd IS NOT NULL
UNION ALL SELECT b.sTorihikisakicd,sKyaku=a.sKyaku8,dcKyakuKin=a.dcKyakuKin8,dcSyohiKin=a.dcSyohiKin8
FROM bmgnb10 a LEFT JOIN T_DMTORIHISAKISAKI b ON ltrim(rtrim(a.sOdrNcd))=LTRIM(RTRIM(b.sOdrNcd)) WHERE a.sKyaku8 BETWEEN '20020800' AND '20050899' AND b.sTorihikisakicd IS NOT NULL
UNION ALL SELECT b.sTorihikisakicd,sKyaku=a.sKyaku9,dcKyakuKin=a.dcKyakuKin9,dcSyohiKin=a.dcSyohiKin9
FROM bmgnb10 a LEFT JOIN T_DMTORIHISAKISAKI b ON ltrim(rtrim(a.sOdrNcd))=LTRIM(RTRIM(b.sOdrNcd)) WHERE a.sKyaku9 BETWEEN '20020800' AND '20050899' AND b.sTorihikisakicd IS NOT NULL
UNION ALL SELECT b.sTorihikisakicd,sKyaku=a.sKyaku10,dcKyakuKin=dcKyakuKin10,dcSyohiKin=a.dcSyohiKin10
FROM bmgnb10 a LEFT JOIN T_DMTORIHISAKISAKI b ON ltrim(rtrim(a.sOdrNcd))=LTRIM(RTRIM(b.sOdrNcd)) WHERE a.sKyaku10 BETWEEN '20020800' AND '20050899' AND b.sTorihikisakicd IS NOT NULL) t
GROUP BY t.sTorihikisakicd,t.sKyaku,t.dcKyakuKin,t.dcSyohiKin order by t.sKyaku desc

SQL语句仅供参考。

查询结果：
sTorihikisakicd sKyaku dcKyakuKin
0 2005 131 841000 42050
0 2005 131 860000 43000
0 2005 131 878000 43900
0 2005 131 883500 44175
0 2005 131 886000 44300
0 2005 131 888000 44400
0 2005 131 909500 45475
0 2005 131 910000 45500
0 2005 131 933000 46650
0 2005 131 950000 47500
0 2005 131 963500 48175
0 2005 131 991500 49575
0 2005 131 1040000 52000
0 2005 131 1050000 52500
0 2005 131 1080000 54000
0 2005 131 1120500 56025
0 2005 131 1150000 57500

……………………………….省略

...全文