如何寫出高效的ＳＱＬ指令碼《二》-ASP.Net

如何寫出高效的ＳＱＬ指令碼《二》

12. 視圖

盡量少用視圖，它的效率相對比較低。對視圖操作比直接對錶操作慢,可以用stored procedure來代替她。特別的是不要用視圖嵌套,嵌套視圖增加了尋找原始資料的難度。我們看視圖的本質：它是存放在伺服器上的被最佳化好了的已經產生了查詢規劃的SQL。對單個表檢索資料時，不要使用指向多個表的視圖，直接從表檢索或者僅僅包含這個表的視圖上讀，否則增加了不必要的開銷,查詢受到幹擾.為了加快視圖的查詢，MsSQL增加了視圖索引的功能
如果建立的視圖資料量比較大，操作比較頻繁，則推薦使用索引的視圖.

13. GROUP BY Having
一般在GROUP BY 個HAVING字句之前就能剔除多餘的行，所以盡量不要用它們來做剔除行的工作。他們的執行順序應該如下最優：select 的Where字句選擇所有合適的行，Group By用來分組個統計行，Having字句用來剔除多餘的分組。這樣Group By 個Having的開銷小，查詢快.對於大的資料行進行分組和Having十分消耗資源。如果Group BY的目的不包括計算，只是分組，那麼用Distinct更快
14. 預存程序中的參數定義的資料類型盡量和條件中的資料類型一致
1) select fcandidateID from tcandidate where fcredentialstype=’1’
2) select fcandidateID from tcandidate where fnation=1
Fcredentialstype欄位在ｔｃａｎｄｉｄａｔｅ表中是整型，那麼如果第一種方式ｓｑｌ就有一個隱式的函數轉換ｃａｓｔ（‘１＇　ａｓ　ｉｎｔ），花銷時間。
如果一個欄位是ｆｌｏａｄ類型的，比如有一個欄位ｆｓｃｏｒｅ是ｆｌｏａｔ類型，那麼條件陳述式就應該這樣寫（假如：是選出６０分以上的學生），就因該這樣寫：
出fscore＞６０．０. 而不是　fscore>60
這個有一個隱式的轉換。
雖然這些都是小事，也許如果每一步都注意的話，增提效能就會提高的　：）

15. 測試結果如下：
1. 多表選擇Tcandidate 和Tcandidatedetail 表 各有資料 3040000條記錄
select t.fcandidateID,t.fcandidatename,d.fhealth,d.faddress
from tcandidate as t
inner join tcandidatedetail as d
on t.fcandidateid = d.fcandidateid
and t.fcandidatename=’tim1000322’
2. 根據姓名，查詢該使用者的其它的資訊
3. 查詢結果平均用時：19秒
---------------------------------------------------------------------------------------------------------------
1. 　多表選擇Tcandidate 和Tcandidatedetail 表 各有資料 3040000條記錄
select t.fcandidateID,t.fcandidatename,d.fhealth,d.faddress
from tcandidate as t
inner join tcandidatedetail as d
on t.fcandidateid = d.fcandidateid
and t.fCredentialsID=’630103197107037030’
2. 根據考生的身分證ＩＤ，查詢該使用者的其它的資訊
3. 查詢結果平均用時：19秒
1. Tcandidate 和Tcandidatedetail 表，TUserInfo 各有資料 3040000多記錄
select t.fcandidateID,t.fcandidatename,d.fhealth,d.faddress
from tcandidate as t
inner join tcandidatedetail as d
on t.fcandidateid = d.fcandidateid
inner join tuserinfo as u
on u.fLogonCode = t.fcandidatename
where u.flogoncode=’tim1090122’
2. 登陸考生通過TUserInfo表中的flogonCode得自己的詳細資料
3. 平均用時：２８秒
---------------------------------------------------------------------------------------------------------------
1. 通過登陸的flogonCode獲得該使用者的相關資訊，並更新考生的部分資訊
UPDATE tCandidate
SET fCandidateCode = fCandidateCode+’_U’
FROM tCandidate AS T
JOIN TUserInfo AS U
ON T.fCandidateCode = U.fLogonCode
where U.fLogonCode = ’tim1090123’
2. 通過考生登陸網站的ｌｏｇｏｎｃｏｄｅ獲得考生的資訊，並更新部分資訊
3. 平均時間：28秒
---------------------------------------------------------------------------------------------------------------
1. 根據tCandidate表中的fcandidatecode刪除TuserInfo表中的某條記錄
DELETE FROM tUserInfo
From TUserInfo as U
INNER JOIN tCandidate as T
on T.fCandidateCode =U.fLogonCode
WHERE T.fCandidateCode =’tim1096133’
2. 刪除記錄
3. 平均時間20秒
---------------------------------------------------------------------------------------------------------------
1. 更新考生記錄
UPDATE tCandidateDetail
SET faddress = faddress+’ beijing road’
FROM tCandidateDetail AS T
JOIN tCandidate AS D
ON T.fCandidateID = D.fCandidateID
where D.fCandidateName = ’tim1090168’
2. 根據考生的姓名更新考生的詳細資料
3. 平均時間20秒

添加評論 | 固定連結 | 引用 (0) | 寫入日誌
Design Guidelines for Application Performance
http://msdn.microsoft.com/SQL/2000/learn/perf/default.aspx?pull=/library/en-us/dnpag/html/scalenetchapt03.asp

添加評論 | 固定連結 | 引用 (0) | 寫入日誌
how to restore and backup database by using store procedure

if exists(
select * from sysobjects
where and xtype=’p’
)
begin
drop proc pr_backup_db
end

go

/*備份資料庫*/
create proc pr_backup_db
@flag varchar(10) out,
@backup_db_name varchar(128),
@filename varchar(1000) --路徑＋檔案名稱字
as
declare @sql nvarchar(4000),@par nvarchar(1000)
select @par=’@filename varchar(1000)’
select @sql=’BACKUP DATABASE ’+@backup_db_name+’ to disk=@filename with init’
execute sp_executesql @sql,@par,@filename
select @flag=’ok’
go
if exists(
select * from sysobjects
where and xtype=’fn’
)
begin
drop function fn_GetFilePath
end
go

/*建立函數,得到檔案得路徑*/
create function fn_GetFilePath(@filename nvarchar(260))
returns nvarchar(260)
as
begin
declare @file_path nvarchar(260)
declare @filename_reverse nvarchar(260)
select @filename_reverse=reverse(@filename)
select @file_path=substring(@filename,1,len(@filename)+1-charindex(’/’,@filename_reverse))
return @file_path
end
go

if exists(
select * from sysobjects
where and xtype=’p’
)
begin
drop proc pr_restore_db
end
go

create proc pr_restore_db /*恢複資料庫*/
@flag varchar(20) out, /*過程啟動並執行狀態標誌,是輸入參數*/
@restore_db_name nvarchar(128), /*要恢複的資料名字*/
@filename nvarchar(260) /*備份檔案存放的路徑+備份檔案名字*/
as
declare @proc_result tinyint /*返回系統預存程序xp_cmdshell運行結果*/
declare @loop_time smallint /*迴圈次數*/
declare @max_ids smallint /*@tem表的ids列最大數*/
declare @file_bak_path nvarchar(260) /*原資料庫存放路徑*/
declare @flag_file bit /*檔案存放標誌*/
declare @master_path nvarchar(260) /*資料庫master檔案路徑*/
declare @sql nvarchar(4000),@par nvarchar(1000)
declare @sql_sub nvarchar(4000)
declare @sql_cmd nvarchar(4000)
/*
判斷參數@filename檔案格式合法性，以防止使用者輸入類似d: 或者 c:/a/ 等非法檔案名稱
參數@filename裡面必須有’/’並且不以’/’結尾
*/
if right(@filename,1)<>’/’ and charindex(’/’,@filename)<>0
begin
select @sql_cmd=’dir ’+@filename
EXEC @proc_result = master..xp_cmdshell @sql_cmd,no_output
IF (@proc_result<>0) /*系統預存程序xp_cmdshell傳回碼值:0(成功）或1（失敗）*/
begin
select @flag=’not exist’ /*備份檔案不存在*/
return /*退出過程*/
end
/*建立暫存資料表,儲存由備份組內自主資料庫和記錄檔列表組成的結果集*/
create table #tem(
LogicalName nvarchar(128), /*檔案的邏輯名稱*/
PhysicalName nvarchar(260) , /*檔案的實體名稱或作業系統名稱*/
Type char(1), /*資料檔案 (D) 或記錄檔 (L)*/
FileGroupName nvarchar(128), /*包含檔案的檔案組名稱*/
[Size] numeric(20,0), /*當前大小（以位元組為單位）*/
[MaxSize] numeric(20,0) /*允許的最大大小（以位元組為單位）*/
)
/*
建立表變數，表結構與暫存資料表基本一樣
就是多了兩列,
列ids（自增編號列）,
列file_path,存放檔案的路徑
*/
declare @tem table(
ids smallint identity, /*自增編號列*/
LogicalName nvarchar(128),
PhysicalName nvarchar(260),
File_path nvarchar(260),
Type char(1),
FileGroupName nvarchar(128)
)
insert into #tem
execute(’restore filelistonly from disk=’’’+@filename+’’’’)
/*將暫存資料表匯入表變數中,並且計算出相應得路徑*/
insert into @tem(LogicalName,PhysicalName,File_path,Type,FileGroupName)
select LogicalName,PhysicalName,dbo.fn_GetFilePath(PhysicalName),Type,FileGroupName
from #tem
if @@rowcount>0
begin
drop table #tem
end
select @loop_time=1
select @max_ids=max(ids) /*@tem表的ids列最大數*/
from @tem
while @loop_time<=@max_ids
begin
select @file_bak_path=file_path
from @tem where ids=@loop_time
select @sql_cmd=’dir ’+@file_bak_path
EXEC @proc_result = master..xp_cmdshell @sql_cmd,no_output
/*系統預存程序xp_cmdshell傳回碼值:0(成功）或1（失敗）*/
IF (@proc_result<>0)
select @loop_time=@loop_time+1
else
BREAK /*沒有找到備份前資料檔案原有存放路徑，退出迴圈*/
end
select @master_path=’’
if @loop_time>@max_ids
select @flag_file=1 /*備份前資料檔案原有存放路徑存在*/
else
begin
select @flag_file=0 /*備份前資料檔案原有存放路徑不存在*/
select @master_path=dbo.fn_GetFilePath(filename)
from master..sysdatabases where
end
select @sql_sub=’’
/*type=’d’是資料檔案,type=’l’是記錄檔 */
/*@flag_file=1時新的資料庫檔案還是存放在原來路徑，否則存放路徑和master資料庫路徑一樣*/
select @sql_sub=@sql_sub+’move ’’’+LogicalName+’’’ to ’’’
+case type
when ’d’ then case @flag_file
when 1 then File_path
else @master_path
end
when ’l’ then case @flag_file
when 1 then File_path
else @master_path
end
end
+case type
when ’d’ then @restore_db_name+’_’+LogicalName+’_data.mdf’’,’
when ’l’ then @restore_db_name+’_’+LogicalName+’_log.ldf’’,’
end
from @tem
select @sql=’RESTORE DATABASE @db_name FROM DISK=@filename with ’
select @sql=@sql+@sql_sub+’replace’
select @par=’@db_name nvarchar(128),@filename nvarchar(260)’
print @sql
execute sp_executesql @sql,@par,@db_name=@restore_db_name,@filename=@filename
select @flag=’ok’ /*操作成功*/
end
else
begin
SELECT @flag=’file type error’ /*參數@filename輸入格式錯誤*/
end

--備份資料庫test_database
declare @fl varchar(10)
execute pr_backup_db @fl out,’test_database’,’c:/test_database.bak’
select @fl
--恢複資料庫,輸入的參數錯誤
declare @fl varchar(20)
exec pr_restore_db @fl out,’sa’,’c:/’
select @fl

--恢複資料庫,即建立資料庫test_database的複本test_db
declare @fl varchar(20)
exec pr_restore_db @fl out,’test_db’,’c:/test_database.bak’
select @fl

本文轉自
http://q.163.com/dotnet/blog/lujian811/10190113200702522722718/all/#10190113200702522722718