Everything about database

People often can be immersed in an error when use SQL,Pay close attention to the result at earning too namely correct,And oversight the function difference that different implementation may exist between the method,Difference of this kind of function is in large or it is complex database environment in (if online general affairs handles DSS of OLTP or decision-making support system) in expression is gotten particularly apparent.The author discovers in working practice,Undesirable SQL is designed at impertinent index oneself toward come-and-go, deficient join condition and the Where clause that cannot optimize.After undertaking be optimizinged appropriately to them,Its traversal speed had apparent ground to rise!Below my general undertakes summing up respectively from these three respects:
To show an issue more intuitionisticly,The SQL run time in all example all passes a test,All express what do not exceed 1 second to be (%26lt;1 second) .
Test environment- -
Lead plane:HP LH II
Advocate frequency:330MHZ
Memory:128 million
Operating system:Operserver5.0.4
Database:Sybase11.0.3

One, unreasonable index design
Exemple:Watch Record has 620000 to go,Try look below different index,Below the moving circumstance of a few SQL:
1. Build on Date one is not a teeming index



Select Count(*) From Record Where Date%26gt;19991201 ' of ' And Date%26lt;19991214 ' of ' And Amount%26gt;2000 (25 second) Select Date, second of Sum(amount) From Record Group By Date (55) Select Count(*) From Record Where Date%26gt;19990901 ' of ' ' of BJ of ' of And Place In (, ' SH ' ) (27 seconds)


Analysis:

There is many repetition to be worth on Date,In be not teeming index to fall,Data is deposited randomly on physics on data page,When limits is searched,Must carry out watch scanning ability to find the overall travel inside this one limits.


2. In a on Date teeming index





Select Count(*) From Record Where Date%26gt;19991201 ' of ' And Date%26lt;19991214 ' of ' And Amount%26gt;2000 (14 seconds) Select Date, sum(amount) From Record Group By Date (28 seconds) Select Count(*) From Record Where Date%26gt;19990901 ' of ' ' of BJ of ' of And Place In (, ' SH ' ) (14 seconds)


Analysis:


Below teeming index,Data is by order on physics on data page,Repeat a value to also be arranged together,Consequently when limits is searched,The place having tip that can find this range first,And in page of data of the scanning inside this limits,Avoided big range to scan,Rose to inquire speed.


3. In Place,Date,The combination on Amount indexes





Select Count(*) From Record Where Date%26gt;19991201 ' of ' And Date%26lt;19991214 ' of ' And Amount%26gt;2000 (26 seconds) Select Date, sum(amount) From Record Group By Date (27 seconds) Select Count(*) From Record Where Date%26gt;19990901 ' of ' BJ of ' of And Place In (, ' SH ' ) (%26lt;1 second)


Analysis:


This is a very not reasonable combination index,The bellwether row because of it is Place,The first did not cite with the second SQL Place,Also use index without benefit accordingly;The third SQL used Place,And all quotative rows are included in constituting index,Formed index to enclothe,So its speed is very fast.


4. In Date,Place,The combination on Amount indexes





Select Count(*) From Record Where Date%26gt;19991201 ' of ' And Date%26lt;19991214 ' of ' And Amount%26gt;2000(%26lt;1 second) Select Date, sum(amount) From Record Group By Date (11 seconds) Select Count(*) From Record Where Date%26gt;19990901 ' of ' ' of BJ of ' of And Place In (, ' SH ' ) (%26lt;1 second)


Analysis:


This is a reasonable assorted index.It lists Date as bellwether,Make every SQL can use index,And be in the first and index was formed to enclothe in the third SQL,Consequently function was achieved best.


5. Sum up:


The index dispute that builds below default circumstance is teeming index,But sometimes it is not first-rate;Reasonable index design should be built mix in the analysis to all sorts of inquiry forecast on.Generally speaking:


? . Have a large number of repetition value, and often limits inquires (Between, %26gt; , %26lt;,%26gt; = , %26lt;= ) with the row of Order By, Group By happening,Can consider to build teeming index;


? . Often access much line at the same time,And every are contained repeat a value to be able to consider to build combination to index;


? . Assorted index should make crucial inquiry forms index to enclothe as far as possible,Its bellwether row is to use the most frequent line certainly.



Two, deficient join condition:


Exemple:Watch Card has 7896 to go,There is a blame to collect index on Card_no,Watch Account has 191122 to go,There is a blame to collect index on Account_no,Try look below different watch join condition,The executive circumstance of two SQL:





Select Sum(a.amount) From Account A, card B Where A.card_no = B.card_no (20 seconds)


SQL instead:





Select Sum(a.amount) From Account A, card B Where A.card_no = B.card_no And A. Account_no=b.account_no (%26lt;1 second)


Analysis:


Below condition of the first join,Optimal inquiry plan is make Account outer watch,Card makes lining express,The index that uses Card to go up,Its I/O frequency can is by the following and formulary estimation:


Outer the 22541 pages on watch Account + (outer the correspondence on the Card of watch of 191122 * lining that expresses Account is outer the 3 pages that express group place to want to search) =595907 second I/O


Below condition of the second join,Optimal inquiry plan is make Card outer watch,Account makes lining express,The index that uses Account to go up,Its I/O frequency can is by the following and formulary estimation:


Outer the 1944 pages on watch Card + (outer the correspondence on the Account of watch of 7896 * lining that expresses Card is outer the 4 pages that express each place to want to search) = 33528 second I/O


Visible,Have satisfying join requirement only,Real optimal plan just can be carried out.


Sum up:


1. Before much watch operation is being carried out actually,Inquiry is optimized implement meeting according to join condition,List a few groups of possible join plan find out the optimal program with systematic the smallest expense from which.The watch with the list that join condition wants to fill a consideration to contain reference, much linage;The choice that inside and outside expresses but by formula:Outer the number that the in matching linage * lining to express every time in the watch searchs is certain,Product is the smallest for optimal program.


2. Examine the method of executive plan- - with Set Showplanon,Open Showplan option,Can see join is ordinal, use why to plant the information of index;Want to watch more detailed news,Need to carry out Dbcc(3604 with Sa part, 310, 302) .

Three, the Where that cannot optimize is clausal


1. Exemple:The row in statement of following SQL condition is built have appropriate index,But executive rate is very slow however:





Select * From Record Where Substring(card_no, 1, 5378 ' of 4)= ' (13 seconds) Select * From Record Where Amount/30%26lt;1000 (11 seconds) Select * From Record Where Convert(char(10) , date, 19991201 ' of 112)= ' (10 seconds)


Analysis:

Any to the row operations in Where clause are to be in what a computation chases to get when SQL moves as a result,Accordingly it must undertake expressing searching,And did not use this index above;If these results are in,can get,Can be optimized by SQL so implement optimize,Use index,Avoid to express a search,Because this rescripts SQL below such:





5378% ' of ' of Select * From Record Where Card_no Like (%26lt;1 second) Select * From Record Where Amount%26lt;1000*30 (%26lt;1 second) ' of 1999/12/01 of ' of Select * From Record Where Date= (%26lt;1 second)


You can discover SQL rises apparently quickly!


2. Exemple:Watch Stuff has 200000 to go,Have on Id_no be not teeming index,Look please below this SQL:





0 ' of ' of Select Count(*) From Stuff Where Id_no In(, 1 ' of ' ) (23 seconds)


Analysis:

The ' In ' in Where condition is equivalent to ' Or ' on logic,So parse implement 0 ' of ' of meeting general In (, 1 ' of ' ) 1 ' of ' of 0 ' Or Id_no= comes to ' of translate into Id_no = carry out.We expect its meeting is searched respectively according to every Or clause,Again result addition,The index that can use Id_no to go up so;But actually (according to Showplan) , it used "OR politic " however,Namely preferential a travel that satisfies every Or clause,In stocking the labour tabulation that faces duration to occupy a library,Build only index to repeat with take out again row,Finally from result of this computation in be being expressed temporarily.Because of this,Actual process did not use the index on Id_no,The effect that and finish time suffers Tempdb database function even.


Practice proves,The linage of the watch is more,The performance of labour tabulation is poorer,When Stuff has 620000 to go,Executive time reachs 220 seconds unexpectedly!Still be inferior to parting Or clause:





0 ' of ' of Select Count(*) From Stuff Where Id_no= 1 ' of ' of Select Count(*) From Stuff Where Id_no=


Get two results,Make addition again economical.Because every used index,Executive time has 3 seconds only,Fall in 620000,Time also has 4 seconds only.Or,With better method,Write a simple memory procedure:





0 ' of ' of Create Proc Count_stuff As Declare @a Int Declare @b Int Declare @c Int Declare @d Char(10) Begin Select @a=count(*) From Stuff Where Id_no= 1 ' of ' of Select @b=count(*) From Stuff Where Id_no= End Select @c=@a+@b Select @d=convert(char(10) , @c) Print @d


Result of direct cipher out,Executive time is Alexandrine the face is euqally fast!


Sum up:


Visible,Alleged optimize namely Where clause used index,Cannot optimize happened to express scanning or additional expense namely.


1. Any operations to the row will bring about a watch to scan,It includes expression of database function, computation to wait a moment,Should move the operation as far as possible when inquiry to equal-sign right.


Clausal regular meeting of 2.in, Or uses labour tabulation,Make index invalidation;If do not produce a large number of repetition to be worth,Can consider to ravel clause;Reference should be contained in the clause that ravel.


3. Should be good at using memory process,It makes SQL becomes more agile and efficient.


From above these example can see,The essence that SQL optimizes is namely below the premise with accurate result,With optimize implement the statement that OK identifying,Take full advantage of indexes,The I/O number that reduces watch scanning,The happening that avoids to express a search as far as possible.Actually the function of SQL is optimized is a complex process,Afore-mentioned these are reflected one kind when just using arrangement,The overall design of the flow control of layer of the resource configuration that considers to still can involve database layer deep, network and operating system layer.