【文章內(nèi)容簡(jiǎn)介】 、使用大塊 I/O 的查詢的磁盤讀次數(shù)比預(yù)料的要多。 (5)、由于大量數(shù)據(jù)修改，使得數(shù)據(jù)頁(yè)和索引頁(yè)沒(méi)有充分使用而導(dǎo)致空間的使用超出估算。 (6)、 dbcc 檢查出索引有問(wèn)題。 當(dāng)重建聚簇索引時(shí) ,這張表的所有非聚簇索引將被重建 . b) 索引統(tǒng)計(jì)信息的更新 當(dāng)在一個(gè)包含數(shù)據(jù)的表上創(chuàng)建 索引的時(shí)候， SQL Server 會(huì)創(chuàng)建分布數(shù)據(jù)頁(yè)來(lái)存放有關(guān)索引的兩種統(tǒng)計(jì)信息：分布表和密度表。優(yōu)化器利用這個(gè)頁(yè)來(lái)判斷該索引對(duì)某個(gè)特定查詢是否有用。但這個(gè)統(tǒng)計(jì)信息并不動(dòng)態(tài)地重新計(jì)算。這意味著，當(dāng)表的數(shù)據(jù)改變之后，統(tǒng)計(jì)信息有可能是過(guò)時(shí)的，從而影響優(yōu)化器追求最有工作的目標(biāo)。因此，在下面情況下應(yīng)該運(yùn)行 update statistics 命令： (1)、數(shù)據(jù)行的插入和刪除修改了數(shù)據(jù)的分布。 (2)、對(duì)用 truncate table 刪除數(shù)據(jù)的表上增加數(shù)據(jù)行。 (3)、修改索引列的值 。 第三章 查詢優(yōu)化 主鍵 主鍵用整型會(huì)極大的提高查詢效率，而字符型的比較開銷要比整型的比較開銷大很多，用字符型數(shù)據(jù)作主鍵會(huì)使數(shù)據(jù)插入、更新與查詢的效率降低。數(shù)據(jù)量小的時(shí)候這點(diǎn)降低可能不會(huì)被注意，可是當(dāng)數(shù)據(jù)量大的時(shí)候，小的改進(jìn)也能夠提高系統(tǒng)的響應(yīng)速度。 雖然整型能提高查詢效率，但在具體的方案中考慮到教師工號(hào)的唯一性和我們所做軟件的數(shù)據(jù)量，我們還是犧牲了這一效率，采用了字符型數(shù)據(jù)作主鍵。 通配符 在數(shù)據(jù)庫(kù)管理系統(tǒng)中，查詢是一個(gè)很重要的內(nèi)容。然而，在多數(shù)情況下人們不能準(zhǔn)確知道作為查詢條件的字段內(nèi)容，如：在“師資管理系統(tǒng)”中要查 詢教師內(nèi)容，通過(guò)教師工號(hào)，教師類別號(hào)，教師姓名，教師學(xué)科號(hào)之中的一個(gè)或多個(gè)查詢，而查詢者可能只知道其中的姓或名，這時(shí)，為保證能查到滿足條件的數(shù)據(jù)記錄，只能進(jìn)行模糊查詢。以下是我在“師資管理系統(tǒng)”中為查詢教師的存儲(chǔ)過(guò)程的代碼： CREATE procedure seachTeacher @teacherID varchar(10), @kindID char(2), @name varchar(12), @subjectID varchar(8) as if (@teacherID is not null and @kindID is not null and @name is not null and @subjectID is not null) begin select , from Teacher as a join Kind as b on = join Subject as c on = where =@teacherID and =@kindID and like (39。%39。+ @name + 39。%39。) and =@subjectID order by end if (@teacherID is null and @kindID is not null and @name is not null and @subjectID is not null) begin select , from Teacher as a join Kind as b on = join Subject as c on = where =@kindID and like (39。%39。+ @name + 39。%39。) and =@subjectID order by end if (@teacherID is not null and @kindID is null and @name is not null and @subjectID is not null) begin select , from Teacher as a join Kind as b on = join Subject as c on = where =@teacherID and like (39。%39。+ @name + 39。%39。) and =@subjectID order by end if (@teacherID is null and @kindID is null and @name is not null and @subjectID is not null) begin select , from Teacher as a join Kind as b on = join Subject as c on = where like (39。%39。+ @name + 39。%39。) and =@subjectID order by end if (@teacherID is not null and @kindID is not null and @name is null and @subjectID is not null) begin select , from Teacher as a join Kind as b on = join Subject as c on = where =@teacherID and =@kindID and =@subjectID order by end if (@teacherID is null and @kindID is not null and @name is null and @subjectID is not null) begin select , from Teacher as a join Kind as b on = join Subject as c on = where =@kindID and =@subjectID order by end if (@teacherID is not null and @kindID is null and @name is null and @subjectID is not null) begin select , from Teacher as a join Kind as b on = join Subject as c on = where =@teacherID and =@subjectID order by end if (@teacherID is null and @kindID is null and @name is null and @subjectID is not null) begin select , from Teacher as a join Kind as b on = join Subject as c on = where =@subjectID order by end if (@teacherID is not null and @kindID is not null and @name is not null and @subjectID is null) begin select , from Teacher as a join Kind as b on = join Subject as c on = where =@teacherID and =@kindID and like (39。%39。+ @name + 39。%39。) order by end if (@teacherID is null and @kindID is not null and @name is not null and @subjectID is null) begin select , from Teacher as a join Kind as b on = join Subject as c on = where =@kindID and like (39。%39。+ @name + 39。%39。) order by end if (@teacherID is not null and @kindID is null and @name is not null and @subjectID is null) begin select , from Teacher as a join Kind as b on = join Subject as c on = where =@teacherID and like (39。%39。+ @name + 39。%39。) order by end if (@teacherID is null and @kindID is null and @name is not null and @subjectID is null) begin select , from Teacher as a join Kind as b on = join Subject as c on = where like (39。%39。+ @name + 39。%39。) order by end if (@teacherID is not null and @kindID is not null and @name is null and @subjectID is null) begin select , from Teacher as a join Kind as b on = join Subject as c on = where =@teacherID and =@kindID order by end if (@teacherID is null and @kindID is not null and @name is null and @subjectID is null) begin select , from Teacher as a join Kind as b on = join Subject as c on = where =@kindID order by end if (@teacherID is not null and @kindID is null and @name is null and @subjectID is null) begin select , from Teacher as a join Kind as b on = join Subject as c on = where =@teacherID order by end if (@teacherID is null and @kindID is null and @name is null and @subjectID is null) begin select , from Teacher as a join Kind as b on = join Subject as c on = order by end GO 大家可以從以上代碼中看到有 4 個(gè)參數(shù)就有 16 中情況的可能性，這大大的影響了查詢的效率，在性能上是不可 取的，怎么可以提高效率又可以滿足上面的查詢要求呢，我們這里可以用通配符來(lái)達(dá)到這種效果。在“師資管理系統(tǒng)”中要查詢教師學(xué)科信息，有 6個(gè)參數(shù)，那么就要有 64個(gè)可能性，如果象上面那樣做的話，那么代碼太復(fù)雜了。而我用了通配符就比較簡(jiǎn)單，以下是我寫的代碼： CREATE procedure subjectpro @subjectID varchar(8), @subjectName varchar(20), @subjectKind varchar(10), @isBase char(2), @department varchar(20) as declare @subjectID1 VARCHAR(10), @subjectName1 varchar(22), @subjectKind1 varchar(12), @isBase1 varchar(4), @department1 varchar(22) set @subjectID1 = case when @subjectID is null then 39。%39。 else 39。%39。+@subjectID+39。%39。 end。 set @subjectName1 = case when @subjectName is null then 39。%39。 else 39。%39。+@subjectName+39。%39。 end。 set @subjectKind1= case when @subjectKind is null then 39。%39。 else 39。%39。+@subjectKind+39。%39。 end。 set @isBase1 = case when @isBase is null then 39。%39。 else 39。%39。+@isBase+39。%39。 end。 set @department1 = case when @department is null then 39。%39。 else 39。%39。+@department+39。%39。 end。 select * from Subject where (subjectID like @subjectID1) and (s