在线等-如何提高以下SQL查询效率

soft_wsx 2009-07-15 12:26:06
我有EXCEL源文件,不知道怎么发,所以数据放到后面
--使用OPENROWSET读取EXCEL文件中的值
select * into tran_codes from openrowset('Microsoft.jet.oledb.4.0','EXCEL 8.0;HDR=yes;database=c:\tran.xls',sheet1$)
-- select * from tran_codes drop table tran_codes
go

--使用存储过程
alter proc tran_proc
@Start_Station nvarchar(100),
@End_Station nvarchar(100)
as
set nocount on
declare @l int
set @l=0

--从开始站查询可能的乘车线路-- select * from #aa
select 车次,车站,站次,
convert(nvarchar(4000),'('+rtrim(车次)+N':'+rtrim(车站)) as 线路,level=@l into #aa
from tran_codes where 车站=@Start_Station

--循环查询所有可能线路,直到找不到可用线路或者找到达到终止站点的乘车线路
while @@ROWCOUNT>0 and not exists(select 1 from #aa where 车站=@End_Station)
begin
set @l=@l+1
insert #aa(线路,车次,车站,站次,level)
select 线路=a.线路+
case
when a.车次=b.车次 then N'->'+RTRIM(ltrim(b.车站))
else N')可转乘('+RTRIM(ltrim(b.车次))+N':'+LTRIM(RTRIM(b.车站))
end,
b.车次,b.车站,b.站次,@l
from #aa a,tran_codes b
where a.level=@l-1
and(
(a.车站=b.车站 and a.车次<>b.车次) --相同的车站不同的车次
or (a.车次=b.车次 and (a.站次=b.站次+1 or a.站次=b.站次-1))
)
and len(a.线路)<4000
and PATINDEX('%'+b.车站+'[-)]%',a.线路)=0
end

--显示最后结果
select
N'起始站'=@Start_Station,
N'终点站'=@End_Station,
N'乘车线路'=线路+N')'
from #aa
where 车站=@End_Station
--如果未有可以一达的线路,则显示处理结果表备查
if @@ROWCOUNT=0
select * from #aa
go
--(K166次(空调快速):昆明->广通->元谋->攀枝花->西昌->普雄->汉源->峨眉->成都)可转乘(T8次:成都->绵阳->广元->略阳->宝鸡->西安->洛阳->石家庄->保定->北京西)
exec tran_proc N'昆明',N'北京西'

附表数据
/*
站次 车次 车站 日期 到达时间 停留时间 开车时间 运行时间 里程 硬座 硬卧下铺 软座 软卧
1 2640次(普快) 昆明 当天 -- -- 16:40 0 0 0 0 0
2 2640次(普快) 曲靖 当天 18:48 6分钟 18:54 2小时8分 157 13 67 21 102
3 2640次(普快) 红果 当天 20:49 20分钟 21:09 1小时55分 261 21 67 35 102
4 2640次(普快) 柏果 当天 22:00 3分钟 22:03 0小时51分 304 24 67 40 102
5 2640次(普快) 发耳 当天 23:27 5分钟 23:32 1小时24分 377 27 67 47 102
6 2640次(普快) 六盘水 第二天 0:55 17分钟 1:12 1小时23分 427 31 71 53 108
7 2640次(普快) 草海 第二天 2:28 2分钟 2:30 1小时16分 500 36 82 63 127
8 2640次(普快) 昭通 第二天 3:57 6分钟 4:03 1小时27分 585 41 93 72 144
9 2640次(普快) 大关 第二天 5:42 2分钟 5:44 1小时39分 671 46 103 81 162
10 2640次(普快) 水富 第二天 7:52 2分钟 7:54 2小时8分 808 53 117 93 184
11 2640次(普快) 宜宾 第二天 8:21 5分钟 8:26 0小时27分 834 55 122 98 193
12 2640次(普快) 自贡 第二天 9:56 8分钟 10:04 1小时30分 913 59 130 105 207
13 2640次(普快) 内江 第二天 10:50 13分钟 11:03 0小时46分 952 62 136 110 216
14 2640次(普快) 资中 第二天 11:34 19分钟 11:53 0小时31分 982 64 140 114 223
15 2640次(普快) 资阳 第二天 13:20 4分钟 13:24 1小时27分 1049 67 145 118 231
16 2640次(普快) 简阳 第二天 14:01 2分钟 14:03 0小时37分 1088 68 149 122 238
17 2640次(普快) 成都 第二天 15:27 -- -- 1小时24分 1171 73 159 131 254
1 K854次(快速) 昆明 当天 -- -- 9:35 0 0 0 0 0
2 K854次(快速) 曲靖 当天 11:32 8分钟 11:40 1小时57分 157 14 53 0 76
3 K854次(快速) 宣威 当天 13:49 19分钟 14:08 2小时9分 258 23 62 0 89
4 K854次(快速) 六盘水 当天 16:38 12分钟 16:50 2小时30分 389 32 71 0 106
5 K854次(快速) 草海 当天 18:03 3分钟 18:06 1小时13分 462 39 83 0 125
6 K854次(快速) 昭通 当天 19:28 6分钟 19:34 1小时22分 547 44 92 0 139
7 K854次(快速) 盐津 当天 22:11 10分钟 22:21 2小时37分 683 53 110 0 169
8 K854次(快速) 水富 第二天 0:28 4分钟 0:32 2小时7分 770 59 121 0 184
9 K854次(快速) 宜宾 第二天 1:07 5分钟 1:12 0小时35分 796 61 125 0 192
10 K854次(快速) 自贡 第二天 2:34 5分钟 2:39 1小时22分 875 66 135 0 209
11 K854次(快速) 内江 第二天 3:27 8分钟 3:35 0小时48分 914 68 139 0 216
12 K854次(快速) 资阳 第二天 5:09 5分钟 5:14 1小时34分 1011 74 150 0 232
13 K854次(快速) 简阳 第二天 5:51 2分钟 5:53 0小时37分 1050 78 156 0 242
14 K854次(快速) 成都 第二天 7:36 -- -- 1小时43分 1133 81 164 0 255
1 K166次(空调快速) 昆明 当天 -- -- 17:58 0 0 0 0 0
2 K166次(空调快速) 广通 当天 20:28 6分钟 20:34 2小时30分 153 24 78 0 112
3 K166次(空调快速) 元谋 当天 21:57 2分钟 21:59 1小时23分 249 41 95 0 136
4 K166次(空调快速) 攀枝花 当天 23:38 13分钟 23:51 1小时39分 351 52 106 0 156
5 K166次(空调快速) 西昌 第二天 2:55 18分钟 3:13 3小时4分 543 76 143 0 215
6 K166次(空调快速) 普雄 第二天 6:16 10分钟 6:26 3小时3分 704 99 183 0 276
7 K166次(空调快速) 汉源 第二天 8:14 4分钟 8:18 1小时48分 820 106 197 0 298
8 K166次(空调快速) 峨眉 第二天 10:22 7分钟 10:29 2小时4分 944 125 231 0 352
9 K166次(空调快速) 成都 第二天 12:53 22分钟 13:15 2小时24分 1100 140 257 0 390
10 K166次(空调快速) 德阳 第二天 14:04 6分钟 14:10 0小时49分 1161 150 274 0 417
11 K166次(空调快速) 绵阳 第二天 14:57 6分钟 15:03 0小时47分 1215 154 281 0 429
12 K166次(空调快速) 江油 第二天 15:46 8分钟 15:54 0小时43分 1257 158 290 0 442
13 K166次(空调快速) 广元 第二天 18:35 14分钟 18:49 2小时41分 1419 175 319 0 486
14 K166次(空调快速) 阳平关 第二天 20:08 2分钟 20:10 1小时19分 1498 179 327 0 499
15 K166次(空调快速) 略阳 第二天 21:13 18分钟 21:31 1小时3分 1554 191 345 0 529
16 K166次(空调快速) 宝鸡 第三天 3:03 19分钟 3:22 5小时32分 1769 203 369 0 568
17 K166次(空调快速) 咸阳 第三天 4:55 2分钟 4:57 1小时33分 1919 219 399 0 613
18 K166次(空调快速) 西安 第三天 5:16 -- -- 0小时19分 1942 219 399 0 613
1 K146次(空调快速) 昆明 当天 -- -- 19:50 0 0 0 0 0
2 K146次(空调快速) 广通 当天 22:20 8分钟 22:28 2小时30分 153 24 78 0 112
3 K146次(空调快速) 元谋 当天 23:51 7分钟 23:58 1小时23分 249 41 95 0 136
4 K146次(空调快速) 攀枝花 第二天 1:33 8分钟 1:41 1小时35分 351 52 106 0 156
5 K146次(空调快速) 西昌 第二天 4:50 13分钟 5:03 3小时9分 543 76 143 0 215
6 K146次(空调快速) 喜德 第二天 6:06 3分钟 6:09 1小时3分 617 87 162 0 245
7 K146次(空调快速) 普雄 第二天 7:33 6分钟 7:39 1小时24分 704 99 183 0 276
8 K146次(空调快速) 汉源 第二天 9:42 4分钟 9:46 2小时3分 820 106 197 0 298
9 K146次(空调快速) 峨眉 第二天 11:56 3分钟 11:59 2小时10分 944 125 231 0 352
10 K146次(空调快速) 成都 第二天 14:12 -- -- 2小时13分 1100 140 257 0 390
1 1364 空调普快 成都 当天 -- -- 22:00 0 0 0 0 0
2 1364 空调普快 德阳 当天 22:42 9分钟 22:51 0小时42分 61 12 62 17 92
3 1364 空调普快 绵阳 当天 23:33 7分钟 23:40 0小时42分 115 18 71 27 102
4 1364 空调普快 江油 第二天 0:16 5分钟 0:21 0小时36分 157 22 75 34 109
5 1364 空调普快 广元 第二天 2:51 12分钟 3:03 2小时30分 319 41 103 66 156
6 1364 空调普快 汉中 第二天 6:26 2分钟 6:28 3小时23分 515 64 128 105 196
7 1364 空调普快 西乡 第二天 7:43 7分钟 7:50 1小时15分 608 73 146 120 223
8 1364 空调普快 安康 第二天 10:19 12分钟 10:31 2小时29分 755 91 179 148 274
9 1364 空调普快 旬阳北 第二天 11:13 3分钟 11:16 0小时42分 801 94 185 154 286
10 1364 空调普快 镇安 第二天 12:26 9分钟 12:35 1小时10分 873 102 201 170 312
11 1364 空调普快 渭南 第二天 15:03 2分钟 15:05 2小时28分 1070 123 240 204 373
12 1364 空调普快 华山 第二天 15:57 2分钟 15:59 0小时52分 1137 126 246 209 383
13 1364 空调普快 灵宝 第二天 17:03 2分钟 17:05 1小时4分 1226 136 263 225 411
14 1364 空调普快 三门峡西 第二天 17:29 2分钟 17:31 0小时24分 1256 140 272 232 424
15 1364 空调普快 三门峡 第二天 17:52 2分钟 17:54 0小时21分 1279 140 272 232 424
16 1364 空调普快 义马 第二天 18:52 2分钟 18:54 0小时58分 1346 145 281 240 438
17 1364 空调普快 洛阳 第二天 19:38 2分钟 19:40 0小时44分 1401 154 298 255 465
18 1364 空调普快 巩义 第二天 20:18 2分钟 20:20 0小时38分 1455 158 306 262 478
19 1364 空调普快 郑州 第二天 21:12 12分钟 21:24 0小时52分 1525 161 312 268 490
20 1364 空调普快 新乡 第二天 22:29 2分钟 22:31 1小时5分 1605 171 330 284 516
21 1364 空调普快 安阳 第二天 23:40 16分钟 23:56 1小时9分 1712 176 339 292 530
22 1364 空调普快 邯郸 第三天 0:31 5分钟 0:36 0小时35分 1772 179 345 298 544
23 1364 空调普快 邢台 第三天 1:11 16分钟 1:27 0小时35分 1824 184 355 308 560
24 1364 空调普快 石家庄 第三天 2:41 6分钟 2:47 1小时14分 1937 193 373 323 587
25 1364 空调普快 定州 第三天 3:31 2分钟 3:33 0小时44分 2008 199 382 330 600
26 1364 空调普快 保定 第三天 4:08 4分钟 4:12 0小时35分 2068 204 391 339 615
27 1364 空调普快 北京西 第三天 5:49 -- -- 1小时37分 2214 216 417 362 657
1 T8次领导人专用空调特快列车 成都 当天 -- -- 11:40 0 0 0 0 0
2 T8次领导人专用空调特快列车 绵阳 当天 13:12 7分钟 13:19 1小时32分 115 19 73 0 104
3 T8次领导人专用空调特快列车 广元 当天 16:26 12分钟 16:38 3小时7分 319 47 101 0 147
4 T8次领导人专用空调特快列车 略阳 当天 18:45 2分钟 18:47 2小时7分 454 65 123 0 183
5 T8次领导人专用空调特快列车 宝鸡 当天 22:32 20分钟 22:52 3小时45分 669 92 170 0 254
6 T8次领导人专用空调特快列车 西安 第二天 0:24 10分钟 0:34 1小时32分 842 113 209 0 316
7 T8次领导人专用空调特快列车 洛阳 第二天 4:57 2分钟 4:59 4小时23分 1229 154 281 0 429
8 T8次领导人专用空调特快列车 石家庄 第二天 9:47 18分钟 10:05 4小时48分 1765 203 369 0 568
9 T8次领导人专用空调特快列车 保定 第二天 11:34 2分钟 11:36 1小时29分 1896 215 390 0 600
10 T8次领导人专用空调特快列车 北京西 第二天 13:28 -- -- 1小时52分 2042 231 418 0 642
*/

...全文
139 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
昵称被占用了 2009-07-15
  • 打赏
  • 举报
回复
要进行全查询,速度的提高应该有限
sdhdy 2009-07-15
  • 打赏
  • 举报
回复
似乎没有可优化的。
kkun_3yue3 2009-07-15
  • 打赏
  • 举报
回复
如果EXCEL里的内容不经常变化的话,一次性导入到实表里去
以后就在实表里查
soft_wsx 2009-07-15
  • 打赏
  • 举报
回复
高手出来
soft_wsx 2009-07-15
  • 打赏
  • 举报
回复
PATINDEX('%'+b.车站+'[-)]%',a.线路)=0 我查了一下是这句的问题

如果改成PATINDEX('%'+b.车站+'%',a.线路)=0 速度正常,但是查不出我需要的结果
soft_wsx 2009-07-15
  • 打赏
  • 举报
回复
5楼没有理解我的意思!我是将EXCEL中的数据插入到SQL SERVER表中tran_codes


alter只是修改存储过程定义,

exec tran_proc N'昆明',N'北京西' 这句才是执行

本身EXCEL中目前我只插入了96行数据。
cqq_chen 2009-07-15
  • 打赏
  • 举报
回复
存储过程往表里面插入几万行数据?
问题就在这了,为什么存在execl中呢?不直接存放在sql server中呢?
之所以慢,
select * into tran_codes from openrowset('Microsoft.jet.oledb.4.0','EXCEL 8.0;HDR=yes;database=c:\tran.xls',sheet1$)
是最大的问题了.

还在存储过程如果每次都alter proc
那也不能发挥存储过程的优势的.
soft_wsx 2009-07-15
  • 打赏
  • 举报
回复
太慢了!存储过程往表里面插入几万行数据,正常应该只有几十行

27,579

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧