求优化公交线路查询效率

[blunuser]

-- 模拟数据
SET NOCOUNT ON
IF OBJECT_ID(N'tempdb..#tb') IS NOT NULL
DROP TABLE #tb

CREATE TABLE #tb(
id int IDENTITY
PRIMARY KEY,
lineID int,
state nvarchar(10),
orderid int
)
INSERT #tb(
lineID, state, orderid)
SELECT 1, N'广州东', 1 UNION ALL
SELECT 1, N'体育中心', 2 UNION ALL
SELECT 1, N'体育西', 3 UNION ALL
SELECT 1, N'烈士陵园', 4 UNION ALL
SELECT 1, N'公园前', 5 UNION ALL
SELECT 1, N'西门口', 6 UNION ALL
SELECT 2, N'火车站', 1 UNION ALL
SELECT 2, N'纪念堂', 2 UNION ALL
SELECT 2, N'公园前', 3 UNION ALL
SELECT 2, N'中大', 4 UNION ALL
SELECT 2, N'客村', 5 UNION ALL
SELECT 2, N'琶洲', 6 UNION ALL
SELECT 2, N'万胜围', 7 UNION ALL
SELECT 3, N'广州东', 1 UNION ALL
SELECT 3, N'体育西', 2 UNION ALL
SELECT 3, N'珠江新城', 3 UNION ALL
SELECT 3, N'客村', 4 UNION ALL
SELECT 3, N'市桥', 5 UNION ALL
SELECT 4, N'万胜围', 1 UNION ALL
SELECT 4, N'金洲', 2

CREATE INDEX IX_lineID
ON #tb(
lineID)

CREATE INDEX IX_state
ON #tb(
state)

CREATE INDEX IX_orderid
ON #tb(
orderid)
GO

-- 乘车路线查询
DECLARE
@state_start nvarchar(10),
@state_stop nvarchar(10)
SELECT
@state_start = N'广州东',
@state_stop = N'中大'

-- 查询
IF OBJECT_ID(N'tempdb..#re') IS NOT NULL
DROP TABLE #re
CREATE TABLE #re(
ID int IDENTITY
PRIMARY KEY,
path nvarchar(2500),
state_count int,
line_count int,
start_lineID int,
start_state nvarchar(10),
current_lineID int,
current_state nvarchar(10),
next_orderid int,
flag int,
lineIDs varchar(1000),
level int
)

CREATE INDEX IX_current_lineID
ON #re(
current_lineID )

CREATE INDEX IX_current_state
ON #re(
current_state )

CREATE INDEX IX_next_orderid
ON #re(
next_orderid )

CREATE INDEX IX_current_level
ON #re(
level )

DECLARE
@level int,
@rows int
SET
@level = 0

-- 开始
INSERT #re(
path,
state_count, line_count,
start_lineID, start_state,
current_lineID, current_state,
next_orderid, flag, lineIDs, level)
SELECT
path = CONVERT(nvarchar(4000),
RTRIM(A.lineID) + N'{'
+ RTRIM(A.orderid) + N'.' + A.state
),
state_count = 0,
line_count = 0,
start_lineID = A.lineID,
start_state = A.state,
current_lineID = A.lineID,
current_state = A.state,
next_orderid = A.orderid,
flag = CASE
WHEN A.state = @state_stop THEN 0
ELSE NULL END,
lineIDs = ',' + RTRIM(A.lineID) + ',',
level = -(@level + 1)
FROM #tb A
WHERE state = @state_start
SET @rows = @@ROWCOUNT
WHILE @rows > 0
BEGIN
SELECT
@level = @level + 1
INSERT #re(
path,
state_count, line_count,
start_lineID, start_state,
current_lineID, current_state,
next_orderid, flag, lineIDs, level)
-- 同一LineID
SELECT
path = CONVERT(nvarchar(4000),
A.path
+ N'->'
+ RTRIM(B.orderid) + N'.' + B.state
),
state_count = A.state_count + 1,
A.line_count,
A.start_lineID, A.start_state,
current_lineID = B.lineID,
current_state = B.state,
next_orderid = B.orderid + A.flag,
flag = CASE
WHEN B.state = @state_stop THEN 0
ELSE A.flag END,
A.lineIDs,
level = @level
FROM #re A, #tb B
WHERE A.flag <> 0
AND A.level = @level - 1
AND A.current_lineID = B.lineID
AND A.next_orderid = B.orderid

UNION ALL
-- 不同LineID
SELECT
path = CONVERT(nvarchar(4000),
A.path + N')->'
+ RTRIM(B.lineID) + N'{'
+ RTRIM(B.orderid) + N'.' + B.state
),
state_count = A.state_count + 1,
line_count = A.line_count + 1,
A.start_lineID, A.start_state,
current_lineID = B.lineID,
current_state = B.state,
next_orderid = B.orderid,
flag = CASE
WHEN B.state = @state_stop THEN 0
ELSE NULL END,
A.lineIDs + RTRIM(B.lineID) + ',',
level = - @level
FROM #re A, #tb B
WHERE A.flag <> 0
AND state_count = @level - 1
AND A.current_lineID <> B.lineID
AND A.current_state = B.state
AND NOT EXISTS(
SELECT * FROM #re
WHERE CHARINDEX(',' + RTRIM(B.lineID) + ',', A.lineIDs) > 0)
SET @rows = @@ROWCOUNT

INSERT #re(
path,
state_count, line_count,
start_lineID, start_state,
current_lineID, current_state,
next_orderid, flag, lineIDs, level)
-- 不同LineID 的第站正向
SELECT
path = CONVERT(nvarchar(4000),
A.path
+ N'->'
+ RTRIM(B.orderid) + N'.' + B.state
),
state_count = A.state_count + 1,
A.line_count,
A.start_lineID, A.start_state,
current_lineID = B.lineID,
current_state = B.state,
next_orderid = B.orderid + 1,
flag = CASE
WHEN B.state = @state_stop THEN 0
ELSE 1 END,
A.lineIDs,
level = @level
FROM #re A, #tb B
WHERE A.flag IS NULL
AND A.level = - @level
AND A.current_lineID = B.lineID
AND A.next_orderid + 1 = B.orderid
UNION ALL
-- 不同LineID 的第站反向
SELECT
path = CONVERT(nvarchar(4000),
A.path
+ N'->'
+ RTRIM(B.orderid) + N'.' + B.state
),
state_count = A.state_count + 1,
A.line_count,
A.start_lineID, A.start_state,
current_lineID = B.lineID,
current_state = B.state,
next_orderid = B.orderid - 1,
flag = CASE
WHEN B.state = @state_stop THEN 0
ELSE - 1 END,
A.lineIDs,
level = @level
FROM #re A, #tb B
WHERE A.flag IS NULL
AND A.level = - @level
AND A.current_lineID = B.lineID
AND A.next_orderid - 1 = B.orderid

SET @rows = @rows + @@ROWCOUNT
END

SELECT
-- *,
path = path + N'}',
line_count,
state_count
FROM #re
WHERE flag = 0
以上语句可以执行 但是数据少的时候可以执行,一旦数据多了就要耗费 很长时间,效率很低. 现向各位求助, 要求提高运行效率,或者有更好的语句 效率更好 请发上来 一旦确认 马上结帐

[blunuser]

哎!杂都没人帮帮呢

[-狙击手-]

又是这个问题

[dawugui]

--最短乘车路线查询示例(邹老大的。)
CREATE TABLE T_Line(
ID      nvarchar(10),  --公交线路号
Station nvarchar(10),  --站点名称
Orders  int)           --行车方向(通过它反应每个站的上一个、下一个站)
INSERT T_Line 
SELECT N'8路'  ,N'站A',1 UNION ALL
SELECT N'8路'  ,N'站B',2 UNION ALL
SELECT N'8路'  ,N'站C',3 UNION ALL
SELECT N'8路'  ,N'站D',4 UNION ALL
SELECT N'8路'  ,N'站J',5 UNION ALL
SELECT N'8路'  ,N'站L',6 UNION ALL
SELECT N'8路'  ,N'站M',7 UNION ALL
SELECT N'20路' ,N'站G',1 UNION ALL
SELECT N'20路' ,N'站H',2 UNION ALL
SELECT N'20路' ,N'站I',3 UNION ALL
SELECT N'20路' ,N'站J',4 UNION ALL
SELECT N'20路' ,N'站L',5 UNION ALL
SELECT N'20路' ,N'站M',6 UNION ALL
SELECT N'255路',N'站N',1 UNION ALL
SELECT N'255路',N'站O',2 UNION ALL
SELECT N'255路',N'站P',3 UNION ALL
SELECT N'255路',N'站Q',4 UNION ALL
SELECT N'255路',N'站J',5 UNION ALL
SELECT N'255路',N'站D',6 UNION ALL
SELECT N'255路',N'站E',7 UNION ALL
SELECT N'255路',N'站F',8
GO

--乘车线路查询存储过程
CREATE PROC p_qry
@Station_Start nvarchar(10),
@Station_Stop  nvarchar(10)
AS
SET NOCOUNT ON
DECLARE @l int
SET @l=0
SELECT ID,Station,
	Line=CAST('('+RTRIM(ID)+': '+RTRIM(Station) as nvarchar(4000)),
	Orders=Orders,
	[Level]=@l
INTO # FROM T_Line
WHERE Station=@Station_Start
WHILE @@ROWCOUNT>0 
	AND NOT EXISTS(SELECT * FROM # WHERE Station=@Station_Stop)
BEGIN
	SET @l=@l+1
	INSERT #(Line,ID,Station,Orders,[Level])
	SELECT 
		Line=a.Line+CASE
			WHEN a.ID=b.ID THEN N'->'+RTRIM(b.Station)
			ELSE N') ∝ ('+RTRIM(b.ID)
				+N': '+RTRIM(b.Station) END,
		b.ID,b.Station,b.Orders,@l
	FROM # a,T_Line b
	WHERE a.[Level]=@l-1
		AND(a.Station=b.Station AND a.ID<>b.ID
			OR a.ID=b.ID AND(
				a.Orders=b.Orders+1
				OR
				a.Orders=b.Orders-1))
		AND LEN(a.Line)<4000
		AND PATINDEX('%[ >]'+b.Station+'[-)]%',a.Line)=0
END
SELECT N'起点站'=@Station_Start
	,N'终点站'=@Station_Stop
	,N'乘车线路'=Line+N')' 
FROM # 
WHERE [Level]=@l 
	AND Station=@Station_Stop
IF @@ROWCOUNT =0 --如果未有可以到达的线路,则显示处理结果表备查
	SELECT * FROM #
GO

--调用
EXEC p_qry N'站A',N'站L'
/*--结果
起点站  终点站  乘车线路
---------- ------------ -----------------------------------------------------------
站A    站L    (8路: 站A->站B->站C->站D->站J->站L)
--*/

[blunuser]

dawugui
潇洒老乌龟(原名:爱新觉罗.毓华)
等 级：
你发的 我以前就试过了 效率更底