🚀 go-pugleaf

RetroBBS NetNews Server

Inspired by RockSolid Light RIP Retro Guy

Article View: cn.bbs.comp.database
Article #547

������·�߲�ѯϵͳ��̨��ݿ���ơ������㷨�Ľ�zz

#547
From: Galaxy��VOLUNTEERS��Database
Date: Tue, 03 Mar 2009 02:05
601 lines
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

�� ��Դ:����ˮ˼Դ bbs.sjtu.edu.cn��[FROM: 207.46.55.27]
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!SJTU

Show 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