Ծ֧ǵĹҵ

: 10Ԫ 50Ԫ

:

ʼ:

绰:

˾:

˵:

֤룺 ֤,?ˢ֤



  Ҫ Ŀ Ƶ Code iProcess γ ֤ ѯ   רļ  
Ա   
 
   
 
  
ÿ15ƪ
ı
׷
 
 
     
   
 
 
ݿŻ
 
 Դcsdn    2017-1-18
Ҫ   482     ۣ      
 

ܵڵĿͨͨ I/O CPU ʱСʹÿѯӦʱ̲޶ȵݿΪﵽĿģҪ˽Ӧóݵ߼ṹ໥ͻݿʹ֮䣨 (OLTP) ֧֣Ȩ⡣

ĿӦᴩڿ׶εọ̈̄Ӧֻʵϵͳʱſ⡣ʹܵõߵ˿ͨʼʱϸƵʵ֡ΪЧŻ Microsoft? SQL Server? 2000 ܣڼΪʶʹ򣬲Щз

Ȼϵͳ⣨ڴ桢ӲȣҲо󣬵Щõͨͨ£SQL Server ԶõӲԴӶٶԴϵͳֶԼõ棩

Ŀ¼

ݿͨɷ̯ڶﵽܼ Web վܼ𣩡

ݿƣݿγΪܵЧ;ݿư߼ݿܹԼԣϵͳλú

ѯŻȷƵIJѯӦóܡ

ӦóƣȷƵûӦóܡӦóư߽硢ʹá

Żʵùߺ͹ܣ Microsoft SQL Server 2000 ṩʵùߺ͹ߵһЩѡЩѡͻ˵ЩߵܵķԼͬʱЩߺӦóЧ

ŻܣθIJϵͳMicrosoft Windows NT?Microsoft Windows? 95Microsoft Windows 98 Microsoft Windows 2000 SQL Server ܡ

ϵͳŻ:ϵͳݿ֮ɸƵķ桭7

ݿ

Ϊﵽ Web վĸܼ𣬶ϵͳһڶ֮ƽÿһĴɡMicrosoft? SQL Server? 2000 ͨ SQL Server ݽˮƽһ̯֮ݿ⴦ɡЩ໥Ҳ໥ЭԴӦóݿһЭΪ塣

ֻеӦóÿ SQL ䷢͵ӵиĴ󲿷ݵijԱʱݿſԴﵽdzߵܼΪʹ SQL 䡣ʹ SQL 䲻ϷеҪȺϵͳͬдҪ

Ȼ뵥ݿָӦóͼͬʵݿķʽϴڲ졣

ȻĿݿȫĹɣǿͨһڲͬķֲ֮ݵķֲʽͼﵽĿġ

ݿ

ݿưɲ֣߼ƺơ߼ݿưʹݿԼΪҵݽģ뿼λ洢Щݡݿư߼ӳ䵽ýϡÿõӲʹþܿضݽʺάҪƺЩѣݿӦó򿪷ڽ׶ȷݿ⡢ʹΪҵģӲܺҪ

ʵSQL ServerݿŻҪһõݿƷʵʹУSQL ServerݿƵòõܺܲʵõݿƱ뿼Щ:

1.1߼淶

һ˵߼ݿƻ淶ǰ3׼:

1.1淶:ûظֵС

2.2淶:ÿǹؼֶαؼ֣1ʽؼֵijЩɲ֡

3.3淶:1ǹؼֶβ1ǹؼֶΡ

Щƻٵк͸ıҲͼ࣬Ҳڴ洢ݵҳϵҲҪͨӵĺϲήϵͳܡij̶ֳϵķǹ淶Ըϵͳܣǹ淶̿Ըܷ治ͬĿöֲͬķУ·ʵ֤ܡ

1.淶Ʋ4··ϲϵͿԿݿʵ()мظ()

2.õļֶ(ܼơֵ)ԿǴ洢ݿʵС

ijһĿļƻϵͳмƻֶΪ:Ŀšƻμƻƻмƻƻ(ƻ+μƻ+ƻ+мƻ)ûҪڲѯͱõģڱļ¼ܴʱбҪѼƻΪ1ֶμ뵽СԲôڿͻ˱ݵһԡ

3.¶ʵԼⲿݻݵĿ֧Ӧķǹ淶:

(1)1ʵ()ָ2(еԷֳ2)ͰƵʵͬٱʵݷֿˡַҪÿиҪؼ֡ڲдٵı

(2)1ʵ()ָ2(езֳ2)ַЩݵʵ()ӦгҪʷ¼ʷ¼õ˿԰ƵʵͬٱʵʷݷֿΪӼ߼(š۷)ʵģôַҲǺкôġ

1.2ݿ

Ҫȷѡʵֲԣ붮ݿʸʽӲԴIJص㣬ҪڴʹϵͳI/OһΧ㷺Ļ⣬µ׼ܻ

1.ÿصӦ÷ӳС洢ռ䣬رǶڱиˡʹsmallint;ͲҪintegerֶͣοԱضȡҿ1ҳϷøУҲͼI/O

2.1ij豸ϣͨSQL ServerΰIJִ1ͬ豸ϣܡϵͳ˶ʹ̿ݷ뼼£ĺôԡ

3.SQL ServerΰһƵʹõĴָ2ʹ̿ݿ豸ϣҲܡΪжͷڲңݷҲܡ

4.SQL Serverΰıͼеݴ1豸Ͽܡ1רõ͵Ŀܽһܡ

ѯŻ

ѯٶԭܶ࣬¼֣

1ûûõ(Dzѯ⣬dzƵȱ)

2I/OСγƿЧӦ

3ûде²ѯŻ

4ڴ治㡡

6ѯ󣨿Բöβѯķ

7(ҲDzѯ⣬dzƵȱ)

8sp_lock,sp_who,û鿴,ԭǶдԴ

9˲ҪкС

10ѯ䲻ãûŻ

ͨ·Żѯ :

1ݡ־ŵͬI/O豸ϣӶȡٶȣǰԽTempdbӦRAID0ϣSQL2000֧֡ߴ磩ԽI/OԽҪ.

2򡢺ָٱijߴ(sp_spaceuse)

4ݲѯ,,ŻŻʷʽƽעҪʵʹĬֵ0ӦþСʹֽСнãĴ,Ҫ޵ļֵֶνһԱֶΡ

5;

6ڴ,Windows 2000SQL server 2000֧4-8Gڴ档ڴ棺ڴСӦڼϲеķá Microsoft SQL Server? 2000 ʱɿǽڴСΪаװڴ 1.5 ⰲװȫļܣ Microsoft ԱִȫͲѯɿǣڴСΪǼаװڴ 3 SQL Server max server memory ѡΪڴ 1.5 ڴСõһ룩

7ӷ CPU;DZײддҪԴڴ档ʹòлǴгMsSQLԶѡġֽɶ񣬾ͿڴС絢ѯӡɨGROUP BY־ͬʱִУSQL SERVERϵͳĸŵIJеȼӵҪĴCPUIJѯʺϲдǸ²Update,Insert Deleteܲд

8ʹlikeвѯĻ򵥵ʹindexDzеģȫĿռ䡣 like 'a%' ʹ like '%a' ʹ like '%a%' ѯʱѯʱֵֶܳȳ,ԲCHARͣVARCHARֶεֵܳĽȫ

9DB Server APPLication Server 룻OLTPOLAP롡

10ֲʽͼʵݿ塣һֿķ໥ЭֵϵͳĴɡͨγݿĻܹһִ֧͵Ķ Web վĴҪйظϢμݿSQLļ'ͼ'

aʵַͼ֮ǰˮƽ

bڴԱÿԱ϶һֲʽͼÿͼͬơ÷ֲʽͼIJѯκһԱСϵͳͬÿԱ϶һԭʼĸһʵÿֻһԱһֲʽͼݵλöӦó͸ġ

11ؽ DBCC REINDEX ,DBCC INDEXDEFRAG,ݺ־ DBCC SHRINKDB,DBCC SHRINKFILE. Զ־.ڴݿⲻҪݿԶήͷܡT-sqlдкܴĽгҪ㣺ȣDBMSѯƻĹģ

1ѯĴʷ﷨顡

2ύDBMSIJѯŻ

3ŻŻʹȡ·Ż

4Ԥģɲѯ滮

5ȻںʵʱύϵͳִС

6ִнظûΣһSQL SERVERݴŵĽṹһҳĴСΪ8K(8060)ֽڣ8ҳΪһBš

12Commitrollback Rollback:عе Commit:ύǰ. ûбҪڶ̬SQLдҪдд磺 begin tran exec(@s) commit trans ߽̬SQL дɺߴ洢̡

13ڲѯSelectWhere־Ʒص,ɨ,زҪݣ˷˷I/OԴĸܡܴڱɨڼ佫סֹӷʱ,ء

14SQLעִûκӰ

15ܲʹù꣬ռôԴҪrow-by-rowִУ÷ǹ꼼,磺ڿͻѭʱTableӲѯCaseȵȡα԰ֵ֧ȡѡзֻࣺ밴մӵһеһе˳ȡСFETCH NEXT Ψһȡ,ҲĬϷʽɹԿακεطȡСαļSQL2000±ùܺǿĿ֧ѭĸѡ READ_ONLYͨα궨λ(Update)ɽû OPTIMISTIC WITH valueS:ֹ۲۵һ׼ֹ֡۲Σڴα꼰еļУֻкСĻõڶûijһСijαԴѡʱûеУ⽫䴦ûͼ޸ijһУеĵǰֵһȡʱȡֵбȽϡκֵı䣬ͻ֪Ѹ˴У᷵һֵһģִ޸ġѡѡOPTIMISTIC WITH ROW VERSIONING:ֹ۲ѡа汾ơʹа汾ƣеıijְ汾ʶȷڶαǷġ SQL Server У timestamp ṩһ֣ʾݿиĵ˳ÿݿⶼһȫֵǰʱֵ@@DBTSÿκηʽĴ timestamp еʱSQL Server ʱд洢ǰ @@DBTS ֵȻ @@DBTS ֵij timestamp УʱᱻǵмͿԱȽijеĵǰʱֵϴȡʱ洢ʱֵӶȷǷѸ¡رȽеֵֻȽ timestamp мɡӦóû timestamp еıҪа汾Ƶֹ۲αĬΪֵֹ۲ơ SCROLL LOCKS ѡʵֱ۲ơڱ۲УڰݿжαʱӦóͼݿСʹ÷αʱжαʱϷһڴα꣬һֱֵύعȡһʱȥαα꣬ȡһʱͱˣÿûҪȫı۲ʱα궼Ӧڴ򿪡ֹκȡӶֹ¸СȻֹֹȡУǵڶҲҪĶȡα궨 Select ָʾЩα겢ѡɹȡʱÿϻȡֵ´ȡαرգȷΪ׼´ȡʱΪȡелȡͷϴȡеĹԱֵһύع֮ύʱرαѡΪأ COMMIT 䲢رκδ򿪵α꣬ҹύ֮άȡݵĸ롣ȡȡα겢ѡα Select еʾʾֵֹֹֻа汾ʾδδδ NOLOCK δδδδ HOLDLOCK UPDLOCK ¸¸ TABLOCKX δδδδδ *ָ NOLOCK ʾʹָ˸ʾıαֻġ

16Profilerٲѯõѯʱ䣬ҳSQL;ŻŻ

17עUNionUNion all UNION allá

18עʹDISTINCTûбҪʱҪãͬUNIONһʹѯظļ¼ڲѯûġ

19ѯʱҪزҪСС

20sp_configure 'query governor cost limit'SET QUERY_GOVERNOR_COST_LIMITƲѯĵԴѯĵԴʱԶȡѯ,ڲѯ֮ǰͶɱ SET LOCKTIMEʱ䡡

21select top 100 / 10 Percent ûصSET ROWCOUNTƲС

22SQL2000ǰһ㲻Ҫµ־: "IS NULL", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", and "LIKE '%500'"ΪDzȫDZɨ衣ҲҪWhere־еӺConvertsubstring,úʱ򣬴ٴ.ԱͨдWhere SUBSTRING(firstname,1,1) = 'm'ΪWhere firstname like 'm%'ɨ裩һҪֿ̫̫ܽNOT INɨʹEXISTSNOT EXISTS IN , LEFT OUTER JOIN ر,ExistsIN죬NOT.еֵпգǰã2000ŻܹˡͬIS NULL"NOT", "NOT EXISTS", "NOT IN"Ż"<>"ȻDzŻò

23ʹQuery Analyzer鿴SQLIJѯƻǷŻSQLһ20%Ĵռ80%ԴŻصЩĵط

24ʹINORʱֲѯûʹʾָ Select * FROM PersonMember (INDEX = IX_Title) Where processid IN ('''Ů')

25ҪѯĽԤȼ÷ڱУѯʱSelectSQL7.0ǰҪֶΡҽԺסԺѼ㡣

26MIN() MAX()ʹõʵ

27ݿһԭǴԽԽãѡDefault,ΪRules,Triggers, ConstraintԼ⽡CheckUNIQUE,͵󳤶ȵȵȶԼ,Procedure.άСдߣִеٶȿ졣

28ҪĶֵImageУʹô洢̣ǧҪǶInsert(֪JAVAǷ)ΪӦóȽֵתַߴַֽܵתɶֵ.洢̾ûЩ: Create procedure p_insert as insert into table(Fimage) values (@image), ǰ̨洢̴ƲٶԸơ

29BetweenijЩʱIN ٶȸ,BetweenܹظҵΧòѯŻɼ select * from chineseresume where title in ('','Ů') Select * from chineseresume where between '' and 'Ů' һġinڱȽ϶ΣʱЩ

30ڱҪǶȫֻ߾ֲʱʱܹٶȣһΪҲķѴԴĴͬʵʱһ

31Ҫûõʱ˷ԴֻڱҪʹʱʹ

32OR־ԷֽɶѯͨUNION ӶѯǵٶֻͬǷʹй,ѯҪõUNION allִеЧʸ.OR־ûõдUNIONʽͼƥ䡣һؼǷõ

33ͼЧʵ͡ͼֱӶԱ,stored procedureرDzҪͼǶ,ǶͼѰԭʼϵѶȡǿͼıʣǴڷϵıŻ˵Ѿ˲ѯ滮SQLԵʱҪʹָͼֱӴӱ߽ͼ϶˲ҪĿ,ѯܵ.Ϊ˼ӿͼIJѯMsSQLͼĹܡ

34ûбҪʱҪDISTINCTORDER BYЩԸڿͻִС˶ĿͬUNION UNION ALLһĵ

35INֵбУƵֵǰ棬ֵٵķ棬жϵĴ

36Select INTOʱסϵͳ(sysobjectssysindexesȵ)ӵĴȡʱʱʾ䣬 select INTO. drop table t_lxh begin tran select * into t_lxh from chineseresume where name = 'XYZ' --commit һSelect * from sysobjectsԿ Select INTO סϵͳCreate table Ҳϵͳ(ʱϵͳ)ǧҪʹĻǾҪõʱʹʵʱ

37һGROUP BY HAVING־֮ǰ޳УԾҪ޳еĹǵִ˳Ӧţselect Where־ѡкʵУGroup ByͳУHaving־޳ķ顣Group By HavingĿСѯ.ڴнзHavingʮԴGroup BYĿIJ㣬ֻǷ飬ôDistinct졡

38һθ¶¼ȷֶθÿһ,˵á

39ʱýTableԵı,Table ͵ıʱá

40SQL2000£ֶǿģҪ£

aֶεıȷġ

bTEXT,NtextImage͡

cѡ ANSI_NULLS = ON, ANSI_PADDINGS = ON, .

41ݵĴڷϣĿʹô洢̡洢DZáŻұ֯һִй滮Ҵ洢ݿеSQL䣬ǿԵļϣٶȵȻ졣ִеĶ̬SQL,ʹʱ洢̣ụ̀ʱTempdbСǰSQL SERVERԸӵѧ㲻֧֣ԲòIJ϶ĿSQL2000֧UDFs,ָ֧ӵѧ㣬ķֵҪ̫ĿܴûԶ庯һִеĴԴشĽô洢̡

42Ҫһ仰ʹͬĺ˷Դ,ڱٵø졡

43Select COUNT(*)Чʽ̵ͣͨдEXISTS.ͬʱע select count(Field of null) from Table select count(Field of NOT null) from Table ķֵDzͬģ

44ڴ湻ʱ߳ = +5ܷЧʣʹ߳<SQL SERVER̳߳, = +5ص𺦷ܡ

45һĴıסAסBôеĴ洢жҪ˳ǡ㣨ģij洢BAܾͻᵼһ˳ûбԤϸƺãѱ֡

46ͨSQL Server Performance MonitorӦӲĸ Memory: Page Faults / secֵż߸ߣʱ߳̾ڴ档ܸߣڴƿ

Process:

1% DPC Time ָڷڼ䴦ڻӳ(DPC)պṩİٷֱȡ(DPC еΪȱ׼Ȩ͵ļ) DPC ȨģʽִеģDPC ʱİٷֱΪȨʱٷֱȵһ֡Щʱ䵥㲢Ҳڼһ֡ʾΪʵʱٷֱȵƽæʱ

2%Processor Timeòֵ95%ƿCPUԿһһĴ

3% Privileged Time ָôʱȨģʽİٷֱȡ(ȨģʽΪϵͳͲӲƵһִģʽֱӷӲڴ档һģʽΪûģʽһΪӦó򡢻ϵͳϵͳƵһ޴ģʽϵͳӦó߳תȨģʽԷʲϵͳ)Ȩʱ % ΪϺ DPC ṩʱ䡣Ȩʱʸ߿ʧ豸ĴļġƽæʱΪʱһʾ

4% User TimeʾķCPUݿִaggregate functionsȡֵܸߣɿʹü򵥵ıӣˮƽָȷ͸ֵ Physical Disk: Curretn Disk Queue LengthֵӦ1.5~2ҪܣӴ̡ SQLServer:Cache Hit RatioֵԽԽá80%Ӧڴ档עòֵǴSQL Server󣬾һֱۼӼоһʱ󣬸ֵܷӳϵͳǰֵ

47select emp_name form employee where salary > 3000 ڴsalaryFloat͵ģŻŻΪConvert(float,3000)Ϊ3000ǸӦڱʱʹ3000.0ҪʱDBMSתַͬݵת

48ѯĹͬд˳򡡡

select a.personMemberID, * from chineseresume a,personmember b where personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' A = B ,B = ''

select a.personMemberID, * from chineseresume a,personmember b where a.personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' and b.referenceid = 'JCNPRH39681' A = B ,B = '' A = ''

select a.personMemberID, * from chineseresume a,personmember b where b.referenceid = 'JCNPRH39681' and a.personMemberID = 'JCNPRH39681' B = '' A = ''

49

(1)IF û븺˴ THEN code1=0 code2=9999 ELSE code1=code2=˴ END IF ִSQLΪ: Select FROM P2000 Where ˴>=:code1 AND˴ <=:code2

(2)IF û븺˴ THEN Select FROM P2000 ELSE code= ˴ Select ˴ FROM P2000 Where ˴=:code END IF һַֻһSQL,ڶַSQL䡣û븺˴ʱ,ڶַȻȵһִַЧʸ,Ϊû; ˸˴ʱ,ڶַȻȵһַЧʸ,һ,IJѯ㡣дҪ鷳

50JOBCNڲѯҳ·£ŻܵƿI/OٶϣµķŻʵЧCPUڴϣڵķáµķ˵ԽСԽá

begin

DECLARE @local_variable table (FID int identity(1,1),ReferenceID varchar(20))

insert into @local_variable (ReferenceID)

select top 100000 ReferenceID from chineseresume order by ReferenceID

select * from @local_variable where Fid > 40 and fid <= 60

end

begin

DECLARE @local_variable table (FID int identity(1,1),ReferenceID varchar(20))

insert into @local_variable (ReferenceID)

select top 100000 ReferenceID from chineseresume order by updatedate

select * from @local_variable where Fid > 40 and fid <= 60

end IJͬ

begin

create table #temp (FID int identity(1,1),ReferenceID varchar(20))

insert into #temp (ReferenceID)

select top 100000 ReferenceID from chineseresume order by updatedate

select * from #temp where Fid > 40 and fid <= 60 drop table #temp

end

ȫͨϵͳŻڴСļϵͳ͡Ŀ͵ȣܺˡⲻַͨЩ⣺ӦóԼӦóύݿIJѯ͸£Щѯ͸ݿܹ

ʱسIJѯ͸¿ԭ

ͨѶٶ

ڴ治 Microsoft? SQL Server? 2000 õڴ治㡣

ȱõͳݡ

ͳݹڡ

ȱõ

ȱõ

ѯ»ѵʱԤڵijʱʹļ嵥ܣ

˵뼼֧ṩϵ֮ǰȲοü嵥

1.ѯǷйأ磬ǷΪǷκӵ½ʹ Windows NT ܼ SQL Server غ SQL Server صܡйظϢμʹϵͳмӡ

2.ѯأ漰ĸѯѯʹ SQL ¼̽ʶٲѯйظϢμʹ SQL ¼̽мӡ

ͨʹ SET SHOWPLANSTATISTICS IOSTATISTICS TIME STATISTICS PROFILE ѡȷݿѯܡ

SHOWPLAN SQL Server ѯŻѡݼйظϢμ SET SHOWPLAN_ALL

STATISTICS IO õÿɨ߼ȡڸٻзʵҳȡʴ̵ĴйصϢйظϢμ SET STATISTICS IO

STATISTICS TIME ʾִвѯʱ䣨ԺΪλйظϢμ SET STATISTICS TIME

STATISTICS PROFILE ʾÿѯִкĽѯִеļйظϢμ SET STATISTICS PROFILE

SQL ѯУԴ graphical execution plan ѡ鿴 SQL Server μݵͼαʾ

ЩռϢʹȷ SQL Server ѯŻִвѯԼʹЩЩϢȷͨдѯıϵ޸ݿƵȷܷܡйظϢμѯ

3.ǷѾõͳŻѯ

SQL Server ԶϴڵֲֵͳơҲʹ SQL ѯ CREATE STATISTICS ڷֶͳƣ auto create statistics ݿѡΪ trueԶڷϴͳơѯЩͳȷѵIJѯԡӲ漰ķάӵͳϢ߲ѯܡйظϢμͳϢ

ʹ SQL ¼̽ SQL ѯڵͼִмƻӲѯȷѯǷ㹻ͳϢйظϢμ;¼ࡣ

4.ѯͳϢǷΪ£ͳϢǷԶ£

SQL Server Զϴ²ѯͳƣֻҪûнԶѯͳƸԣ⣬ʹ SQL ѯ UPDATE STATISTICS ڷֹͳƣ auto update statistics ݿѡΪ trueԶڷϸͳơµͳƲȡڻʱݡδ UPDATE ѯͳϢµġ

ûнͳΪԶ£ӦΪԶ¡йظϢμͳϢ

5.ǷкʵһǷ߲ѯܣйظϢμŻ顣

6.Ƿκȵȵ㣿УʹôйظϢμʹļݺ RAID

7.ǷΪѯŻṩŻӲѯйظϢμѯŻ顣

洢̵Ż

һǰԣھһʱĴ洢֮̿дһЩʱС;ҹϣԴ棬ҪSybaseSQL Serverݿ⣬ݿӦһЩԡ

ʺ϶߶ݿ⿪Աݿܶ࣬漰SP洢̣ŻĿԱݿŨȤˡ

ܣݿĿУӵҵ߼ͶݿIJʱͻSPװݿĿSP϶࣬дûһĹ淶ӰԺϵͳάѺʹSP߼⣬ݿĿSPҪܣͻŻ⣬ٶпܺ飬һŻSPҪһܲSPЧ߼ٱ

ġݣ

1ԱõTableViewڵǰнViewʵֿòҪֱʹádatabse.dbo.table_nameΪsp_dependsʾSPʹõĿtableviewУ顣

2ԱύSPǰѾʹset showplan onѯƻIJѯŻ顣

3߳ЧʣŻӦóSPдӦע¼㣺

a) SQLʹù淶

i. holdlockӾ䣬ϵͳ

ii. ⷴͬһŻűϴıԿȸȡݵʱУȻӡ

iii.ʹα꣬ΪαЧʽϲαݳ1УôӦøдʹα꣬ҪαѭٽбӵIJ

iv. עwhere־д뿼˳Ӧø˳򡢷ΧСȷӾǰ˳򣬾ֶܵ˳˳һ£ΧӴС

v. ҪwhereӾеġ=߽кʽ㣬ϵͳ޷ȷʹ

vi. ʹexistsselect count(1)жǷڼ¼countֻͳƱʱʹãcount(1)count(*)Чʡ

vii.ʹá>=Ҫʹá>

viii.עһЩorӾunionӾ֮滻

ix.ע֮ӵͣⲻ֮ͬӡ

x. ע洢в͵Ĺϵ

xi.עinsertupdateֹӦóͻ200ҳ棨400kôϵͳҳɱ

b) ʹù淶

i. ĴҪӦýϿǣOLTPҪ6

ii. ܵʹֶΪѯǾ۴Ҫʱͨindex index_nameǿָ

iii.Դѯʱtable scanҪʱ½

iv. ʹֶΪʱôʹõеĵһֶΪʱܱ֤ϵͳʹøᱻʹá

v. Ҫעάؽ±洢̡

c)tempdbʹù淶

i. ʹdistinctorder bygroup byhavingjoincumputeΪЩtempdbĸ

ii. ƵɾʱϵͳԴġ

iii.½ʱʱһԲܴôʹselect intocreate tablelogٶȣΪ˻ϵͳԴcreate tableȻinsert

iv. ʱϴҪôӦýʱͽĹ̷ڵһӴ洢Уܱ֤ϵͳܹܺõʹõʱ

v. ʹõʱڴ洢̵ؽеʱʽɾtruncate tableȻdrop tableԱϵͳĽϳʱ

vi. ôʱӲѯ޸ģϵͳΪֲһжʹtempdbϵͳ

d)㷨ʹã

ᵽSQLŻASE TuningֲеSQLŻ,ʵӦ,ö㷨бȽ,ԻԴ١ЧߵķASEset statistics io on, set statistics time on , set showplan on ȡ

һЩõŻҪעķ棺

Ż IN INдSQLŵDZȽд׶Ƚʺִķ񡣵INSQLDZȽϵ͵ģORACLEִеIJINSQL벻INSQL ORACLEͼתɶӣתɹִINӲѯٲѯı¼תɹֱӲöӷʽѯɴ˿ɼINSQLٶһתĹ̡һSQLתɹںзͳƵȷSQLͲתˡ

ƼҵܼSQLоIN NOT IN

˲ǿƼʹõģΪӦñƼNOT EXISTS +жΪգ

<> ڣڲԶõģ˶Ĵֻȫɨ衣ƼͬܵIJ棬

a<>0 Ϊ a>0 or a<0 a<> Ϊ a> IS NULL IS NOT NULLжֶǷΪգжֶǷΪһDzӦģΪBDzֵġ

ƼͬܵIJ棬

a is not null Ϊ a>0 a>ȡֶΪգһȱʡֵֵҵ״ֶ̬βΪգȱʡΪ롣λͼзıܽλͼȽѿƣֵֶ̫ʹ½˸²ݿ > < ڻСڲ

ڻСڲһDzõģΪͻңе¿ԶŻһ100¼һֵֶA30¼A=030¼A=139¼A=21¼A=3ôִA>2A>=3ЧкܴˣΪA>2ʱORACLEҳΪ2ļ¼ٽбȽϣA>=3ʱORACLEֱҵ=3ļ¼

LIKE LIKEӦͨѯͨϿܴﵽIJѯõòϵ⣬LIKE %5400% ֲѯLIKE X5400%÷ΧһʵӣYW_YHJBQKӪҵźĻʶſѯӪҵ YY_BH LIKE %5400% ȫɨ裬ijYY_BH LIKE X5400% OR YY_BH LIKE B5400% YY_BHΧIJѯܿ϶ߡ UNION

UNIONڽбӺɸѡظļ¼ڱӺĽ㣬ɾظļ¼ٷؽʵʴ󲿷ӦDzظļ¼ṉ̀ʷUNION磺 select * from gc_dfys union select * from ls_jg_dfys SQLʱȡĽռɾظļ¼󷵻ؽĻܻᵼô̽ƼUNION ALLUNIONΪUNION ALLֻǼ򵥵Ľϲͷء

select * from gc_dfys union all select * from ls_jg_dfys

SQLдӰͬһͬһܲͬдSQLӰһSQLAԱдΪ Select * from zl_yhjbqk BԱдΪ Select * from dlyx.zl_yhjbqkߵǰ׺ CԱдΪ Select * from DLYX.ZLYHJBQKд DԱдΪ Select * from DLYX.ZLYHJBQKм˿ոĸSQLORACLE֮ĽִеʱһģǴORACLEڴSGAԭԵóORACLEÿSQL һηռùڴ棬SQLַʽдȫͬORACLEֻһΣڴҲֻһεķⲻԼٷSQLʱ䣬ҿԼٹڴظϢORACLEҲ׼ȷͳSQLִƵʡ WHERE˳Ӱ

WHEREӾ˳ԴIJѯֱӵӰ죬 Select * from zl_yhjbqk where dy_dj = '1KV' and xh_bz=1 Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1KV'

SQLdy_djѹȼxh_bz־ֶζûִеʱȫɨ裬һSQLdy_dj = '1KV'ڼ¼ڱΪ99%xh_bz=1ıֻΪ0.5%ڽеһSQLʱ99%¼dy_djxh_bzıȽϣڽеڶSQLʱ0.5%¼dy_djxh_bzıȽϣԴ˿ԵóڶSQLCPUռԱȵһ͡

ѯ˳ӰFROMıеб˳SQLִӰ죬ûORACLEûжԱͳƷORACLEᰴֵ˳ӣɴΪ˳򲻶ԻʮֺķԴݽ档עԱͳƷORACLEԶȽСӣٽдӣ

SQLöԲŻϽڣ

ֶεһЩŻúֶβ磺

substr(hbs_bh,1,4)=5400Żhbs_bh like 5400% trunc(sk_rq)=trunc(sysdate)Ż sk_rq>=trunc(sysdate) and sk_rq<trunc(sysdate+1) ʽʽֶβܽ磺 ss_df+20>50Żss_df>30

X||hbs_bh>X5400021452Żhbs_bh>5400021542 sk_rq+5=sysdateŻsk_rq=sysdate-5

hbs_bh=5401002554Żhbs_bh= 5401002554עhbs_bh ʽto_numberתΪhbs_bhֶַ͡

ڰ˶ֶʱܽ磺 ys_df>cx_df޷Ż

qc_bh||kh_bh=5400250000Żqc_bh=5400 and kh_bh=250000 ӦORACLEHINTʾ

ʾORACLESQLִ·ҪõġԶSQL·ʾ

Ŀ귽ʾ COSTɱŻ

RULEŻ CHOOSEȱʡORACLEԶѡɱŻ

ALL_ROWSео췵أ FIRST_ROWSһݾ췵أ

ִзʾ USE_NLʹNESTED LOOPSʽϣ USE_MERGEʹMERGE JOINʽϣ USE_HASHʹHASH JOINʽϣʾ

INDEXTABLE INDEXʹʾıвѯ

߼ʾ粢дȵȣ ORACLEʾDZȽǿĹܣҲDZȽϸӵӦãʾֻǸORACLEִеһ飬ʱڳɱĿORACLEҲܲᰴʾСʵӦãһ㲻鿪ԱӦORACLEʾΪݿ⼰һܿһطˣһطȴ½ˣORACLESQLִзѾȽϳ죬ִе·ӦݿṹҪǰܣڴ桢ļƬݿ󣨱ͳϢǷȷ⼸

ûŻݿվȣݿĴȡήϵͳܡǴ£վݿܲɷֵĹϵݿվṩ˴ԡԻɫʵ˺ܶĹܡ 1 ҪǸݿݿϵͳܡԲοйSQLܵ鼮ѧѯʽ͸ʽĽѯ䡣 2 ʵ£ܵô洢̶SQLѯΪǰѾԤ룬ٶȸ졣ͬʱݿҪЩݣǷشASPˡ֮ҪֺЧطݿǿܣǵҪʺϢ 3 ڿӦʹSQL ServerAccessΪAccessǻļݿ⣬ûܺܲݿӾʹOLEDBͷDSNʽΪӷʽиõIJܡ

4 ʹDAOData Access ObjectsRDORemote Data ObjectsԴΪҪӦڵûĴϵͳADOActiveX Data ObjectsΪWebӦƵġ 5 ¼RescordsetʱҪα(cursort)ʽ(locktype)ΪڲͬķʽASPԲͬķʽݿ⣬ִٶҲкܴڴʱֻݣôĬα꣨ǰֻõܡ 6 ADOʱ򣬻Ľ϶CPUڡˣһASPҳжݿֶαһϺõķʽǽֵֶȷ뱾رȻֱӵñرʾݡ 7 ADO ConnectionҲһ⡣һӣConnection󱻴洢ApplicationжASPҳʹãôҳͻʹӡӶ󱻴洢SessionУҪΪÿûһݿӣͼСӳصãWebݿѹÿʹADOASPҳͷADOݿӡΪIISڽݿӳأַdzЧȱÿASPҳ涼ҪһЩͷŲ 8 ASPǿҪ;֮һǶݿвݿҪע⣺ҪʹáSELECT ...... ʽSQLѯ䡣ӦþҪЩֶΡһ10ֶΣֻõеһֶΣname͸ʹáselect name from mytableáselect from mytableֶȽٵʱߵܲԣǵһӵмʮֶεʱݿܶ㲢ҪݡòҪΪ˽ʡʱߺ²ҶӦֶƵ鷳Ҫʵʵʹáselect id,name,age... from mytable 9 ʱرմ򿪵ļ¼Լ(Connection)󡣼¼ӶķϵͳԴ൱ǵĿ޵ġ̫ļ¼ԼӶȴûйرǣܻASPտʼʱٶȺܿ죬мԽԽ·ʹ·йرգ MyRecordSet.closeSet MyRecordSet=Nothing Set MyConnection=Nothing 10 ݿȻʹODBCϵͳļDSNݿ⣬ʹúܿOLEDBӡʹúߣƶWebļʱҪ޸á OLEDBλӦóODBC֮䡣ASPҳУADOλOLEDB֮ϵij򡣵ADOʱȷ͸OLEDBȻٷ͸ODBC㡣ֱӵOLEDB㣬ô󣬽߷˵ܡôֱӵOLEDBأʹSQLServer 7ʹĴΪַ strConnString = "DSN='';DRIVER={SQL SERVER};" & _

"UID=myuid;PWD=mypwd;" & _ "DATABASE=MyDb;SERVER=MyServer;"

ҪIJǡDRIVER=֡ƹODBCʹOLEDBSQL Serverʹ﷨ strConnString ="Provider=SQLOLEDB.1;Password=mypassword;" & _ "Persist Security Info=True;User ID=myuid;" & _ "Initial Catalog=mydbname;" & _

"Data Source=myserver;Connect Timeout=15" ΪʲôҪΪʲôѧϰµӷܹؼΪʲôʹñ׼DSNϵͳDSNãWroxǵADO 2.0Աο鼮IJԣʹOLEDBӣҪʹDSNDSNlessӣµ֣ܱ߱Ƚϣ ---------------------------------------------------------------------- SQL Access ʱ: 18 82 ظ1000¼ʱ䣺2900 5400

OLEDB DSN OLEDB DSN ʱ䣺62 99 ظ1000¼ʱ䣺100 950

---------------------------------------------------------------------- WroxADO 2.0ԱοʱԺΪλظ1000¼ʱԷͱķʽġ

һӣ

select a. *, m.amount from tableA a, ( select b.fieldD, sum(c.total_amount) amount from tableA b, tableB c where b.fieldC = 100 and b.fieldA in ('AA', 'BB', 'CC', 'DD', 'EE', 'FF') and b.fieldId = c.fieldId group by b.fieldD ) m where a.fieldC = 100 and a.fieldD = m.fieldD and a.fieldA = 'GG'

sqlжͬһɨ,Ч̫,ʲô취Աд? tableA,tableB ӱϵ벻Ҫsql server ̫﷨ΪҪõoracleСoracle˻ش

------------------------------------------

SQLдǸҵҪ󣬸дЧܺԡ

ȷһSQLִ·

1ȻֱtableAtableBӦfilterʹmӲѯеwhereȻӣܻnestloophash join...ȡ

ݹȻлܣgroup bym

2ὫmtableA㣩˺a.fieldC = 100 and a.fieldA = 'GG'Ľӣжӷʽ

a. *, m.amount

·һִе·ͻɻ󣺡ͬһɨΡ϶ָtableAˡûнص

˵IJѯ㽨ҲͨrowidλУȨǡɨ衱ڲIJѯӦòᷢɨ裨all table

accessӦɨ裨index scanŶԡһ㣬ǿȿǽЧʡ

Կǽ

create index idx_1 on tableA(fieldC,fieldA,fieldId,fieldD)

create index idx_2 on tableB(fieldId,total_amount)

ִзԱ֤ͳֵ׼ȷ

ڲִмƻӦǶidx_1idx_2ɨ裨index scanȻmľɨ裨

index scan + rowid to tableĽӡ

ѯƻԣŻãҪʹrboҪʹcboûв/* index*/ʾǿָ....

ǵ濼ǡDzٶȣǽʵ廯ͼﻯͼֻmֽʵ廯tableAtableB

ھ̬Կǽʵ廯

иdzõӲܽˣ

SERVERݿʵֿٵȡݷҳ´˵ʵݿġͷļһIJݽṹ

CREATE table [dbo].[TGongwen] (--TGongwenǺͷļ

[Gid] [int] ideNTITY (1, 1) NOT NULL , --idţҲ

[title] [varchar] (80) COLLATE Chinese_PRC_CI_AS NULL , --ͷļı

[fariqi] [datetime] NULL , --

[neibuYonghu] [varchar] (70) COLLATE Chinese_PRC_CI_AS NULL , --û

[reader] [varchar] (900) COLLATE Chinese_PRC_CI_AS NULL ,

--Ҫûÿûм÷ָ,ֿ

) ON [PRIMARY] TEXTimage_ON [PRIMARY]

GO

棬ݿ1000ݣ

declare @i int

set @i=1

while @i<=250000

begin

insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-2-5','ͨſ','ͨſ,칫,ֳ,ֳ,žֳ,admin,֧,֧,Ѳ֧,֧,,ΰ֧,¿','ȵ25¼')

set @i=@i+1

end

GO

declare @i int

set @i=1

while @i<=250000

begin

insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-9-16','칫','칫,ͨſ,ֳ,ֳ,žֳ,admin,֧,֧,Ѳ֧,֧,,¿','м25¼')

set @i=@i+1

end

GO

declare @h int

set @h=1

while @h<=100

begin

declare @i int

set @i=2002

while @i<=2003

begin

declare @j int

set @j=0

while @j<50

begin

declare @k int

set @k=0

while @k<50

begin

insert into Tgongwen(fariqi,neibuyonghu,reader,title) values(cast(@i as varchar(4))+'-8-15 3:'+cast(@j as varchar(2))+':'+cast(@j as varchar(2)),'ͨſ','칫,ͨſ,ֳ,ֳ,žֳ,admin,֧,֧,Ѳ֧,֧,,¿','50¼')

set @k=@k+1

end

set @j=@j+1

end

set @i=@i+1

end

set @h=@h+1

end

GO

declare @i int

set @i=1

while @i<=9000000

begin

insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-5-5','ͨſ','ͨſ,칫,ֳ,ֳ,žֳ,admin,֧,֧,Ѳ֧,֧,,ΰ֧,¿','ӵ900¼')

set @i=@i+1000000

end

GO

ͨ䣬Ǵ25200425շļ¼25ɰ칫200496շļ¼200220031002500ͬڡͬļ¼50ͨſ200455շ900¼ϼ1000

һˣʵʵʵֲѯŻҪǰᡣ

indexdz֮һҪġûĴ洢ϵݽṹֵʱṩ˶ݵĿٷʡʵϣû,ݿҲܸselectɹؼűԽԽʹáʵЧԽԽԡע⣬仰УˡʵʣΪʹʱ濼ʵֹ̣ȿҲƻݿĹܡ

һdzṹ

ʵϣ԰ΪһĿ¼΢SQL SERVERṩۼclustered indexҲƾؼͷǾۼnonclustered indexҲƷǾǴؼ棬Ǿ˵һ¾ۼͷǾۼ

ʵǵĺֵıһۼ磬Ҫ顰֣ͻȻطֵǰҳΪƴǡanƴֵֵӢĸaͷԡzβģô־ȻֵǰԡaͷIJȻҲ֣ô˵ֵû֣ͬģ顰š֣ҲὫֵ䷭󲿷֣ΪšƴǡzhangҲ˵ֵIJֱһĿ¼ҪȥĿ¼ҵҪҵݡ

ǰݱһְһеĿ¼Ϊۼ

ʶij֣ԿٵشԶв鵽֡Ҳܻʶ֣֪ķʱͲܰողŵķҵҪ֣ҪȥݡƫԲס鵽Ҫҵ֣ȻֺҳֱӷijҳҵҪҵ֡ϡĿ¼ֱ͡鵽ֵ򲢲ĵ򷽷顰š֣ǿԿڲ鲿֮ļֱСšҳ672ҳֱСšǡ֣ۡҳȴ63ҳšǡ֣ҳ390ҳȻЩֲķֱλڡšֵ·ġۡšʵϾڷǾۼеֵеڷǾۼеӳ䡣ǿַͨʽҵҪ֣Ҫ̣ҵĿ¼еĽȻٷҪҳ롣

ǰĿ¼Ŀ¼ĴĵʽΪǾۼ

ͨӣǿ⵽ʲôǡۼ͡Ǿۼ

һһ£ǿԺ׵⣺ÿֻһۼΪĿ¼ֻܰһַ

ʱʹþۼǾۼ

ıܽ˺ʱʹþۼǾۼҪ

ʹþۼʹ÷Ǿۼ

оӦӦ

ijΧڵӦӦ

һٲֵͬӦӦ

СĿIJֵͬӦӦ

ĿIJֵͬӦӦ

ƵµвӦӦ

ӦӦ

ӦӦ

Ƶ޸вӦӦ

ʵϣǿͨǰۼͷǾۼĶϱ磺ijΧڵһijһʱУǡѾۺ˸Уʱѯ2004112004101֮ȫʱٶȾͽǺܿģΪⱾֵǰڽģֻҪҵҪеĿͷͽβݼɣǾۼȲ鵽Ŀ¼в鵽ÿһݶӦҳ룬Ȼٸҳ鵽ݡ

ʵʣ̸ʹõ

۵ĿӦáȻǸղг˺ʱӦʹþۼǾۼʵϹȴױӻܸʵۺϷǽʵʵ̸һʹõԱڴķ

1Ǿۼ

뷨ΪǼ˴ģǶԾۼһ˷ѡȻSQL SERVERĬϽۼġ

ͨǻÿжһIDУÿݣIDԶģһΪ1ǵ칫ԶʵеGidˡʱǽΪSQL SERVERὫĬΪۼкôǿݿаID򣬵Ϊ岻

Զ׼ۼǺԵģÿֻһۼĹʹþۼø

ǰ̸ľۼĶǿԿʹþۼôܹݲѯҪѸСѯΧȫɨ衣ʵӦУΪIDԶɵģDz֪ÿ¼IDţǺʵIDвѯʹIDΪۼΪһԴ˷ѡΣÿIDŶֶͬΪۼҲϡĿIJֵͬ²Ӧۺ򣻵Ȼֻû޸ļ¼ݣرʱḺãڲѯٶȲûӰ졣

ڰ칫ԶϵͳУϵͳҳʾҪûǩյļ黹ûļѯκ½ݲѯ벻ֶεǡڡûġû

ͨ칫Զҳʾÿûδǩյļ顣ȻǵwhereԽƵǰûδǩյϵͳѽ˺ܳʱ䣬ܴôÿÿûҳʱ򶼽һȫɨ裬Dzģû1ǰļѾˣֻͽݿĿѡʵϣȫûϵͳҳʱݿѯû3δļͨڡֶƱɨ裬߲ѯٶȡİ칫ԶϵͳѾ2꣬ôҳʾٶϽԭٶ8졣

֮ᵽϡ֣ΪľۼäĿؽIDʱIJѯٶûôߵģʹڡڡֶϽǾۺǾһ1000¸ֲѯٶȱ֣3ڵΪ25

1ϽۼҲʱΣ

Select gid,fariqi,neibuyonghu,title from tgongwen

ʱ128470루128룩

2ϽۼfariqϽǾۼ

select gid,fariqi,neibuyonghu,title from Tgongwen

where fariqi> dateadd(day,-90,getdate())

ʱ53763루54룩

3ۺУfariqiϣ

select gid,fariqi,neibuyonghu,title from Tgongwen

where fariqi> dateadd(day,-90,getdate())

ʱ2423루2룩

ȻÿȡĶ25ݣIJȴǾ޴ģرǽۼʱIJ졣ʵϣݿ1000ĻIDϣϵĵ12ҳϵı־dzʱ޷ʾҲIDΪۼһҪء

óٶȵķǣڸselectǰӣdeclare @d datetime

set @d=getdate()

selectӣ

select [ִлʱ()]=datediff(ms,@d,getdate())

2ֻҪ߲ѯٶ

ʵϣǿԷУ23ȫͬҽֶҲͬͬĽǰfariqiֶϽǷǾۺڴֶϽǾۺѯٶȴ֮ԣκֶϼ򵥵ؽ߲ѯٶȡ

ӽУǿԿ1000ݵıfariqiֶ5003ͬ¼ڴֶϽۺٺʲˡʵУÿ춼ᷢļ⼸ļķھͬȫϽۼҪģȲֲֻܾͬмͬĹɴ˿ǽʵľۺ߲ѯٶǷdzҪġ

3Ҫ߲ѯٶȵֶζӽۼ߲ѯٶ

Ѿ̸ڽݲѯʱ벻ֶεǡڡûġûȻֶζ˵Ҫǿ԰Ǻϲһcompound index

ܶΪֻҪκֶμӽۼ߲ѯٶȣҲ˸еԻѸϵľۼֶηֿѯôѯٶȻ𣿴⣬һµIJѯٶȣ25ݣfariqiڸϾۼʼУûneibuyonghuںУ

1select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5'

ѯٶȣ2513

2select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5' and neibuyonghu='칫'

ѯٶȣ2516

3select gid,fariqi,neibuyonghu,title from Tgongwen where neibuyonghu='칫'

ѯٶȣ60280

УǿԿþۼʼΪѯͬʱõϾۼȫеIJѯٶǼһģȫĸлҪԿ죨ڲѯĿһ£øϾۼķʼΪѯĻDzκõġȻ12IJѯٶһΪѯĿһжϣҲѯٵĻͻγɡǡܿԴﵽšͬʱסǷ񾭳ʹþۺУǰһҪʹƵС

ģûеʹþܽ

1þۺòǾۺٶȿ

ʵ䣺ȡ25ݣ

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'

ʹʱ䣺3326

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid<=250000

ʹʱ䣺4470

þۺòǾۺٶȿ˽1/4

2þۺһorder byʱٶȿ죬رС

select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by fariqi

ʱ12936

select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by gid

ʱ18843

þۺһorder byʱٶȿ3/10ʵϣСĻþۼΪҪʹ÷ǾۼٶȿԵĶܴࣻĻ10ϣߵٶȲԡ

3ʹþۺڵʱΣʱᰴռݱİٷֱȳɱ٣۾ۺʹ˶ٸ

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1'

ʱ6343루ȡ100

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-6-6'

ʱ3170루ȡ50

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'

ʱ3326루ϾĽһģһɼһôôںź͵ںһģ

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1' and fariqi<'2004-6-6'

ʱ3280

4 вΪзѯٶ

У100ݣ200411Ժ50ֻͬڣھȷգ֮ǰ505000ͬڣھȷ롣

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1' order by fariqi

ʱ6390

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi<'2004-1-1' order by fariqi

ʱ6453

壩ע

ˮۣɸۡҲһ߼ܣ򲻵ҲᵼϵͳЧΪûڱÿӽһݿҪĹᵼƬ

˵ҪһʵϵرǶԾۺĴӦ󾫣ʹݿܵõܵķӡ

ȻʵУΪһְݿԱҪһЩҳַЧߡΪЧ

SQLܶ˲֪SQLSQL SERVERִеģǵԼдSQLᱻSQL SERVER⡣磺

select * from table1 where name='zhangsan' and tID > 10000

ִ:

select * from table1 where tID > 10000 and name='zhangsan'

һЩ˲ִ֪ЧǷһΪ򵥵ĴȺϿȷDzһtIDһۺôһӱ10000Ժļ¼вҾˣǰһҪȴȫвҿмname='zhangsan'ģٸtID>10000ѯ

ʵϣĵDzҪġSQL SERVERһѯŻԼwhereӾеȷĸСɨռ䣬Ҳ˵ʵԶŻ

ȻѯŻԸwhereӾԶĽвѯŻȻбҪ˽һ¡ѯŻĹԭʱѯŻͻ᲻ıпٲѯ

ڲѯ׶ΣѯŻ鿴ѯÿ׶βҪɨǷáһ׶οԱһɨSARGôͳ֮ΪŻģҿٻݡ

SARGĶ壺һΪָͨһضƥ䣬һֵ÷ΧڵƥANDӡʽ£

<>

<>

Գڲһߣڲһߡ磺

Name=

۸>5000

5000<۸

Name= and ۸>5000

һʽSARGʽ޷ķΧˣҲSQL SERVERÿһжжǷWHEREӾеһڲSARGʽıʽ˵õġ

SARGܽһʹSARGԼʵĺijЩϽ۲ͬľ飺

1LikeǷSARGȡʹõͨ

磺name like % SARG

name like %š ,ͲSARG

ԭͨ%ַĿͨʹ޷ʹá

2or ȫɨ

Name= and ۸>5000 SARGName= or ۸>5000 򲻷SARGʹorȫɨ衣

3DzIJSARGʽ

SARGʽ͵ǰDz䣬磺NOT!=<>!<!>NOT EXISTSNOT INNOT LIKEȣ⻹кǼSARGʽӣ

ABS(۸)<5000

Name like %

Щʽ磺

WHERE ۸*2>5000

SQL SERVERҲΪSARGSQL SERVERὫʽתΪ

WHERE ۸>2500/2

DzƼʹãΪʱSQL SERVERܱ֤תԭʼʽȫȼ۵ġ

4IN ൱OR

Select * from table1 where tid in (2,3)

Select * from table1 where tid=2 or tid=3

һģȫɨ裬tidҲʧЧ

5NOT

6exists in ִЧһ

ܶ϶ʾ˵existsҪinִЧҪߣͬʱӦܵnot existsnot inʵϣһ£ֶǰnotִ֮ЧʶһġΪ漰ӲѯSQL SERVERԴpubsݿ⡣ǰǿ԰SQL SERVERstatistics I/O״̬򿪡

1select title,price from titles where title_id in (select title_id from sales where qty>30)

þִнΪ

'sales'ɨ 18߼ 56 Σ 0 ΣԤ 0 Ρ

'titles'ɨ 1߼ 2 Σ 0 ΣԤ 0 Ρ

2select title,price from titles where exists (select * from sales where sales.title_id=titles.title_id and qty>30)

ڶִнΪ

'sales'ɨ 18߼ 56 Σ 0 ΣԤ 0 Ρ

'titles'ɨ 1߼ 2 Σ 0 ΣԤ 0 Ρ

ǴӴ˿ԿexistsinִЧһġ

7úcharindex()ǰͨ%LIKEִЧһ

ǰ棬̸LIKEǰͨ%ôȫɨ裬ִЧǵµġеϽ˵úcharindex()LIKEٶȻд飬˵ҲǴģ

select gid,title,fariqi,reader from tgongwen where charindex('֧',reader)>0 and fariqi>'2004-5-5'

ʱ7룬⣺ɨ 4߼ 7155 Σ 0 ΣԤ 0 Ρ

select gid,title,fariqi,reader from tgongwen where reader like '%' + '֧' + '%' and fariqi>'2004-5-5'

ʱ7룬⣺ɨ 4߼ 7155 Σ 0 ΣԤ 0 Ρ

8unionԱorִЧʸ

ǰѾ̸whereӾʹorȫɨ裬һģ϶Ƽunionorʵ֤˵ڴ󲿷ֶõġ

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16' or gid>9990000

ʱ68롣ɨ 1߼ 404008 Σ 283 ΣԤ 392163 Ρ

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'

union

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid>9990000

ʱ9롣ɨ 8߼ 67489 Σ 216 ΣԤ 7499 Ρ

unionͨ±orЧҪߵĶࡣ

飬߷orߵIJѯһĻôunion򷴵orִٶȲܶ࣬Ȼunionɨorɨȫ

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16' or fariqi='2004-2-5'

ʱ6423롣ɨ 2߼ 14726 Σ 1 ΣԤ 7176 Ρ

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'

union

select gid,fariqi,neibuyonghu,reader,title from Tgongwen wherefariqi='2004-2-5'

ʱ11640롣ɨ 8߼ 14806 Σ 108 ΣԤ 1144 Ρ

9ֶȡҪա١١ԭ򣬱⡰select *

һ飺

select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc

ʱ4673

select top 10000 gid,fariqi,title from tgongwen order by gid desc

ʱ1376

select top 10000 gid,fariqi from tgongwen order by gid desc

ʱ80

ɴ˿ÿȡһֶΣݵȡٶȾͻӦٶȻҪֶεĴСжϡ

10count(*)count(ֶ)

ijЩ˵*ͳУȻҪһЧʵ͡˵ʵûиݵġ

select count(*) from Tgongwen

ʱ1500

select count(gid) from Tgongwen

ʱ1483

select count(fariqi) from Tgongwen

ʱ3140

select count(title) from Tgongwen

ʱ52050

ϿԿcount(*)count()ٶ൱ģcount(*)ȴκγֶλٶҪ죬ֶԽܵٶȾԽ룬count(*) SQL SERVERܻԶСֶܵġȻֱдcount()ĸֱЩ

11order byۼЧ

gidfariqiǾۺУ

select top 10000 gid,fariqi,reader,title from tgongwen

ʱ196 롣ɨ 1߼ 289 Σ 1 ΣԤ 1527 Ρ

select top 10000 gid,fariqi,reader,title from tgongwen order by gid asc

ʱ4720롣ɨ 1߼ 41956 Σ 0 ΣԤ 1287 Ρ

select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc

ʱ4736롣ɨ 1߼ 55350 Σ 10 ΣԤ 775 Ρ

select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi asc

ʱ173롣ɨ 1߼ 290 Σ 0 ΣԤ 0 Ρ

select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi desc

ʱ156롣ɨ 1߼ 289 Σ 0 ΣԤ 0 Ρ

ǿԿٶԼ߼Ǻ͡order by ۼС ٶ൱ģЩȡorder by ǾۼСIJѯٶǿöġ

ͬʱijֶνʱǵٶǻ൱ġ

12ЧTOP

ʵϣڲѯȡݼʱӰݿӦʱزݲңI/0磺

select top 10 * from (

select top 10000 gid,fariqi,title from tgongwen

where neibuyonghu='칫'

order by gid desc) as a

order by gid asc

䣬ϽִʱӦñӾִʱ䳤ʵ෴ΪӾִк󷵻ص10000¼10䣬ӰݿӦʱI/OI/O˴Ч֮һʹTOPؼˡTOPؼSQL SERVERоϵͳŻһȡǰǰٷֱݵĴʡʵеӦãTOPȷʵܺãЧҲܸߡһݿORACLEȴûУⲻ˵һźȻORACLEп磺rownumberԺĹڡʵǧݵķҳʾ洢̡УǾͽõTOPؼʡ

ΪֹʵִӴݿпٵزѯҪݷȻǽܵЩǡʵУǻҪǸ֡Ӳأ磺ܡܡϵͳܣȡ

ʵСͺݵͨ÷ҳʾ洢̽һweb Ӧãҳܱز١ݿ⴦ʮֳ⡣ݷҳ:ADO ¼ҳҲADOԴķҳܣα꣩ʵַҳַҳڽСΣΪα걾ȱ㣺αǴڴУܷڴ档αһͽصļ¼סֱȡαꡣαṩ˶ضɨֶΣһʹαбݣȡIJͬвͬIJڶʹжα꣨ݼϣѭʹһĵȴ

ҪǣڷdzģͶԣҳʱմͳÿζԴķǷdz˷ԴġеķҳһǼҳСĿݣǼеݣȻ󵥲ִеǰС

ϺõʵָҳСҳȡݵķžǡ˹洢̡洢α꣬αľԣûеõҵձϿɡ

˸˴˴洢̣Ĵ洢̾ǽǵİ칫Զʵдķҳ洢̣

CREATE procedure pagination1

(@pagesize int,--ҳСÿҳ洢20¼

@pageindex int --ǰҳ

)

as

set nocount on

begin

declare @indextable table(id int identity(1,1),nid int)--

declare @PageLowerBound int--ҳĵ

declare @PageUpperBound int--ҳĶ

set @PageLowerBound=(@pageindex-1)*@pagesize

set @PageUpperBound=@PageLowerBound+@pagesize

set rowcount @PageUpperBound

insert into @indextable(nid) select gid from TGongwen where fariqi >dateadd(day,-365,getdate()) order by fariqi desc

select O.gid,O.mid,O.title,O.fadanwei,O.fariqi from TGongwen O,@indextable t where O.gid=t.nid

and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id

end

set nocount off

ϴ洢SQL SERVER¼DDӦ˵洢Ҳһdzķҳ洢̡ȻУҲ԰еıдʱCREATE TABLE #TempԣSQL SERVERУʱûñġԱ߸տʼʹ洢ʱоdzIJٶҲԭADOĺáַ˱ȴ˷õķ

ϿһƪСġݱȡnmļ¼ķȫ£

publish ȡ n m ļ¼ SELECT TOP m-n+1 * FROM publish WHERE (id NOT IN (SELECT TOP n-1 id FROM publish))

id Ϊpublish Ĺؼ

ҵʱƪµʱǾΪ֮һ񣬾˼·dzúáȵ칫ԶϵͳASP.net+ C#SQL SERVERʱ򣬺Ȼƪ£һ£Ϳһdzõķҳ洢̡Ҿƪ£û뵽»ûҵȴҵһƪݴдһҳ洢̣洢ҲĿǰΪеһַҳ洢̣ҺܺûȰָɴ洢̣

CREATE PROCEDURE pagination2 ( @SQL nVARCHAR(4000),--SQL @Page int,--ҳ @RecsPerPage int, --ÿҳɵļ¼ @ID VARCHAR(255), --ҪIJظID @Sort VARCHAR(255)--ֶμ ) AS

DECLARE @Str nVARCHAR(4000)

SET @Str='SELECT TOP '+CAST(@RecsPerPage AS VARCHAR(20))+' * FROM ('+@SQL+') T WHERE T.'+@ID+'NOT IN (SELECT TOP '+CAST((@RecsPerPage*(@Page-1)) AS VARCHAR(20))+' '+@ID+' FROM ('+@SQL+') T9 ORDER BY '+@Sort+') ORDER BY '+@Sort

PRINT @Str

EXEC sp_ExecuteSql @Str GO

ʵԼΪ

SELECT TOP ҳС *

FROM Table1

WHERE (ID NOT IN

(SELECT TOP ҳС*ҳ id

FROM

ORDER BY id))

ORDER BY ID

洢һȱ㣬NOT INȻҿ԰Ϊ

SELECT TOP ҳС *

FROM Table1

WHERE not exists

(select * from (select top (ҳС*ҳ) * from table1 order by id) b where b.id=a.id )

order by id

not existsnot inǰѾ̸ˣߵִЧʵûġ

ȱˣTOP NOT INDZαҪÿһЩ

Ȼnot existsϸ洢̵ЧʣʹSQL SERVERеTOPؼȴһdzǵѡΪҳŻĿľDZļ¼ǰҲѾᵽTOPƣͨTOP ʵֶĿơ

ڷҳ㷨УӰDzѯٶȵĹؼ㣺TOPNOT INTOPǵIJѯٶȣNOT INǵIJѯٶȣҪҳ㷨ٶȣҪ׸NOT INͬ

֪κֶΣǶͨmax(ֶ)min(ֶ)ȡijֶеСֵֶβظôͿЩظֶεmaxminΪˮ룬ʹΪҳ㷨зֿÿҳIJǿò><ʹʹѯSARGʽ磺

Select top 10 * from table1 where id>200

Ǿ·ҳ

select top ҳС *

from table1

where id>

(select max (id) from

(select top ((ҳ-1)*ҳС) id from table1 order by id) as T

)

order by id

ѡ񼴲ظֵ׷ֱСʱͨѡ±г˱1000ݵİ칫ԶϵͳеıGIDGIDǾۼΪСȡgid,fariqi,titleֶΣֱԵ11010050010001102550ҳΪַҳִٶȣλ룩

ҳ뷽1 2 3

1 60 30 76

10 46 16 63

100 1076 720 130

500 540 12943 83

1000 17110 470 250

1 24796 4500 140

10 38326 42283 1553

25 28140 128720 2330

50 121686 127846 7168

ϱУǿԿִ洢ִ100ҳµķҳʱǿεģٶȶܺáһִַзҳ1000ҳϺٶȾͽڶַԼִзҳ1ҳϺٶȿʼַȴʼûдĽƣȻ㡣

ȷ˵ַҳǿԾݴдһ洢̡֪SQL SERVERĴ洢ȱõSQL䣬ִЧҪͨWEBҳ洫SQLִЧҪߡĴ洢̲зҳҳ洫IJȷǷͳơ

-- ȡָҳ

CREATE PROCEDURE pagination3

@tblName varchar(255), --

@strGetFields varchar(1000) = '*',-- Ҫص

@fldName varchar(255)='',-- ֶ

@PageSize int = 10,-- ҳߴ

@PageIndexint = 1, -- ҳ

@doCountbit = 0, -- ؼ¼, 0 ֵ򷵻

@OrderType bit = 0,-- , 0 ֵ

@strWherevarchar(1500) = ''-- ѯ (ע: Ҫ where)

AS

declare @strSQL varchar(5000) --

declare @strTmp varchar(110)-- ʱ

declare @strOrder varchar(400)--

if @doCount != 0

begin

if @strWhere !=''

set @strSQL = "select count(*) as Total from [" + @tblName + "] where "+@strWhere

else

set @strSQL = "select count(*) as Total from [" + @tblName + "]"

end

--ϴ˼@doCountݹIJ0ִͳơµд붼@doCountΪ0

else

begin

if @OrderType != 0

begin

set @strTmp = "<(select min"

set @strOrder = " order by [" + @fldName +"] desc"

 

--@OrderType0ִнҪ

end

else

begin

set @strTmp = ">(select max"

set @strOrder = " order by [" + @fldName +"] asc"

end

if @PageIndex = 1

begin

if @strWhere != ''

set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "from [" + @tblName + "] where " + @strWhere + " " + @strOrder

else

set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "from ["+ @tblName + "] "+ @strOrder

--ǵһҳִϴ룬ӿִٶ

end

else

begin

--´븳@strSQLִеSQL

set @strSQL = "select top " + str(@PageSize) +" 
"+@strGetFields+ "from ["
+ @tblName + "] where [" + @fldName + "]" + @strTmp +
"(["+ @fldName + "]) from (select top " +
str((@PageIndex-1)*@PageSize) +
" ["+ @fldName + "] from [" + @tblName + "]"
+ @strOrder + ") as tblTmp)"+ @strOrder
if @strWhere != ''
set @strSQL = "select top " + str(@PageSize) +"
"+@strGetFields+ "from ["
+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["
+ @fldName + "]) from (select top " +
str((@PageIndex-1)*@PageSize) + " ["
+ @fldName + "] from [" + @tblName + "] where " + @strWhere + " "
+ @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder
end
end
exec (@strSQL)
GO

洢һͨõĴ洢̣עдˡ

ڴ£رڲѯҳʱ򣬲ѯʱһ㲻ᳬ9룻洢̣ʵоͻᵼ³ʱ洢̷dzڴݿIJѯ

ϣܹͨϴ洢̵ĽܸҴһʾһЧͬʱϣͬʵʱݷҳ㷨

ġۼҪԺѡۼһڵıУдǣʵСͺݵͨ÷ҳʾ洢̡Ϊڽ洢Ӧڡ칫Զϵͳʵʱ߷ִ洢С£

1ҳٶһά13֮䡣

2ڲѯһҳʱٶһΪ58룬·ҳֻ3ҳ30ҳ

Ȼڳ£ҳʵֹǺܿģڷǰҳʱ13ٶȱһûоŻķҳٶȻҪûĻ˵ǡûACCESSݿٶȿ족ʶԵûʹϵͳ

߾ʹ˷һ£ԭ֢˵ļ򵥣˵ҪֶβǾۼ

ƪµĿǣѯŻҳ㷨ֻ԰ѡѯŻ͡ҳ㷨ϵǺܴһ𣬾Ϊ߶ҪһdzҪĶDDۼ

ǰѾᵽˣۼƣ

1ٶСѯΧ

2ٶȽֶ

1ڲѯŻʱ2ڽзҳʱ

ۼÿֻܽһʹþۼԵøӵҪۼѡ˵ʵ֡ѯŻ͡Чҳؼء

ҪʹۼмȷϲѯеҪַеҪͨһìܡ

ǰ桰УfariqiûΪ˾ۼʼУڵľȷΪաŵ㣬ǰѾᵽˣڽлʱεĿٲѯУIDкܴơ

ڷҳʱۼдظ¼޷ʹmaxminΪҳIJ޷ʵָΪЧ򡣶IDΪۼôۼ֮⣬ûκôʵ˷˾ۼԴ

ΪìܣߺһУĬֵΪgetdate()ûд¼ʱԶд뵱ʱʱ䣬ʱ侫ȷ롣ʹΪ˱ԺСغϣҪڴϴUNIQUEԼΪۼС

ʱ;ۼ֮ûͼȿвûڲʱijʱεIJѯֿΪΨһʵmaxminΪҳ㷨IJ

Ż߷֣Ǵ»С£ҳٶһ㶼Ǽʮ룬0롣ڶСΧIJѯٶȱԭҲûκγٶۡ

ۼ˵ҪԱܽһ£һҪۼڣ

1ƵʹõġСѯΧֶϣ

2ƵʹõġҪֶϡ

Ӧó

Ӧóھʹ Microsoft? SQL Server? 2000 ϵͳܷؼáͻΪʵݿͻȷѯ͡ʱύѯԼδѯⷴԷϵͺͳʱ䡢I/O Լ (CPU) ɵȲҪӰ죬ɴӰܵӡ

ΪˣӦóƽ׶ȷʮҪȻʹʹܿӦóʱƺܸĿͻӦó򣩳⣬ҲıӰܵĸأͻ˾֧ãĿͻⶼ޷Ӧó SQL Server ֳ֧ǧIJû֮ƲӦóʹǿķƽ̨û

ͻӦó׼

ͻ˺ SQL Server ֮ͨݿӦóܽϲҪԭ˷Ϳͻ֮䴫͵һصӰ졣ڿͻӦó SQL Server ֮Ϊÿͽ͵ĻỰͨʹô洢̣ԽС磬Ӧóݴ SQL Server յֵȡͬIJֻҪܾӦֱڴ洢Ӷ

洢ж䣬Ĭ£SQL Server ÿʱͻӦóһϢϸ˵ÿӰӦóҪЩϢȷӦóҪǣԽЩϢܡʹ SET NOCOUNT ỰΪӦóЩϢйظϢμ SET NOCOUNT

ʹС

ûҪĽǧУڿͻ CPU I/O ĸأʹӦóԶʹͲƶûԡýӦóΪʾû㹻ϢԱѯύɴСеĽйظϢμʹøЧݼŻӦóܡ

ɰʵĿӦóƼɲѯʱͨпƣǿijЩֶΣѯԼʹ TOPPERCENT SET ROWCOUNT Transact-SQL ƲѯصйظϢμʹ TOP PERCENT ƽ SET ROWCOUNT

ûҪ¿ӦóʱȡִеIJѯ

ӦóӦǿûͻȡѯһ㽫޷⡣Ӧóȡѯʹÿʽݿ (ODBC) sqlcancel ȡѯӦ񼶱ʵĿǡ磬ȡѯύعûȡѯڻȡˣȡѯʼҪύعͬҲڿȡѯ DB-Library Ӧóӿ (API)

ʼʵֲѯʱ

ҪòѯСʵ API òѯʱ磬ʹ ODBC SQLSetStmtOption

йòѯʱĸϢμ ODBC API ĵ

йʱĸϢμԶʱ

ҪʹòʽƷ͵ SQL Server SQL Ӧó򿪷ߡ

߻ڸ߼Ķ͸ Transact-SQL 䣬ҲṩѯȡѯʱȫƵȹؼܣҪʹ๤ߡӦó͸ SQL 䣬ͨάõܻ⣬Ϊ²ʽƣһ״Ҫ

Ҫֺ֧ (OLTP) ѯһйظϢμ֧֡

ֻڱҪʱʹαꡣ

αǹϵݿеùߣʹαʼձʹ򼯺ϵ SQL 仨Ѷࡣ

ʹ򼯺ϵ SQL ʱͻӦó÷ָļ¼ΪԪɸ¡ͨαʱͻӦóҪΪÿά汾ϢֻΪ˿ͻȡкС

ңʹαζŷͨҪʱ洢άͻ˵״̬ϢûڷϵĵǰмΪڶͻά״̬ϢĴķԴڹϵݿ⣬õIJÿͻӦóٽԱڸε֮䲻ڷάͻ˵״̬Ϣ򼯺ϵ SQL ִ֧˲ԡ

Ȼѯʹα꣬ȷʹøЧαֻͣα꣩򵥸ѯܷЧرдαѯйظϢμʹøЧݼŻӦóܡ

ʹ񾡿̡ܼйظϢμӦóܵӰ졣

ʹô洢̡йظϢμ洢̶ӦóܵӰ졣

ʹ Prepared Execution ִв SQL 䡣йظϢμ Prepared Execution (ODBC)

ʼմн

ҪƻʹδȡѯʱֹͣеӦó򡣷ͨᵼͽܡйظϢμ˽ͱ

ȷӦóΪɱйظϢμ١

ȷܹŻֲʽѯܵʵѡйظϢμŻֲʽѯ

ӦϵͳУҪؿ¼㣺

ݿҪݽṹĸĿľΪ߲ѯЧʡڴݿƷIBMISAMṹʹҪǡôʹԭ£

ھӣûָΪϽӵֶŻԶ

Ƶ飨group byorder byϽ

ʽоõIJֵͬ϶ϽڲֵͬٵϲҪڹԱġԱֻСС롰Ůֵͬ˾ޱҪ߲ѯЧʣؽ͸ٶȡ

жЩϽcompound index

ʹϵͳߡInformixݿһtbcheckߣڿɵϽм顣һЩݿϣʧЧΪƵʹöȡЧʽͣһʹIJѯ׵tbcheck߼ԣҪʱ޸⣬ݿ´ݺɾؽ߲ѯٶȡ

2

Ӧ򻯻Դͱظ򡣵ܹԶʵĴʱŻͱIJ衣һЩӰأ

вһ򼸸У

group byorder byӾеĴĴһ

Բͬı

Ϊ˱ⲻҪ򣬾ҪȷغϲݿʱӰĹ淶Чʵֵõģ򲻿ɱ⣬ôӦͼСеķΧȡ

3Դͱݵ˳ȡ

ǶײѯУԱ˳ȡԲѯЧʿܲӰ졣˳ȡԣһǶ3IJѯÿ㶼ѯ1000УôѯҪѯ10ݡҪǶӵн磬ѧѧš䡭ѡαѧšγ̺šɼҪӣҪڡѧšֶϽ

ʹò˳ȡеļ϶ijЩʽwhereӾǿŻʹ˳ȡIJѯǿȶordersִ˳

SELECT FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008

Ȼcustomer_numorder_numϽŻʹ˳ȡ·ɨΪҪǷеļϣӦøΪ䣺

SELECT FROM orders WHERE customer_num=104 AND order_num>1001

UNION

SELECT FROM orders WHERE order_num=1008

·ѯ

4Ӳѯ

һеıǩͬʱѯwhereӾеIJѯг֣ôܿܵѯеֵı֮Ӳѯ²ѯһΡѯǶײԽ࣬ЧԽͣӦӲѯӲѯɱ⣬ôҪӲѯй˵ܶС

5ѵʽ

MATCHESLIKEؼ֧ͨƥ䣬Ͻʽƥرķʱ䡣磺SELECT FROM customer WHERE zipcode LIKE 98_ _ _

ʹzipcodeֶϽҲDz˳ɨķʽΪSELECT FROM customer WHERE zipcode >98000ִвѯʱͻѯȻٶȡ

⣬ҪǿʼӴ䣺SELECT FROM customer WHERE zipcode[23] >80whereӾв˷ǿʼӴҲʹ

6ʹʱٲѯ

ѱһӼ򲢴ʱʱܼٲѯڱ滹ܼŻĹ磺

SELECT cust.namercvbles.balanceother columns

FROM custrcvbles

WHERE cust.customer_id = rcvlbes.customer_id

AND rcvblls.balance>0

AND cust.postcode>98000

ORDER BY cust.name

ѯҪִжζֹһΣ԰δĿͻҳһʱļУͻֽ

SELECT cust.namercvbles.balanceother columns

FROM custrcvbles

WHERE cust.customer_id = rcvlbes.customer_id

Żʵùߺ͹

ݿִԻ

ݺͻԭ

ݴƵС

ִݿ̨ (DBCC)

һ£ҪŻЩȻܹܺؼУɲһЩŻܡ

Microsoft SQL Serverݿں1ڷõIJѯŻԶŻSQLύݲѯݲѯָ֧SQLؼWHEREHAVINGIJѯSELECTDELETEUPDATEڷõIJѯŻͳϢӾķù㡣

˽Żݴ̵ļ򵥷ǼSHOWPLANûַĹ(isql)ͨSHOW SHOWPLAN ONõSHOWPLANʹͼλѯSQL Enterprise ManagerеIJѯ߻isql/w趨ѡṩһϢ

SQL ServerŻͨ3׶:ѯѡ񡢺ϲѡ:

1.ѯ

ڲѯ׶ΣSQL ServerŻ鿴ÿһѯӾ䣬жǷܱŻSQL ServerһᾡŻЩɨӾ䡣磬/ϲӾ䡣DzкϷSQL﷨ԷֳɿŻӾ䣬纬SQLȹϵ<>Ӿ䡣Ϊ<>1ųԵIJ1ԵIJɨ֮ǰ޷ȷӾѡΧж󡣵1ϵͲѯквŻӾʱִмƻñɨʲѯ֣ڲѯпŻSQL ServerӾ䣬Żִѡ

2.ѡ

ÿŻӾ䣬Ż鿴ݿϵͳȷǷصڷݡֻеее1ǰ׺ѯӾеȫƥʱűΪõġΪǸе˳ģҪƥǾȷƥ䡣ڷִԭҲǸ˳ġĴҪзݣڵ绰вΪijϵĿһûʲôãΪ㻹ǵò鿴ÿһȷǷ1ӾпõôŻͻΪȷѡԡ

ƹУҪݲѯ׼ϸеIJѯԲѯŻصΪ

(1)ȽխбȽϸߵЧʡڱȽխ˵ÿҳܴŽ϶УļҲ١ԣܷøҳҲI/O

(2)SQL ServerŻܷͺϲԡٵĿȣ϶խŻṩѡ񡣵DzҪҪΪǽӴ洢άĿ֧ڸSQL Se

Ż

Microsoft? SQL Server? 2000 ԶܶѡϵͳԱֻٵĵУЩѡϵͳԱ޸ģһ㽨鱣ΪĬֵʹ SQL Server ܸʱԶе

ҪŻܣ

SQL Server ڴ

I/O ϵͳ

Microsoft Windows NT? ѡ

MSSQLʹڴ:

Ŀһݻ棬ڴ㹻ùݺ;õͳͳӵڴУֱڴ治ʱ򣬲Űʵ͵ݸһڿstatistics ioʱ򣬿physics read0

ξDzѯĿһ˵hash joinǻȽϴڴ濪ģmerge joinnested loopĿȽСмαҲǻбȽϴĿġ

ڹһҪ

ξǶִмƻϵͳݵĴ洢ЩDZȽСġ

ݻܵӰ죬ϵͳûӦóڴ棬ݻһԽԽãЩʱǻǿаһЩpinڸٻСӦóȻҪʱMSSQLͷڴ棬߳лIOȴЩҲҪʱģԾͻܵĽ͡ǾͱMSSQLڴʹáSQL Server ԣڴѡҵʹڴĵطҲʹsp_configureɡûӦóôͲҪMSSQLڴʹá

ȻѯĿȻԽԽãΪDzܴеõô෴ʹԽڴζŲѯٶȵĽ͡һҪмαʹãھϽ

ĴŻݿϵͳ

ܶDBAܶɣսһϵͳԭij̲Դ޸ģϵͳӦñȽϹؼ޸ģԴҵĿģһ̶ȵļܣеʱ--쵼Ϊ˱Σʱϵͳϵ⻹Ƚأ취ôϵͳŻô?

ҳܽһ¿е;

ضSQL"" (Metalink 122812.1)Ľִмƻ

ͳϢ (/״ͼͳ)

(ӻʵɾҪ)

ﻯͼ(ÿռ俪ȡʱ)

ŻOSݿ

Żϵͳ-IJĺϵͳԴĺ; IOĵ,ǺҪһ֣ΪIOٶȺϵͳƿ;ԴŻ-TCP/IPIJ;

Oracleʼ

Żģʽ趨,db_cache 趨,sga СȲ趨ݿҪӰ졣

ϵͳԴ

һЩΪϵͳУϵͳԴĵDZȽҪģȲԴáеϵͳϵͳ֮DZȽϺģһʱ֮󣬿Ϊı仯SQLִмƻ仯Ȼɲʱϵص϶ϵͳѹϵ⡣

ݿ

pctfree ,freelist 洢

ռļݿ󣨱Ĵ̷ֲ

cache һЩõݿ

ϵͳBugӰ/Ľ

OracleBug࣬ϵͳгеBugΣԣʱƣBugϵͳ⡣ʱϵͳBug ޸ѾݿϵͳDZҪġͨOracleȱݣͬʱҲܻǿݿЧʡȻҲҪעܴIJӰ졣

ϵͳŻ

1. ϵͳܵĺûֱӰݿʹܣϵͳ⣬CPUءڴ潻I/Oƿȣ£ݿڲܵDzϵͳܵġǿͨWindows NTϵͳ(System Monitor)ظ豸ƿCPU һֳȱϵͳĴϵͳCPUͺٶȾġϵͳû㹻CPUͲ㹻شҪǿʹSystem MonitorȷCPUʹʣ75%ߵʳʱУͿCPUƿ⣬ʱӦCPUǰϵͳԣΪSQLЧʷdzͣŻڽϵ͵CPUʡȷҪǿĴCPUøCPU 滻ڴ SQL ServerʹõڴSQL Serverؼ֮һڴͬI/OϵͳĹϵҲһdzҪء磬I/OƵϵͳУSQL ServerݵĿڴԽִ࣬еI/OҲԽ١ΪݽݻжȡǴӴ̶ȡͬڴIJԵĴ̶дƿΪϵͳI/OSystem MonitorSQL ServerBuffer Cache Hit Ratioʾ90%ӦӸڴ档I/OϵͳI/OϵͳƿݿϵͳͬӲйص⡣úܲI/Oϵͳس̶ȽڱдܲSQL䡣I/OϵͳģһִܹеI/O޵ģһһͨĴÿֻܴ85I/OأЩI/OҪŶӣSQLI/Oӳٽܻܳʹʱʹ߳ڵȴԴĹбֿ״̬ϵͳܵӰ졣

I/OϵͳйصҲ׵ģ£ӴͿԽ⡣

ȻӰܵغܶ࣬ӦָͬҳһͨõŻǺѵģֻϵͳάĹеľϼԵ

2SQL ServerصӲϵͳ

SQL ServerйصӲưϵͳڴ桢ϵͳ磬4ֻϹӲƽ̨Windows NTSQL Serverϡ

2.1ϵͳ(CPU)

ԼľҪȷCPUṹĹ̾ǹӲƽ̨ռCPUĹĹ̡ľ鿴CPUӦ180586/100ֻ23û㹻ˣָ֧û͹ؼӦãƼPentium ProPCPU

2.2ڴ(RAM)

ΪSQL ServerȷʵڴöʵõҪġSQL Serverڴ̻桢ݺ桢̬֧ÿ֧SQL Server2GBڴ棬Ҳֵһ뿼ǵWindows NTصķҲҪռڴ档

Windows NTΪÿWIN32Ӧóṩ4GBַռ䡣ַռWindows NTڴ(VMM)ӳ䵽ڴϣijЩӲƽ̨ϿԴﵽ4GBSQL ServerӦóַֻ֪Բֱӷڴ棬VMMƵġWindows NTõڴַռ䣬SQL ServerڴڿõڴʱήSQL Serverܡ

ЩַռרΪSQL ServerϵͳõģͬһӲƽ̨ϻ(ļʹӡӦó)УôӦÿǵҲռһڴ档һ˵Ӳƽ̨Ҫ32MBڴ棬УWindows NTҪռ16MB1򵥵ķǣÿһû100KBڴ档磬100ûҪ32MB+100û*100KB=42MBڴ棬ʵʵʹҪеʵ˵ڴϵͳܵõ;

2.3ϵͳ

1õĴI/OϵͳʵõSQL ServerһҪķ档۵Ĵϵͳ1̿豸1Ӳ̵ԪжԴúļϵͳĿǡSCSI-2̿Dzѡص:

(1)ٻ档(2)дԼٶϵͳCPUжϡ(3)첽д֧֡(4)32λRAID֧֡(5)SCSI2(6)ǰٻ(1ŵ)

3

ھѡӲƽ̨ʵ1õݿⷽҾ߱ûӦ÷֪ʶӦƲѯˡ2SQL ServerȡõIJѯʮҪģ1ǸSQL ServerŻ֪ʶɲѯ;2SQL Serverص㣬ǿݷʲ

 

   
 
 
ĵ ؿγ



Ǹݿ
ݿƾ̸
ݿƹ
ݿܽ
ݿܵż
ݿܵ
ݿŻ
ݿϵͳܵϵ
ݿŻ
߼ݿܹʦ
ݲֿھ
Hadoopԭܵ
 

MySQLݽṹ
MySQLܹܵ
SQL Serverݿⱸָ
ݿ 10DB2Ż
oracleʱռд
ݿĿƽ̨
...   

ݿ
߼ݿܹʦ
Hadoopԭʵ
Oracle ݲֿ
ݲֿھ
Oracleݿ⿪
γ...   

IT˾ androidƽ̨ʵ
Android
ijԴҵ Android
ij칫˾ AndroidIOSӦ
Linuxں
Ĭ Ƕʽܹ
Ƕʽܹ
...   
 
 
 
 
 
ÿ2ĵ/Ƶ
ɨ΢Ŷά붩
ļ¿
ÿ300Դ
 
 

| ϵ | ICP10020922 110108001071