Article View: cn.bbs.comp.database
Article #547������·�߲�ѯϵͳ��̨��ݿ���ơ������㷨�Ľ�zz
From: Galaxy��VOLUNTEERS��Database
Date: Tue, 03 Mar 2009 02:05
Date: Tue, 03 Mar 2009 02:05
601 lines
23033 bytes
23033 bytes
http://www.cnblogs.com/lucc/archive/2009/03/03/1401863.html
������ҳ�����������ϵ���?�� ���- 13 ����- 2 ����- 204
������·�߲�ѯϵͳ��̨��ݿ���ơ��������㷨�Ľ����Ż�
������·�߲�ѯϵͳ��̨��ݿ����ϵ������
��ݿ����� (����ݿ��Ѿ������˹�����350��������·����Ϊ�������)
�ڡ���ѯ�㷨��һ�����Ѿ�ʵ���˻����㷨�����ǣ�ʹ�ô洢���InquiryT2��ѯ�ӡ���
����������·�ڡ��ij˳�·��ʱ�����־�Ȼ����5���ӲŲ��ҳ���������Ч����
Ȼ���ʺ�ʵ��Ӧ�á���ˣ��б�Ҫ��ԭ�еĻ����㷨�����Ż��Ľ��ڱ����У�����
��һ�ָĽ�Ļ����㷨�����ԭ�е��㷨���Ľ����㷨���ܸ�ǿ��Ч�ʸ��š�
1. ��ѹ����RouteT0
����RouteT0�����¼���
����ͼ��ʾ������ѯS1��S4�Ķ��λ���·��ʱ���������3��2��4=24�����
��ͼ�п��Կ�������1��·���е�3����·������վ�㶼��ͬ����2��3��·��Ҳ�����
������ʵ�ϣ����˲�ѯ�й��ĵ�������վ��֮��������·��ͨ��������dz���ʲô·
�ߣ���ˣ����Խ�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 view GRouteT0
as
select
StartStopKey,
EndStopKey,
min(StopCount) as MinStopCount,
max(StopCount) as MaxStopCount
from RouteT0
group by StartStopKey,EndStopKey
ע�⣬��ͼGRouteT0������StartStopKey��EndStopKey�У�����MinStopCount�У�MinSt
opCount��ָ��StartStop��EndStop�������·��վ�������磺����RouteT0��Ӧ��GRo
uteT0��
3.���β�ѯ�㷨
�����Ƕ��λ��˲�ѯ�Ĵ洢���GInquiryT2�Ĵ��룬�ô洢���ʹ������ʱ������߲�
ѯЧ�ʣ�
/*
��ѯվ��@StartStops��վ��@EndStops֮��Ķ��λ��˳˳�·�ߣ����վ����'/'�ֿ���
����Է��鷽ʽ������磺
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���ʹ�õ����ű�#R1,#R2,#R3����ˣ������ű�������ֱ��Ӱ
�쵽���λ��˲�ѯʹ�õ�ʱ�䣺
��Ȼ��R1�������������������ѯ��ʼվ���Ӧ��#R1���������SQL������£�
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
���н�����£�
��Ȼ�������Ϊ��������ʱ��#R1����������ͬ��ɵ��յ��#R3�������ϵ����
��
��ˣ��ڽ����������������£���ѯ��������������·�ڡ����õ�ʱ�������
���������У���ʹ�á���������Ϊ��㣬������·�ڡ�Ϊ�յ�Զ��β�ѯ�㷨����Ч
�ʲ���
(3)Ч�ʲ���
����������£�
exec GInquiryT2 '������','����·��'
���Խ��
��ѯ������£�
������£�
====================================================
ɸѡ����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���ӣ���ô�����ܲ������Ż��أ�
��ϸ��������Ľ��ɷ��ֲ�ѯʱ���ʱ�IJ����ǻ��˲�ѯ��䣨140ms��������ɸѡ
����2�β�ѯ·�ߵ���䣨825ms������ͼ��ʾ��
��ô��û�з����������ɸѡ��2��·�ߵ�Ч���أ����ǿ϶��ġ�ֻ���GRouteT0�ij�
ʵ�?��������������ˡ��ij�ʵ��Ͳ���Ҫ�ѵ�2��·���浽��ʱ��#R2�У�
�ĺ��GInquiryT2(������ΪGInquiryT2_1)���£�
/*
��ѯվ��@StartStops��վ��@EndStops֮��Ķ��λ��˳˳�·�ߣ����վ����'/'�ֿ���
����Է��鷽ʽ������磺
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
���棬��Ȼ�ò�ѯ��������������·�ڡ�Ϊ����Ըij�ʵ���GInquiryT2��Ч�ʣ�
����������£�
exec GInquiryT2_1 '������','����·��'
��Ϣ����������£�
�������
��������Կ������������250ms
6.չ��·����
GInquiryT2��ѯ����Ľ����10�����·�ߣ���ô��������ܵõ���̵�10��·�ߣ���
Ȼ��ֻ�轫��10��·��չ�����ɣ�չ�����·����>=10��������̵�10��·�߱�Ȼ��չ
���Ľ���С���ѯ10�����·�ߵĴ洢���GInquiryT2_Expand���£�
GInquiryT2_Expand
���棬��Ȼ�Բ�ѯ��������������·�ڡ�Ϊ�����GInquiryT2_Expand���������£�
exec GInquiryT2_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������������ٲ�ѯ��
�ɵĽ����߲�ѯ������Ч�ʡ�
--
Welcome to Database Board
��ӭ������ݿ��
http://bbs.sjtu.edu.cn/bbsdoc?board=database
[m[1;34m�� ��Դ:����ˮ˼Դ bbs.sjtu.edu.cn��[FROM: 207.46.55.27][m
Technical Details
Message-ID:
<4eeUB9$bSb@bbs.sjtu.edu.cn>Path:
arc1-ca.pugleaf.net!g2news1.google.com!news2.google.com!border1.nntp.dca.giganews.com!nntp.giganews.com!news.neu.edu.cn!news.shec6.edu.cn!SJTUShow Full Headers
Complete Article Headers
X-Google-Thread: 101948,37a3d32199c41571,start X-Google-Attributes: gid101948,domainid0,public,usenet X-Google-Language: Chinese,GB Path: arc1-ca.pugleaf.net!g2news1.google.com!news2.google.com!border1.nntp.dca.giganews.com!nntp.giganews.com!news.neu.edu.cn!news.shec6.edu.cn!SJTU From: Dem.bbs@bbs.sjtu.edu.cn (Galaxy��VOLUNTEERS��Database) Newsgroups: cn.bbs.comp.database Subject: ������·�߲�ѯϵͳ��̨��ݿ���ơ������㷨�Ľ�zz Date: 03 Mar 2009 02:05:01 GMT Organization: ��ˮ˼Դ Message-ID: <4eeUB9$bSb@bbs.sjtu.edu.cn> Mime-Version: 1.0 Content-Type: text/plain; charset="gb2312" Content-Transfer-Encoding: 8bit X-Filename: database/M.1236045849.A Lines: 600