select b.fno 航班号,
b.fdest 目的站,
b.DEPTIME 起飞时间,
b.weight 总载量,
isnull(sum(a.weight), 0) 总订舱量,
b.weight - isnull(sum(a.weight), 0) 剩余量,
isnull(dbo.getbookingd(b.fno, b.fdep, b.fdest, b.fdate, 'CANYYB'), 0) as CANYYB,
isnull(dbo.getbookingd(b.fno, b.fdep, b.fdest, b.fdate, 'CKGSZX'), 0) as CKGSZX,
isnull(dbo.getbookingd(b.fno, b.fdep, b.fdest, b.fdate, 'SHEHYB'), 0) as SHEHYB,
isnull(dbo.getbookingd(b.fno, b.fdep, b.fdest, b.fdate, 'SZXdgb'), 0) as SZXdgb,
isnull(dbo.getbookingd(b.fno, b.fdep, b.fdest, b.fdate, 'SZXfbb'), 0) as SZXfbb,
isnull(dbo.getbookingd(b.fno, b.fdep, b.fdest, b.fdate, 'SZXJCB'), 0) as SZXJCB,
isnull(dbo.getbookingd(b.fno, b.fdep, b.fdest, b.fdate, 'SZXkjy'), 0) as SZXkjy,
isnull(dbo.getbookingd(b.fno, b.fdep, b.fdest, b.fdate, 'SZXynl'), 0) as SZXynl,
isnull(dbo.getbookingd(b.fno, b.fdep, b.fdest, b.fdate, 'TSN808'), 0) as TSN808,
isnull(dbo.getbookingd(b.fno, b.fdep, b.fdest, b.fdate, 'WUXJCB'), 0) as WUXJCB,
isnull(dbo.getbookingd(b.fno, b.fdep, b.fdest, b.fdate, 'WUXZKH'), 0) as WUXZKH
from tbl_booking a
right join (select DEPTIME, fno, fdep, fdest, Weight, fdate
from tbl_Availablespace
where fdate >= '2012-4-01'
and fdate <= '2012-4-10'
and Status <> '1'
and Status <> '7'
) b
on a.fno = b.fno
and a.fdest = b.fdest
and a.fdate = b.fdate
group by b.fno, b.fdest, b.DEPTIME, b.weight, b.fdate, b.fdep
order by b.fno
上述SQL语句是运行在MS SQL Server 2000上的，在查询过程中，几分钟都没有数据出来(接近6000条数据)。尝试去掉下面的代码进行查询时，1S的时间都不到就可以出结果了。
isnull(dbo.getbookingd(b.fno, b.fdep, b.fdest, b.fdate, 'CANYYB'), 0) as CANYYB,
isnull(dbo.getbookingd(b.fno, b.fdep, b.fdest, b.fdate, 'CKGSZX'), 0) as CKGSZX,
isnull(dbo.getbookingd(b.fno, b.fdep, b.fdest, b.fdate, 'SHEHYB'), 0) as SHEHYB,
isnull(dbo.getbookingd(b.fno, b.fdep, b.fdest, b.fdate, 'SZXdgb'), 0) as SZXdgb,
isnull(dbo.getbookingd(b.fno, b.fdep, b.fdest, b.fdate, 'SZXfbb'), 0) as SZXfbb,
isnull(dbo.getbookingd(b.fno, b.fdep, b.fdest, b.fdate, 'SZXJCB'), 0) as SZXJCB,
isnull(dbo.getbookingd(b.fno, b.fdep, b.fdest, b.fdate, 'SZXkjy'), 0) as SZXkjy,
isnull(dbo.getbookingd(b.fno, b.fdep, b.fdest, b.fdate, 'SZXynl'), 0) as SZXynl,
isnull(dbo.getbookingd(b.fno, b.fdep, b.fdest, b.fdate, 'TSN808'), 0) as TSN808,
isnull(dbo.getbookingd(b.fno, b.fdep, b.fdest, b.fdate, 'WUXJCB'), 0) as WUXJCB,
isnull(dbo.getbookingd(b.fno, b.fdep, b.fdest, b.fdate, 'WUXZKH'), 0) as WUXZKH
其实被去掉的代码就是通过调用函数进行累加重量，函数代码如下：
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION [dbo].[GetBookingd](@Fno as varchar(10),@Fdep as varchar(3),@Fdest as varchar(3),@Fdate as smalldatetime,@Agentcode as varchar(7))
RETURNS decimal(9) AS
BEGIN
DECLARE @weight AS decimal(9)
BEGIN
SELECT @weight = isnull(sum(weight),0)
FROM TBL_Booking
WHERE(Fno=@Fno AND Fdep = @Fdep And Fdest=@Fdest AND Fdate = @Fdate And Agentcode=@Agentcode and Status='SS')
END
RETURN @weight
END
我们可以看到函数及其简单，里面仅是对一张表进行查询的。而这张表TBL_BOOKING里面的数据有100多万条。
SELECT COUNT(*) CN FROM TBL_BOOKING;
CN
--------
1187631
select COUNT(*) CN FROM tbl_Availablespace;
-------
413419
select COUNT(*) CN FROM tbl_Availablespace where fdate >= '2012-4-01' and fdate <= '2012-4-10' and Status NOT IN ('1','7');
CN
-------
5828
根据函数中对表TBL_BOOKIN的WHERE条件，添加索引
CREATE INDEX XX ON TBL_BOOKING(Fno,Fdep,Fdest,Fdate,Agentcode,Status);
添加索引后，发出查询，6、7秒查出结果。
一般情况下，能用SQL语句表示结果的尽量用SQL语句表示。接下来尝试不调用函数，直接用SQL语句代替函数的调用。
WITH T AS (
select b.fno ,
b.fdest ,
b.DEPTIME ,
b.weight ,
B.FDEP,
B.FDATE,
isnull(sum(a.weight), 0) AS XA,
b.weight - isnull(sum(a.weight), 0) AS XB
from tbl_booking a
right join (select DEPTIME, fno, fdep, fdest, Weight, fdate
from tbl_Availablespace
where fdate >= '2012-4-01'
and fdate <= '2012-4-10'
and Status NOT IN ('1','7')
) b
on a.fno = b.fno
and a.fdest = b.fdest
and a.fdate = b.fdate
group by b.fno, b.fdest, b.DEPTIME, b.weight, b.fdate, b.fdep
) SELECT T.FNO 航班号,
T.fdest 目的站,
T.DEPTIME 起飞时间,
T.weight 总载量,
T.XA 总订舱量,
T.XB 剩余量,
(SELECT isnull(sum(weight),0)
FROM TBL_Booking C
WHERE T.Fno=C.Fno
AND T.Fdep = C.Fdep
And T.Fdest=C.Fdest
AND T.Fdate = C.Fdate
And C.Agentcode='CANYYB'
and C.Status='SS') AS CANYYB,
(SELECT isnull(sum(weight),0)
FROM TBL_Booking C
WHERE T.Fno=C.Fno
AND T.Fdep = C.Fdep
And T.Fdest=C.Fdest
AND T.Fdate = C.Fdate
And C.Agentcode='CKGSZX'
and C.Status='SS') AS CKGSZX,
(SELECT isnull(sum(weight),0)
FROM TBL_Booking C
WHERE T.Fno=C.Fno
AND T.Fdep = C.Fdep
And T.Fdest=C.Fdest
AND T.Fdate = C.Fdate
And C.Agentcode='SHEHYB'
and C.Status='SS') AS SHEHYB,
(SELECT isnull(sum(weight),0)
FROM TBL_Booking C
WHERE T.Fno=C.Fno
AND T.Fdep = C.Fdep
And T.Fdest=C.Fdest
AND T.Fdate = C.Fdate
And C.Agentcode='SZXdgb'
and C.Status='SS') AS SZXdgb,
(SELECT isnull(sum(weight),0)
FROM TBL_Booking C
WHERE T.Fno=C.Fno
AND T.Fdep = C.Fdep
And T.Fdest=C.Fdest
AND T.Fdate = C.Fdate
And C.Agentcode='SZXfbb'
and C.Status='SS') AS SZXfbb,
(SELECT isnull(sum(weight),0)
FROM TBL_Booking C
WHERE T.Fno=C.Fno
AND T.Fdep = C.Fdep
And T.Fdest=C.Fdest
AND T.Fdate = C.Fdate
And C.Agentcode='SZXJCB'
and C.Status='SS') AS SZXJCB,
(SELECT isnull(sum(weight),0)
FROM TBL_Booking C
WHERE T.Fno=C.Fno
AND T.Fdep = C.Fdep
And T.Fdest=C.Fdest
AND T.Fdate = C.Fdate
And C.Agentcode='SZXkjy'
and C.Status='SS') AS SZXkjy,
(SELECT isnull(sum(weight),0)
FROM TBL_Booking C
WHERE T.Fno=C.Fno
AND T.Fdep = C.Fdep
And T.Fdest=C.Fdest
AND T.Fdate = C.Fdate
And C.Agentcode='SZXynl'
and C.Status='SS') AS SZXynl,
(SELECT isnull(sum(weight),0)
FROM TBL_Booking C
WHERE T.Fno=C.Fno
AND T.Fdep = C.Fdep
And T.Fdest=C.Fdest
AND T.Fdate = C.Fdate
And C.Agentcode='TSN808'
and C.Status='SS') AS TSN808,
(SELECT isnull(sum(weight),0)
FROM TBL_Booking C
WHERE T.Fno=C.Fno
AND T.Fdep = C.Fdep
And T.Fdest=C.Fdest
AND T.Fdate = C.Fdate
And C.Agentcode='WUXJCB'
and C.Status='SS') AS WUXJCB,
(SELECT isnull(sum(weight),0)
FROM TBL_Booking C
WHERE T.Fno=C.Fno
AND T.Fdep = C.Fdep
And T.Fdest=C.Fdest
AND T.Fdate = C.Fdate
And C.Agentcode='WUXZKH'
and C.Status='SS') AS WUXZKH
FROM T
改写SQL后，查询需3、4秒。
疑问：1、索引如此添加是否合适，有没有更优的添加索引的方式?
2、SQL语句的改写是否就是最优的，能否只查询一次TBL_BOOKING表就汇总出CANYYB、CKGSZX、SHEHYB、SZXdgb、SZXfbb、SZXJCB、SZXkjy、SZXynl、TSN808、WUXJCB、WUXZKH这11个列的值。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/24389441/viewspace-1057884/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- 物理standby取消应用日志，出现ORA-16136
- Oracle开发：变量不可VN1<VN2<VN3?
- ORA-20000: Content of the tablespace specified is not permanent or tablespace name is invalid
- Tablespace Point-in-time Recovery (TSPITR) using RMAN
- SESSION连接数
- Red Hat Linux：Linux下访问windows XP的共享文件夹
- MS SQL Server:从该备份集进行读取时，RESTORE检测到在数据库.......
- Opening the Database with the RESETLOGS Option
- Oracle备份与恢复：Types of Oracle Recovery
- Oracle工具DBVERIFY: Offline Database Verification Utility
