//[作者：龙龙，发表时间：2006-11-15，未经同意不得转载]

//本算法实现公交线路一次换乘，多次换乘算法这里不作讨论

//数据库名bus，建立数据表bus

//由于此换乘算法比较简单，只需一张表bus就可以了

//表bus结构如：

/**

* **********************************************************************************************

* id(自动编号 pk)     busline(公交线路 int 4)   busname(站点名称 varchar 20)   busorder(站点顺序 int 4)

* 1                    1                         火车站                           1

* 2                    1                         胜利广场                         2

* 3                    1                         卖渔桥                           3

* ...                  ...                       ...                             ...

* 25                   2                         胜利广场                         1

* 26                   2                         天香电器城                       2

* 27                   2                         五里墩                           3

* ...                  ...                       ...                             ...

* 数据库就是这样插记录的，可以把城市的公交线路数据全部插进去

************************************************************************************************

*/

//定义一个新类

//实现公交换乘

class buss{

//定义数据库连接成员变量

var $host;

var $user;

var $passwd;

var $database;

var $conn;

//利用构造函数实现变量初始化，连接数据库

function buss(){

$this->host="localhost";

$this->user="root";

$this->passwd="";

$this->database="bus";

$this->conn=mysql_connect($this->host, $this->user,$this->passwd) or

die("Could not connect to $this->host");

mysql_select_db($this->database,$this->conn) or

die("Could not switch to database $this->database");

}

//统计数据库中所有公交站点名，存入数组

//返回站点名

function busstotal(){

$SQL = "select * from bus group by busname";

$count = 0;

$result = mysql_query($SQL);

while($row = mysql_fetch_object($result)){

$bustotal[$count]= $row->busname;

$count++;

}

return $bustotal;

}

//统计数据库中所有公交路线，存入数组

//返回公交线路

function busslinetotal(){

$SQL = "select * from bus group by busline";

$count = 0;

$result = mysql_query($SQL);

while($row = mysql_fetch_object($result)){

$buslinetotal[$count]= $row->busline;

$count++;

}

return $buslinetotal;

}

//统计数据库中每一线路经过的站点，存入数组

//需要参数line，区别每一路车

//返回站点名

function bussperline($line){

$SQL = "select * from bus where busline = '$line'";

$count = 0;

$result = mysql_query($SQL);

while($row = mysql_fetch_object($result)){

$busperline[$count]= $row->busname;

$count++;

}

return $busperline;

}

//统计经过某站点的所有公交车的组合

//需要参数station，表示经过的站点

//返回公交线路

function passline($station){

$SQL = "select * from bus where busname = '$station' group by busline";

$count = 0;

$result = mysql_query($SQL);

while($row = mysql_fetch_object($result)){

$passline[$count]= $row->busline;

$count++;

}

return $passline;

}

//实现换乘算法的函数

//需要提供参数，查询的起点和终点

function bussStationToStation($start,$end){

$flag1 = false;

$flag2 = false;

//函数回调

$busstotal = $this->busstotal();

$busslinetotal = $this->busslinetotal();

//判断数据库中是否有此站点

for($i=0;$i

if($start==$busstotal[$i]) $flag1 = true;

if($end==$busstotal[$i]) $flag2 = true;

if($flag1 and $flag2) break;

}

//有一个站点不存在

if(!($flag1 and $flag2)){

if(!$flag1) die("$start站点不存在！");

if(!$flag2) die("$end站点不存在！");

}

//两个站点都存在的情况

//首先判断有无直达车

$strTemp = "";

//遍历所有车次

for($i=0;$i

$flag3 = 0;

//函数回调

$bussperline = $this->bussperline($busslinetotal[$i]);

//遍历每一车次经过的站点

for($j=0;$j

if($start==$bussperline[$j]) $flag3 +=1;

if($end==$bussperline[$j]) $flag3 +=1;

if($flag3==2) break;

}

if($flag3==2)

//保存直达车次，以||分割

$strTemp = $strTemp.$busslinetotal[$i]."||";

}

if($strTemp==""){

//没有直达车，则计算一次换乘情况

echo("".$start. " 到 "

.$end." 没有直达车!请参

照下列换乘建议.

");

//查询一级中转站

//start起点

//end终点

//函数回调，取得经过起点和终点的所有组合

$statpass = $this->passline($start);

$endpass = $this->passline($end);

//得到经过起点和终点的线路的全部组合

$resultbus = "";

for($a=0;$a

for($b=0;$b

//判断两条线路有没有交叉点

$startper = $this->bussperline($statpass[$a]);

$endper = $this->bussperline($endpass[$b]);

for($c=0;$c

for($d=0;$d

if($startper[$c]==$endper[$d]){

//成功找到交叉点后

//存储交叉点处信息

//此只为一次换乘

$fistid = $statpass[$a];

$secondid = $endpass[$b];

$changestation = $startper[$c];

$resultbus .= $fistid.";".$secondid.";".$changestation."||";

}

}

}

}

}

if($resultbus=="")

{

//没有找到换乘线路

echo("

抱歉，" .$start. " 到 "

.$end. "没有直达车,换乘一次也无法到达!");

}

else{

//找到换乘线路

$resultbus = substr($resultbus,0,strlen($resultbus)-2);//去掉最右边的"||"

$resultbus_ok1 = explode("||",$resultbus);//将字符串分割成数组

echo ("

echo ("

");

echo ("

起点");

echo ("

车次");

echo ("

中转站");

echo ("

车次");

echo ("

终点");

echo ("

");

for($mm=0;$mm

$resultbus_ok2 = explode(";",$resultbus_ok1[$mm]);

//计算两辆车的起点和终点

$bus1 = $this->bussperline($resultbus_ok2[0]);

$bus2 = $this->bussperline($resultbus_ok2[1]);

//显示

echo ("

");

echo ("

" .$bus1[0]. "");

echo ("

" .$resultbus_ok2[0]. "");

echo ("

" .$resultbus_ok2[2]. " ==> ");

echo ("

" .$resultbus_ok2[1]. "");

echo ("

" .$bus2[count($bus2)-1]. "");

echo("

");

}

echo("

");

}

}

else{

//有直达车，直接显示直达车情况

echo ("

echo ("

");

echo ("

车次");

echo ("

起点");

echo ("

经过");

echo ("

经过");

echo ("

终点");

echo ("

详情");

echo ("

");

$strTemp = substr($strTemp,0,strlen($strTemp)-2);//去掉最右边的"||"

$strTemp_ok1 = explode("||",$strTemp);//将字符串分割成数组

for($nn=0;$nn

//计算车辆的起点和终点

$bus = $this->bussperline($strTemp_ok1[$nn]);

//显示

echo ("

");

echo ("

" .$strTemp_ok1[$nn]. "");

echo ("

" .$bus[0]. "");

echo ("

" .$start. " ==> ");

echo ("

" .$end. "");

echo ("

" .$bus[count($bus)-1]. "");

echo ("

详情");

echo("

");

}

echo("

");

}

}

}

/*

定义好抽象类后，使用就非常简单了

*/

$bus = new buss;

$bus->bussStationToStation("火车站","五里墩");

//一切ok，直接就可以看到结果了

?>

公交车路线查询系统后台数据库设计系列文章

数据库下载(该数据库已经输入了广州市350条公交车路线作为测试数据)

在《查询算法》一文中已经实现了换乘算法，但是，使用存储过程InquiryT2查询从“东圃镇”到“车陂路口”的乘车路线时，发现居然用了5分钟才查找出结果，这样的效率显然不适合实际应用。因此，有必要对原有的换乘算法进行优化和改进。在本文中，将给出一种改进的换乘算法，相比原有的算法，改进后的算法功能更强，效率更优。

1.“压缩”RouteT0

假设RouteT0有以下几行

如下图所示，当查询S1到S4的二次换乘路线时，将会产生3×2×4=24个结果

从图中可以看出，第1段路线中的3条线路的起点和站点都相同(第2、3段路线也是如此)，事实上，换乘查询中关心的是两个站点之间有无线路可通，而不关心是乘坐什么路线，因此，可以将RouteT0压缩为：

如下图所示，压缩后，查询结果有原来的24条合并1组

查询结果为：

那么，为什么要对视图RouteT0进行压缩呢，原因如下：

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

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

2.视图GRouteT0

在数据库中，将使用GRouteT0来描述压缩的RouteT0，由于本文使用的数据库的关系图与《查询算法》中有所不同，在给出GRouteT0的代码前，先说明一下：

主要的改变是Stop_Route使用了整数型的RouteKey和StopKey引用Route和Stop，而不是用路线名和站点名。

GRouteT0定义如下： create viewGRouteT0

as

selectStartStopKey,

EndStopKey,

min(StopCount) asMinStopCount,

max(StopCount) asMaxStopCount

fromRouteT0

group byStartStopKey,EndStopKey

注意，视图GRouteT0不仅有StartStopKey和EndStopKey列，还有MinStopCount列，MinStopCount是指从StartStop到EndStop的最短线路的站点数。例如：上述RouteT0对应的GRouteT0为：

3.二次查询算法

以下是二次换乘查询的存储过程GInquiryT2的代码，该存储过程使用了临时表来提高查询效率：

GInquiryT2

/*

查询站点@StartStops到站点@EndStops之间的二次换乘乘车路线，多个站点用'/'分开，结果以分组方式给出，如：

exec InquiryT2 '站点1/站点2','站点3/站点4'

*/CREATE procGInquiryT2(

@StartStops varchar(2048),

@EndStops varchar(2048)

)

as

begin

declare@ss_tab table(StopKey int)

declare@es_tab table(StopKey int)

insert@ss_tab

select distinctStop.StopKey

fromdbo.SplitString(@StartStops,'/') sn,Stop

wheresn.Value=Stop.StopName

insert@es_tab

select distinctStop.StopKey

fromdbo.SplitString(@EndStops,'/') sn,Stop

wheresn.Value=Stop.StopName

if(exists(select top1 * from@ss_tab sst,@es_tab est wheresst.StopKey=est.StopKey))

begin

raiserror('起点集和终点集中含有相同的站点',16,1)

return

end

declare@stops table(StopKey int)

insert@stops selectStopKey from@ss_tab

insert@stops selectStopKey from@es_tab

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

--筛选出第1段乘车路线，保存到临时表#R1中select*

into#R1

fromGRouteT0

whereStartStopKey in(selectStopKey from@ss_tab)

andEndStopKey not in(SelectStopKey from@stops)

order byStartStopKey,EndStopKey

--在临时表#R1上创建索引create indexindex1 on#R1(StartStopKey,EndStopKey)

------------------------------------------------------------set statistics time off

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

--筛选出第3段乘车路线，保存到临时表#R3中select*

into#R3

fromGRouteT0

whereEndStopKey in(selectStopKey from@es_tab)

andStartStopKey not in(SelectStopKey from@stops)

order byStartStopKey,EndStopKey

--在临时表上创建索引create indexindex1 on#R3(StartStopKey,EndStopKey)

------------------------------------------------------------set statistics time off

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

--筛选出第2段乘车路线，保存到临时表#R2中select*

into#R2

fromGRouteT0

whereStartStopKey in(selectEndStopKey from#R1)

andEndStopKey in(SelectStartStopKey from#R3)

--在临时表上创建索引create clustered indexindex1 on#R2(StartStopKey,EndStopKey)

create indexindex2 on#R2(EndStopKey,StartStopKey)

------------------------------------------------------------set statistics time off

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

--二次换乘查询selectss.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 top10

r1.StartStopKey asStartStopKey,

r2.StartStopKey asTransStopKey1,

r2.EndStopKey asTransStopKey2,

r3.EndStopKey asEndStopKey,

(r1.MinStopCount+r2.MinStopCount+r3.MinStopCount) asMinStopCount

from#R1 r1,#R2 r2,#R3 r3

wherer1.EndStopKey=r2.StartStopKey andr2.EndStopKey=r3.StartStopKey

order by(r1.MinStopCount+r2.MinStopCount+r3.MinStopCount) asc)res,

Stop ss,

Stop es,

Stop ts1,

Stop ts2

whereres.StartStopKey=ss.StopKey andres.EndStopKey=es.StopKey andres.TransStopKey1=ts1.StopKey andres.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语句使用的三张表#R1,#R2,#R3，因此，这三张表的数据量直接影响到二次换乘查询使用的时间：

显然，R1的数据量由起点决定，查询起始站点对应的#R1的数据量的SQL语句如下：

selectStop.StopName as'站点',count(StartStopKey) '#R1的数据量'fromRouteT0 full joinStop onRouteT0.StartStopKey=Stop.StopKey

group byStop.StopName

order by count(StartStopKey) desc

运行结果如下：

显然，但起点为“东圃镇”时，#R1的数据量最大，同理可得终点和#R3数据量关系如下：

因此，在仅考虑数据量的情况下，查询“东圃镇”到“车陂路口”所用的时间是最长的。在下文中，将使用“东圃镇”作为起点，“车陂路口”为终点对二次查询算法进行效率测试

(3)效率测试

测试语句如下：

execGInquiryT2 '东圃镇','车陂路口'

测试结果：

查询结果如下：

输出如下：

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

筛选出第1段乘车路线

----------------------------------------------------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 毫秒。

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

筛选出第3段乘车路线

----------------------------------------------------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 毫秒。

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

筛选出第2段乘车路线

----------------------------------------------------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 毫秒。

从消息窗口的信息可以看出，查询大概用了1秒

5.效率优化

用GInquiryT2查询“东圃镇”到“车陂路口”仅用了1秒钟，那么，还能不能再优化呢？仔细分析输出的结果，可发现查询时最耗时的并不是换乘查询语句(140ms)，而是筛选出第2段查询路线的语句(825ms)，如图所示：

那么有没有方法可以提高筛选第2段路线的效率呢？答案是肯定的。只需把GRouteT0改成实表，并创建索引就行了。修改成实表后，就不需要把第2段路线缓存到临时表#R2中，修改后的GInquiryT2(重命名为GInquiryT2_1)如下：

/*

查询站点@StartStops到站点@EndStops之间的二次换乘乘车路线，多个站点用'/'分开，结果以分组方式给出，如：

exec GInquiryT2_1 '站点1/站点2','站点3/站点4'

*/CREATE procGInquiryT2_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 distinctStop.StopKey

fromdbo.SplitString(@StartStops,'/') sn,Stop

wheresn.Value=Stop.StopName

insert@es_tab

select distinctStop.StopKey

fromdbo.SplitString(@EndStops,'/') sn,Stop

wheresn.Value=Stop.StopName

if(exists(select top1 * from@ss_tab sst,@es_tab est wheresst.StopKey=est.StopKey))

begin

raiserror('起点集和终点集中含有相同的站点',16,1)

return

end

declare@stops table(StopKey int)

insert@stops selectStopKey from@ss_tab

insert@stops selectStopKey from@es_tab

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

--筛选出第1段乘车路线，保存到临时表#R1中select*

into#R1

fromGRouteT0

whereStartStopKey in(selectStopKey from@ss_tab)

andEndStopKey not in(SelectStopKey from@stops)

order byStartStopKey,EndStopKey

--在临时表#R1上创建索引create indexindex1 on#R1(StartStopKey,EndStopKey)

------------------------------------------------------------set statistics time off

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

--筛选出第3段乘车路线，保存到临时表#R3中select*

into#R3

fromGRouteT0

whereEndStopKey in(selectStopKey from@es_tab)

andStartStopKey not in(SelectStopKey from@stops)

order byStartStopKey,EndStopKey

--在临时表上创建索引create indexindex1 on#R3(StartStopKey,EndStopKey)

------------------------------------------------------------set statistics time off

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

--二次换乘查询selectss.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 top10

r1.StartStopKey asStartStopKey,

r2.StartStopKey asTransStopKey1,

r2.EndStopKey asTransStopKey2,

r3.EndStopKey asEndStopKey,

(r1.MinStopCount+r2.MinStopCount+r3.MinStopCount) asMinStopCount

from#R1 r1,GRouteT0 r2,#R3 r3

wherer1.EndStopKey=r2.StartStopKey andr2.EndStopKey=r3.StartStopKey

order by(r1.MinStopCount+r2.MinStopCount+r3.MinStopCount) asc)res,

Stop ss,

Stop es,

Stop ts1,

Stop ts2

whereres.StartStopKey=ss.StopKey andres.EndStopKey=es.StopKey andres.TransStopKey1=ts1.StopKey andres.TransStopKey2=ts2.StopKey

------------------------------------------------------------set statistics time off

print'===================================================='end

下面，仍然用查询“东圃镇”到“车陂路口”为例测试改成实表后GInquiryT2的效率，测试语句如下：

execGInquiryT2_1 '东圃镇','车陂路口'

消息窗口输出如下：

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

筛选出第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 毫秒。

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

筛选出第3段乘车路线

----------------------------------------------------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 毫秒。

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

从输出可以看出，大概用了250ms

6.展开路线组

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

CREATE procGInquiryT2_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 distinctStop.StopKey

fromdbo.SplitString(@StartStops,'/') sn,Stop

wheresn.Value=Stop.StopName

insert@es_tab

select distinctStop.StopKey

fromdbo.SplitString(@EndStops,'/') sn,Stop

wheresn.Value=Stop.StopName

if(exists(select top1 * from@ss_tab sst,@es_tab est wheresst.StopKey=est.StopKey))

begin

raiserror('起点集和终点集中含有相同的站点',16,1)

return

end

declare@stops table(StopKey int)

insert@stops selectStopKey from@ss_tab

insert@stops selectStopKey from@es_tab

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

--筛选出第1段乘车路线，保存到临时表#R1中select*

into#R1

fromGRouteT0

whereStartStopKey in(selectStopKey from@ss_tab)

andEndStopKey not in(SelectStopKey from@stops)

order byStartStopKey,EndStopKey

--在临时表#R1上创建索引create indexindex1 on#R1(StartStopKey,EndStopKey)

------------------------------------------------------------set statistics time off

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

--筛选出第3段乘车路线，保存到临时表#R3中select*

into#R3

fromGRouteT0

whereEndStopKey in(selectStopKey from@es_tab)

andStartStopKey not in(SelectStopKey from@stops)

order byStartStopKey,EndStopKey

--在临时表上创建索引create indexindex1 on#R3(StartStopKey,EndStopKey)

------------------------------------------------------------set statistics time off

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

--二次换乘查询selectss.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 top10

res.StartStopKey asStartStopKey,

rt1.RouteKey asRouteKey_SS_TS1,

res.TransStopKey1 asTransStopKey1,

rt2.RouteKey asRouteKey_TS1_TS2,

res.TransStopKey2 asTransStopKey2,

rt3.RouteKey asRouteKey_TS2_ES,

res.EndStopKey asEndStopkey,

(rt1.StopCount+rt2.StopCount+rt3.StopCount) asStopCount

from(

--查询出站点数最少的 10 组路线select top10

r1.StartStopKey asStartStopKey,

r2.StartStopKey asTransStopKey1,

r2.EndStopKey asTransStopKey2,

r3.EndStopKey asEndStopKey,

(r1.MinStopCount+r2.MinStopCount+r3.MinStopCount) asMinStopCount

from#R1 r1,GRouteT0 r2,#R3 r3

wherer1.EndStopKey=r2.StartStopKey andr2.EndStopKey=r3.StartStopKey

order by(r1.MinStopCount+r2.MinStopCount+r3.MinStopCount) asc)res,

RouteT0 rt1,

RouteT0 rt2,

RouteT0 rt3

wherert1.StartStopKey=res.StartStopKey andrt1.EndStopKey=res.TransStopKey1 andrt2.StartStopKey=res.TransStopKey1 andrt2.EndStopKey=res.TransStopKey2 andrt3.StartStopKey=res.TransStopKey2 andrt3.EndStopKey=res.EndStopKey

order by(rt1.StopCount+rt2.StopCount+rt3.StopCount) asc)res

left joinStop ss onres.StartStopKey=ss.StopKey

left joinStop es onres.EndStopKey=es.StopKey

left joinStop ts1 onres.TransStopKey1=ts1.StopKey

left joinStop ts2 onres.TransStopKey2=ts2.StopKey

left joinRoute r1 onres.RouteKey_SS_TS1=r1.RouteKey

left joinRoute r2 onres.RouteKey_TS1_TS2=r2.RouteKey

left joinRoute r3 onres.RouteKey_TS2_ES=r3.RouteKey

------------------------------------------------------------set statistics time off

print'===================================================='end

下面，仍然以查询“东圃镇”到“车陂路口”为例测试GInquiryT2_Expand，代码如下：

execGInquiryT2_Expand '东圃镇','车陂路口'

查询结果如下：

消息窗口输出如下：

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

筛选出第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 毫秒，耗费时间 = 1 毫秒。

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

筛选出第3段乘车路线

----------------------------------------------------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 毫秒。

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

由输出结果可看出，大约用了300ms

7.总结

下面，对本文使用的优化策略做一下总结：

(1)使用临时表；

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

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

作者：卢春城

E-mail：mrlucc@126.com

出处：http://lucc.cnblogs.com/

本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接。