SELECT
D.SECT||CHR(9)||A.EXCH||CHR(9)||B.AFT||CHR(9)||A.FILE_NO||CHR(9)||REPLACE(REPLACE(REPLACE(B.LOC_NAME, CHR(13), ' '), CHR(10), ' '), CHR(9), ' ')||CHR(9)||REPLACE(REPLACE(REPLACE(B.LOC_ADDRESS, CHR(13), ' '), CHR(10), ' '), CHR(9), ' ')||CHR(9)||B.BLDG_TYPE||CHR(9)||B.BLDG_CLASS||CHR(9)||REPLACE(REPLACE(REPLACE(B.LOC_REM, CHR(13), ' '), CHR(10), ' '), CHR(9), ' ')||CHR(9)||TO_CHAR(B.BIS_DATE, 'DD-MON-YYYY')||CHR(9)||TO_CHAR(B.STOREY)||CHR(9)||TO_CHAR(B.R)||CHR(9)||TO_CHAR(B.AREA)||CHR(9)||TO_CHAR(B.FC_DEMAND)||CHR(9)||TO_CHAR(B.EST_BW_PAIRS)||CHR(9)||F.ARCH_CO_NAME||CHR(9)||G.DEV_NAME||CHR(9)||REPLACE(REPLACE(REPLACE(A.FILE_REM, CHR(13), ' '), CHR(10), ' '), CHR(9), ' ')||CHR(9)||DECODE(SIGN(TO_DATE('31-DEC-2998', 'DD-MON-YYYY')-NVL(TRUNC(A.FILE_BW_ASS_DATE), SYSDATE)), -1, NULL, TO_CHAR(A.FILE_BW_ASS_DATE, 'DD-MON-YYYY'))||CHR(9)||TWONINFO(B.BLDID, 'AAA')||CHR(9)||TWONINFO(B.BLDID, 'HCL')||CHR(9)|| TWONINFO(B.BLDID, 'NTT')||CHR(9)||TWONINFO(B.BLDID, 'NWT')||CHR(9)||TWONINFO(B.BLDID, 'WCL')||CHR(9)|| TWONINFO2(B.BLDID, 'OTH')||CHR(9)||TWO_N_INFO_NAME(B.BLDID)||CHR(9)||TO_CHAR(A.FILE_COMP_DATE, 'DD-MON-YYYY')||CHR(9)||BLDG_DPS1(B.BLDID)FROM
OPI_PROJECT A,
OPI_BUILDING B,
BW_ACCESS C,
BMS_EXCH_PCS D,
ARCH_INFO E,
ARCH_INFO F,
TWON_BLDG_DEVELOPER G
WHERE
A.EXCH <> 'ZZZ'
AND A.EXCH = D.EXCHG(+)
AND A.PJID = B.PJID(+)
AND B.BLDID = C.BLDID
AND A.ARCH_CODE = E.ARCH_CODE(+)
AND A.DEVELOPER_CODE = F.ARCH_CODE(+)
AND A.TWON_BLDG_DEV_CODE = G.DEV_CODE(+)
AND A.FILE_COMP_DATE >= TO_DATE('01-APR-1996', 'DD-MON-YYYY')
GROUP BY
D.SECT, A.EXCH, B.BLDGCODE, B.AFT, A.FILE_NO, B.LOC_NAME,
B.LOC_ADDRESS, B.BLDG_TYPE, B.BIS_DATE, B.LOC_REM,
B.BLDG_CLASS, B.STOREY, B.R, A.FILE_REM,
B.AREA, B.FC_DEMAND, B.EST_BW_PAIRS, F.ARCH_CO_NAME, A.FILE_BW_ASS_DATE, G.DEV_NAME,
A.PJID, B.BLDID, A.FILE_COMP_DATE
ORDER BY
A.EXCH, A.FILE_NO, B.BLDG_TYPE, A.FILE_COMP_DATE
不到一万条的记录,执行起来差不多要20分钟,为什么?
D.SECT||CHR(9)||A.EXCH||CHR(9)||B.AFT||CHR(9)||A.FILE_NO||CHR(9)||REPLACE(REPLACE(REPLACE(B.LOC_NAME, CHR(13), ' '), CHR(10), ' '), CHR(9), ' ')||CHR(9)||REPLACE(REPLACE(REPLACE(B.LOC_ADDRESS, CHR(13), ' '), CHR(10), ' '), CHR(9), ' ')||CHR(9)||B.BLDG_TYPE||CHR(9)||B.BLDG_CLASS||CHR(9)||REPLACE(REPLACE(REPLACE(B.LOC_REM, CHR(13), ' '), CHR(10), ' '), CHR(9), ' ')||CHR(9)||TO_CHAR(B.BIS_DATE, 'DD-MON-YYYY')||CHR(9)||TO_CHAR(B.STOREY)||CHR(9)||TO_CHAR(B.R)||CHR(9)||TO_CHAR(B.AREA)||CHR(9)||TO_CHAR(B.FC_DEMAND)||CHR(9)||TO_CHAR(B.EST_BW_PAIRS)||CHR(9)||F.ARCH_CO_NAME||CHR(9)||G.DEV_NAME||CHR(9)||REPLACE(REPLACE(REPLACE(A.FILE_REM, CHR(13), ' '), CHR(10), ' '), CHR(9), ' ')||CHR(9)||DECODE(SIGN(TO_DATE('31-DEC-2998', 'DD-MON-YYYY')-NVL(TRUNC(A.FILE_BW_ASS_DATE), SYSDATE)), -1, NULL, TO_CHAR(A.FILE_BW_ASS_DATE, 'DD-MON-YYYY'))||CHR(9)||TWONINFO(B.BLDID, 'AAA')||CHR(9)||TWONINFO(B.BLDID, 'HCL')||CHR(9)|| TWONINFO(B.BLDID, 'NTT')||CHR(9)||TWONINFO(B.BLDID, 'NWT')||CHR(9)||TWONINFO(B.BLDID, 'WCL')||CHR(9)|| TWONINFO2(B.BLDID, 'OTH')||CHR(9)||TWO_N_INFO_NAME(B.BLDID)||CHR(9)||TO_CHAR(A.FILE_COMP_DATE, 'DD-MON-YYYY')||CHR(9)||BLDG_DPS1(B.BLDID)FROM
OPI_PROJECT A,
OPI_BUILDING B,
BW_ACCESS C,
BMS_EXCH_PCS D,
ARCH_INFO E,
ARCH_INFO F,
TWON_BLDG_DEVELOPER G
WHERE
A.EXCH <> 'ZZZ'
AND A.EXCH = D.EXCHG(+)
AND A.PJID = B.PJID(+)
AND B.BLDID = C.BLDID
AND A.ARCH_CODE = E.ARCH_CODE(+)
AND A.DEVELOPER_CODE = F.ARCH_CODE(+)
AND A.TWON_BLDG_DEV_CODE = G.DEV_CODE(+)
AND A.FILE_COMP_DATE >= TO_DATE('01-APR-1996', 'DD-MON-YYYY')
GROUP BY
D.SECT, A.EXCH, B.BLDGCODE, B.AFT, A.FILE_NO, B.LOC_NAME,
B.LOC_ADDRESS, B.BLDG_TYPE, B.BIS_DATE, B.LOC_REM,
B.BLDG_CLASS, B.STOREY, B.R, A.FILE_REM,
B.AREA, B.FC_DEMAND, B.EST_BW_PAIRS, F.ARCH_CO_NAME, A.FILE_BW_ASS_DATE, G.DEV_NAME,
A.PJID, B.BLDID, A.FILE_COMP_DATE
ORDER BY
A.EXCH, A.FILE_NO, B.BLDG_TYPE, A.FILE_COMP_DATE
不到一万条的记录,执行起来差不多要20分钟,为什么?
解决方案 »
- 武汉有哪些网站是用ASP.Net做的,网上查的到得。
- GridView编辑状态同时有2个文件上传时出现问题
- textbox文本框怎么向查询数据库传变量数据?
- 在datagrid中如何合并相同的单元格
- 判断字符串问题
- 如何在本地建立http://www.163.com这样的域名,在IE打开的时候,访问本地硬盘上的一个站点。谢谢!
- 怎么获得在gridview中的linkbutton所在行的索引
- 我想做一个ASP.NET与NOTES达到数据交换,怎么实现?
- 菜鸟问题4:datagrid 删除记录时 如何加个“确认删除? yes no”
- 怎样给test函数添加参数,button1.attributes("onclick")="vbscript:test()"?
- 高分请教一个问题!!在线等。
- 改变FileUpLoad控件的显示文本?
我连仔细看你SQL的勇气都没了...
后来放到服务器上(4颗CPU+12G内存)只用0.29秒
2.不要用replace啦,让程序去处理字符替换吧,不要让sql去做这个
3.group by 整理一下,你这么多分组有几个有用的?用嵌套查询减少分组吧
4.确实看了头疼用查询分析器慢慢调吧...