当前位置:操作系统 > Unix/Linux >>

初尝数据库优化

初尝数据库优化
 
        1.建一个数据库testDB:
[sql] 
create database testDB  
 
        2.建一张测试表T-Test:
[sql] 
use testDB  
create table T_Test (numberTest bigint,dateTest datetime,str1Test varchar(10),str2Test varchar(10),str3Test varchar(10),)  
go  
 
        3.往里添加20万条数据:
[html] 
declare @a bigint  
set @a =1  
while @a <200000  
    begin  
        insert T_Test values(@a,getdate(),'廊坊','信息技术','提高班')  
        set @a=@a+1  
    end  
go  
 
        4.测试:
    查询耗时我用的办法时,查询之前先声明一个时间,查询之后再声明一个时间,然后获得时间差。 
    datediff(millisecond,开始时间,结束时间)  --millisecond 毫秒
 
        测试一:查看T_Test表只查询一个列耗时情况
[sql] 
declare @start datetime,@end datetime  
set @start=getdate()  
select numberTest from T_Test  
set @end=getdate()  
select datediff(millisecond,@start,@end)  
go  
 
    --查询numberTest耗时为:3470毫秒 
    --查询dateTest列耗时:3616毫秒 
 
        测试二:查看peoTest表查询所有列耗时情况
[sql] 
declare @start datetime,@end datetime  
set @start=getdate()  
select * from T_Test  
set @end=getdate()  
select datediff(millisecond,@start,@end)  
go  
 
        --查询所有列耗时为:4723毫秒
 
        测试三:利用分页存储过程(存储过程见文章结尾处),查看某一页单列及所有列耗时情况
[html] 
declare @start datetime,@end datetime  
set @start=getdate()   
exec selectbypage 'T_Test','*','numberTest',20,10,0,0,''   
set @end=getdate()   
select datediff(millisecond,@start,@end)  
go  
 
        --耗时:240毫秒
 
        从以上三个测试例子,可以得出以下结论:
         1、尽量少使用 * 号,应只查询需要的字段,能减少不必要的消耗。 
         2、多使用分页,单页数据量较少,也可以提高查询效率。
 
        一个分页存储过程
[html] 
create PROCEDURE SelectByPage  
(  
@tblName   varchar(255),       -- 表名  
@strGetFields varchar(1000) = '*',  -- 需要返回的列  
@fldName varchar(255)='',      -- 排序的字段名  
@PageSize   int = 40,          -- 页尺寸  
@PageIndex  int = 1,           -- 页码  
@doCount  bit = 0,   -- 返回记录总数, 非 0 值则返回  
@OrderType bit = 0,  -- 设置排序类型, 非 0 值则降序  
@strWhere  varchar(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传递过来的不是0,就执行总数统计。以下的所有代码都是@doCount为0的情况  
else  
begin  
if @OrderType != 0  
begin  
    set @strTmp = '<(select min'  
set @strOrder = ' order by ' + @fldName +' desc'  
--如果@OrderType不是0,就执行降序,这句很重要!  
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)  
 
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,