# 公交车路线查询系统后台数据库设计--换乘算法改进与优化

1. “压缩”RouteT0

(1)RouteT0是原有换乘算法频繁使用的视图，因此，RouteT0的数据量直接影响到查询的效率，压缩RouteT0可以减少RouteT0的数据量，加速查询效率。

(2)压缩RouteT0后，将中转站点相同的路线合并为1组，加速了对结果集排序的速度。

2.视图GRouteT0

GRouteT0定义如下：

create view GRouteT0
as
select
StartStopKey,
EndStopKey,
min(StopCount) as MinStopCount,
max(StopCount) as MaxStopCount
from RouteT0
group by StartStopKey,EndStopKey 

3.二次查询算法

GInquiryT2
/*

exec InquiryT2 '站点1/站点2','站点3/站点4'
*/
CREATE     proc GInquiryT2(
@StartStops varchar(2048),
@EndStops varchar(2048)
)
as
begin
declare @ss_tab table(StopKey int)
declare @es_tab table(StopKey int)
insert @ss_tab
select distinct Stop.StopKey
from dbo.SplitString(@StartStops,'/') sn,Stop
where sn.Value=Stop.StopName
insert @es_tab
select distinct Stop.StopKey
from dbo.SplitString(@EndStops,'/') sn,Stop
where sn.Value=Stop.StopName
if(exists(select top 1 * from @ss_tab sst,@es_tab est where sst.StopKey=est.StopKey))
begin
raiserror ('起点集和终点集中含有相同的站点',16,1)
return
end
declare @stops table(StopKey int)
insert @stops select StopKey from @ss_tab
insert @stops select StopKey from @es_tab

print '===================================================='
print '筛选出第1段乘车路线'
print '----------------------------------------------------'
set statistics time on

------------------------------------------------------------
--筛选出第1段乘车路线，保存到临时表#R1中
select *
into #R1
from GRouteT0
where
StartStopKey in (select StopKey from @ss_tab)
and EndStopKey not in (Select StopKey from @stops)
order by StartStopKey,EndStopKey
--在临时表#R1上创建索引
create index index1 on #R1(StartStopKey,EndStopKey)
------------------------------------------------------------

set statistics time off
print '===================================================='
print '筛选出第3段乘车路线'
print '----------------------------------------------------'
set statistics time on

------------------------------------------------------------
--筛选出第3段乘车路线，保存到临时表#R3中
select *
into #R3
from GRouteT0
where
EndStopKey in (select StopKey from @es_tab)
and StartStopKey not in (Select StopKey from @stops)
order by StartStopKey,EndStopKey
--在临时表上创建索引
create index index1 on #R3(StartStopKey,EndStopKey)
------------------------------------------------------------

set statistics time off
print '===================================================='
print '筛选出第2段乘车路线'
print '----------------------------------------------------'
set statistics time on

------------------------------------------------------------
--筛选出第2段乘车路线，保存到临时表#R2中
select *
into #R2
from GRouteT0
where
StartStopKey in (select EndStopKey from #R1)
and EndStopKey in (Select StartStopKey from #R3)
--在临时表上创建索引
create clustered index index1 on #R2(StartStopKey,EndStopKey)
create index index2 on #R2(EndStopKey,StartStopKey)
------------------------------------------------------------

set statistics time off
print '===================================================='
print '二次换乘查询'
print '----------------------------------------------------'
set statistics time on

------------------------------------------------------------
--二次换乘查询
select
ss.StopName as 起点,
dbo.JoinRoute(res.StartStopKey,res.TransStopKey1) as 路线1,
ts1.StopName as 中转站1,
dbo.JoinRoute(res.TransStopKey1,res.TransStopKey2) as 路线2,
ts2.StopName as 中转站2,
dbo.JoinRoute(res.TransStopKey2,res.EndStopKey) as 路线3,
es.StopName as 终点,
MinStopCount
from(
--查询出站点数最少的10组路线
select top 10
r1.StartStopKey as StartStopKey,
r2.StartStopKey as TransStopKey1,
r2.EndStopKey as TransStopKey2,
r3.EndStopKey as EndStopKey,
(r1.MinStopCount+r2.MinStopCount+r3.MinStopCount) as MinStopCount
from #R1 r1,#R2 r2,#R3 r3
where r1.EndStopKey=r2.StartStopKey and r2.EndStopKey=r3.StartStopKey
order by (r1.MinStopCount+r2.MinStopCount+r3.MinStopCount) asc
)res,
Stop ss,
Stop es,
Stop ts1,
Stop ts2
where
res.StartStopKey=ss.StopKey and
res.EndStopKey=es.StopKey and
res.TransStopKey1=ts1.StopKey and
res.TransStopKey2=ts2.StopKey
------------------------------------------------------------

set statistics time off
print '===================================================='
end 

4.测试

(1) 测试环境

测试数据：广州市350条公交车路线

操作系统：Window XP SP2

数据库：SQL Server 2000 SP4 个人版

CPU：AMD Athlon(tm) 64 X2 Dual 2.4GHz

内存：2G

(2）选择用于测试的的站点

select Stop.StopName as '站点',count(StartStopKey) '#R1的数据量'
from RouteT0 full join Stop on RouteT0.StartStopKey=Stop.StopKey
group by Stop.StopName
order by count(StartStopKey) desc

(3)效率测试

exec GInquiryT2 '东圃镇','车陂路口'

====================================================

----------------------------------------------------

SQL Server 执行时间:
CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。

（所影响的行数为 458 行）

SQL Server 执行时间:
CPU 时间 = 10 毫秒，耗费时间 = 10 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。

SQL Server 执行时间:
CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。

SQL Server 执行时间:
CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。
====================================================

----------------------------------------------------

SQL Server 执行时间:
CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。

（所影响的行数为 449 行）

SQL Server 执行时间:
CPU 时间 = 0 毫秒，耗费时间 = 9 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。

SQL Server 执行时间:
CPU 时间 = 1 毫秒，耗费时间 = 1 毫秒。

SQL Server 执行时间:
CPU 时间 = 15 毫秒，耗费时间 = 1 毫秒。
====================================================

----------------------------------------------------

SQL Server 执行时间:
CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。

（所影响的行数为 41644 行）

SQL Server 执行时间:
CPU 时间 = 825 毫秒，耗费时间 = 825 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。

SQL Server 执行时间:
CPU 时间 = 93 毫秒，耗费时间 = 98 毫秒。

SQL Server 执行时间:
CPU 时间 = 93 毫秒，耗费时间 = 98 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，耗费时间 = 1 毫秒。

SQL Server 执行时间:
CPU 时间 = 73 毫秒，耗费时间 = 73 毫秒。

SQL Server 执行时间:
CPU 时间 = 79 毫秒，耗费时间 = 73 毫秒。
====================================================

----------------------------------------------------

SQL Server 执行时间:
CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。

（所影响的行数为 10 行）

SQL Server 执行时间:
CPU 时间 = 140 毫秒，耗费时间 = 141 毫秒。

5.效率优化

/*

exec GInquiryT2_1 '站点1/站点2','站点3/站点4'
*/
CREATE       proc GInquiryT2_1(
@StartStops varchar(2048),
@EndStops varchar(2048)
)
as
begin
declare @ss_tab table(StopKey int)
declare @es_tab table(StopKey int)
insert @ss_tab
select distinct Stop.StopKey
from dbo.SplitString(@StartStops,'/') sn,Stop
where sn.Value=Stop.StopName
insert @es_tab
select distinct Stop.StopKey
from dbo.SplitString(@EndStops,'/') sn,Stop
where sn.Value=Stop.StopName
if(exists(select top 1 * from @ss_tab sst,@es_tab est where sst.StopKey=est.StopKey))
begin
raiserror ('起点集和终点集中含有相同的站点',16,1)
return
end
declare @stops table(StopKey int)
insert @stops select StopKey from @ss_tab
insert @stops select StopKey from @es_tab

print '===================================================='
print '筛选出第1段乘车路线'
print '----------------------------------------------------'
set statistics time on

------------------------------------------------------------
--筛选出第1段乘车路线，保存到临时表#R1中
select *
into #R1
from GRouteT0
where
StartStopKey in (select StopKey from @ss_tab)
and EndStopKey not in (Select StopKey from @stops)
order by StartStopKey,EndStopKey
--在临时表#R1上创建索引
create index index1 on #R1(StartStopKey,EndStopKey)
------------------------------------------------------------

set statistics time off
print '===================================================='
print '筛选出第3段乘车路线'
print '----------------------------------------------------'
set statistics time on

------------------------------------------------------------
--筛选出第3段乘车路线，保存到临时表#R3中
select *
into #R3
from GRouteT0
where
EndStopKey in (select StopKey from @es_tab)
and StartStopKey not in (Select StopKey from @stops)
order by StartStopKey,EndStopKey
--在临时表上创建索引
create index index1 on #R3(StartStopKey,EndStopKey)
------------------------------------------------------------

set statistics time off
print '===================================================='
print '二次换乘查询'
print '----------------------------------------------------'
set statistics time on

------------------------------------------------------------
--二次换乘查询
select
ss.StopName as 起点,
dbo.JoinRoute(res.StartStopKey,res.TransStopKey1) as 路线1,
ts1.StopName as 中转站1,
dbo.JoinRoute(res.TransStopKey1,res.TransStopKey2) as 路线2,
ts2.StopName as 中转站2,
dbo.JoinRoute(res.TransStopKey2,res.EndStopKey) as 路线3,
es.StopName as 终点,
MinStopCount
from(
--查询出站点数最少的10组路线
select top 10
r1.StartStopKey as StartStopKey,
r2.StartStopKey as TransStopKey1,
r2.EndStopKey as TransStopKey2,
r3.EndStopKey as EndStopKey,
(r1.MinStopCount+r2.MinStopCount+r3.MinStopCount) as MinStopCount
from #R1 r1,GRouteT0 r2,#R3 r3
where r1.EndStopKey=r2.StartStopKey and r2.EndStopKey=r3.StartStopKey
order by (r1.MinStopCount+r2.MinStopCount+r3.MinStopCount) asc
)res,
Stop ss,
Stop es,
Stop ts1,
Stop ts2
where
res.StartStopKey=ss.StopKey and
res.EndStopKey=es.StopKey and
res.TransStopKey1=ts1.StopKey and
res.TransStopKey2=ts2.StopKey
------------------------------------------------------------

set statistics time off
print '===================================================='
end

exec GInquiryT2_1 '东圃镇','车陂路口'

====================================================

----------------------------------------------------

SQL Server 执行时间:
CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。

（所影响的行数为 458 行）

SQL Server 执行时间:
CPU 时间 = 0 毫秒，耗费时间 = 3 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。

SQL Server 执行时间:
CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。

SQL Server 执行时间:
CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。
====================================================

----------------------------------------------------

SQL Server 执行时间:
CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。

（所影响的行数为 449 行）

SQL Server 执行时间:
CPU 时间 = 6 毫秒，耗费时间 = 6 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。

SQL Server 执行时间:
CPU 时间 = 0 毫秒，耗费时间 = 1 毫秒。

SQL Server 执行时间:
CPU 时间 = 0 毫秒，耗费时间 = 1 毫秒。
====================================================

----------------------------------------------------

SQL Server 执行时间:
CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。

（所影响的行数为 10 行）

SQL Server 执行时间:
CPU 时间 = 250 毫秒，耗费时间 = 253 毫秒。
====================================================

6.展开路线组

GInquiryT2查询给出的结果是10组最短路线，那么，怎样才能得到最短的10条路线，显然，只需将这10组路线展开即可（展开后的路线数>=10），而最短的10条路线必然在展开的结果中。查询10条最短路线的存储过程GInquiryT2_Expand如下：

CREATE   proc GInquiryT2_Expand(
@StartStops varchar(2048),
@EndStops varchar(2048)
)
as
begin
declare @ss_tab table(StopKey int)
declare @es_tab table(StopKey int)
insert @ss_tab
select distinct Stop.StopKey
from dbo.SplitString(@StartStops,'/') sn,Stop
where sn.Value=Stop.StopName
insert @es_tab
select distinct Stop.StopKey
from dbo.SplitString(@EndStops,'/') sn,Stop
where sn.Value=Stop.StopName
if(exists(select top 1 * from @ss_tab sst,@es_tab est where sst.StopKey=est.StopKey))
begin
raiserror ('起点集和终点集中含有相同的站点',16,1)
return
end
declare @stops table(StopKey int)
insert @stops select StopKey from @ss_tab
insert @stops select StopKey from @es_tab

print '===================================================='
print '筛选出第1段乘车路线'
print '----------------------------------------------------'
set statistics time on

------------------------------------------------------------
--筛选出第1段乘车路线，保存到临时表#R1中
select *
into #R1
from GRouteT0
where
StartStopKey in (select StopKey from @ss_tab)
and EndStopKey not in (Select StopKey from @stops)
order by StartStopKey,EndStopKey
--在临时表#R1上创建索引
create index index1 on #R1(StartStopKey,EndStopKey)
------------------------------------------------------------

set statistics time off
print '===================================================='
print '筛选出第3段乘车路线'
print '----------------------------------------------------'
set statistics time on

------------------------------------------------------------
--筛选出第3段乘车路线，保存到临时表#R3中
select *
into #R3
from GRouteT0
where
EndStopKey in (select StopKey from @es_tab)
and StartStopKey not in (Select StopKey from @stops)
order by StartStopKey,EndStopKey
--在临时表上创建索引
create index index1 on #R3(StartStopKey,EndStopKey)
------------------------------------------------------------

set statistics time off
print '===================================================='
print '二次换乘查询'
print '----------------------------------------------------'
set statistics time on

------------------------------------------------------------
--二次换乘查询
select
ss.StopName as 起点,
r1.RouteName as '路线 1',
ts1.StopName as 中转站1,
r2.RouteName as '路线 2',
ts2.StopName as 中转站2,
r3.RouteName as '路线 3',
es.StopName as 终点,
res.StopCount as 总站点数
from(
--展开最优 10 组路线 得到最短 10 条路线
select top 10
res.StartStopKey as StartStopKey,
rt1.RouteKey as RouteKey_SS_TS1,
res.TransStopKey1 as TransStopKey1,
rt2.RouteKey as RouteKey_TS1_TS2,
res.TransStopKey2 as TransStopKey2,
rt3.RouteKey as RouteKey_TS2_ES,
res.EndStopKey as EndStopkey,
(rt1.StopCount+rt2.StopCount+rt3.StopCount) as StopCount
from(
--查询出站点数最少的 10 组路线
select top 10
r1.StartStopKey as StartStopKey,
r2.StartStopKey as TransStopKey1,
r2.EndStopKey as TransStopKey2,
r3.EndStopKey as EndStopKey,
(r1.MinStopCount+r2.MinStopCount+r3.MinStopCount) as MinStopCount
from #R1 r1,GRouteT0 r2,#R3 r3
where r1.EndStopKey=r2.StartStopKey and r2.EndStopKey=r3.StartStopKey
order by (r1.MinStopCount+r2.MinStopCount+r3.MinStopCount) asc
)res,
RouteT0 rt1,
RouteT0 rt2,
RouteT0 rt3
where
rt1.StartStopKey=res.StartStopKey and rt1.EndStopKey=res.TransStopKey1 and
rt2.StartStopKey=res.TransStopKey1 and rt2.EndStopKey=res.TransStopKey2 and
rt3.StartStopKey=res.TransStopKey2 and rt3.EndStopKey=res.EndStopKey
order by (rt1.StopCount+rt2.StopCount+rt3.StopCount) asc
)res
left join Stop ss on res.StartStopKey=ss.StopKey
left join Stop es on res.EndStopKey=es.StopKey
left join Stop ts1 on res.TransStopKey1=ts1.StopKey
left join Stop ts2 on res.TransStopKey2=ts2.StopKey
left join Route r1 on res.RouteKey_SS_TS1=r1.RouteKey
left join Route r2 on res.RouteKey_TS1_TS2=r2.RouteKey
left join Route r3 on res.RouteKey_TS2_ES=r3.RouteKey
------------------------------------------------------------

set statistics time off
print '===================================================='
end

exec GInquiryT2_Expand '东圃镇','车陂路口'

====================================================

----------------------------------------------------

SQL Server 执行时间:
CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。

（所影响的行数为 458 行）

SQL Server 执行时间:
CPU 时间 = 0 毫秒，耗费时间 = 3 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。

SQL Server 执行时间:
CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。

SQL Server 执行时间:
CPU 时间 = 0 毫秒，耗费时间 = 1 毫秒。
====================================================

----------------------------------------------------

SQL Server 执行时间:
CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。

（所影响的行数为 449 行）

SQL Server 执行时间:
CPU 时间 = 6 毫秒，耗费时间 = 6 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。

SQL Server 执行时间:
CPU 时间 = 0 毫秒，耗费时间 = 1 毫秒。

SQL Server 执行时间:
CPU 时间 = 0 毫秒，耗费时间 = 1 毫秒。
====================================================

----------------------------------------------------

SQL Server 执行时间:
CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。

（所影响的行数为 10 行）

SQL Server 执行时间:
CPU 时间 = 282 毫秒，耗费时间 = 301 毫秒。
====================================================

7.总结

(1)使用临时表；

(2)将频繁使用的视图改为表；

(3)从实际出发，合并RouteT0中类似的行，从而“压缩”RouteT0的数据量，减少查询生成的结果，提高查询和排序效率。

E-mail：mrlucc@126.com

posted @ 2009-03-03 01:03  卢春城  阅读(13158)  评论(19编辑  收藏  举报