sqlserver 笔记性能优化和用法.docx

1、A.SQL 性能优化1. 查询的模糊匹配尽量避免在一个复杂查询里面使用 LIKE %parm1% 红色标识位置的百分号会导致相关列的索引无法使用，最好不要用.解决办法:其实只需要对该脚本略做改进，查询速度便会提高近百倍。改进方法如下：a、修改前台程序把查询条件的供应商名称一栏由原来的文本输入改为下拉列表，用户模糊输入供应商名称时，直接在前台就帮忙定位到具体的供应商，这样在调用后台程序时，这列就可以直接用等于来关联了。b、直接修改后台根据输入条件，先查出符合条件的供应商，并把相关记录保存在一个临时表里头，然后再用临时表去做复杂关联2. 索引问题在做性能跟踪分析过程中，经常发现有不少后台程序的性能

2、问题是因为缺少合适索引造成的，有些表甚至一个索引都没有。这种情况往往都是因为在设计表时，没去定义索引，而开发初期，由于表记录很少，索引创建与否，可能对性能没啥影响，开发人员因此也未多加重视。然一旦程序发布到生产环境，随着时间的推移，表记录越来越多这时缺少索引，对性能的影响便会越来越大了。这个问题需要数据库设计人员和开发人员共同关注法则：不要在建立的索引的数据列上进行下列操作:避免对索引字段进行计算操作避免在索引字段上使用 not，,=,iexec sp_executesql s,Ncon int output,i int,con output ,i select con-用 execdecla

3、re i int,s nvarchar(1000)set i=5select s=declare con int select con=count(1) from sysobjects where ID+rtrim(i)+ select conexec(s)7. sql 语句访问多台服务器数据/*跨多个服务器访问不同的表的方法一*先创建链接服务器TESTT,*在MSSQL2008 下不能直接修改链接服务器 TESTT 的RPC配置成TURE，可以通过语句修改如下：*/USE masterGOEXEC master.dbo.sp_serveroption server=NTESTT, optna

4、me=Nrpc, optvalue=NtrueGOEXEC master.dbo.sp_serveroption server=NTESTT, optname=Nrpc out, optvalue=NtrueGO-然后通过sql语句访问select * from TESTT.ONLINE_PAYMENT.dbo.TB_USER/*跨多个服务器访问不同的表的方法二*/-启用Ad Hoc Distributed Queries：exec sp_configure show advanced options,1 reconfigure exec sp_configure Ad Hoc Distrib

5、uted Queries,1 reconfigure -访问语句这样写select * from openrowset(sqloledb,10.176.24.111;sa;123,ONLINE_PAYMENT.dbo.TB_USER) a-关闭Ad Hoc Distributed Queries：exec sp_configure Ad Hoc Distributed Queries,0 reconfigure exec sp_configure show advanced options,0 reconfigureC.常用的 sql 文1. 分页 sql1.1select top PageS

6、ize * from UserInfo where Id(select ISNULL(MAX(Id),0) from (select top (PageIndex-1)*PageSize) Id from UserInfo order by Id) as T)order by Id1.2select top PageSize * from (select ROW_NUMBER() over(order by a.Id) as rowid,a.* from UserInfo a) as Twhere T.rowid(PageIndex-1)*PageSize2. 分页存储过程 1create p

7、rocedure sp_paging PageIndex int, -页码PageSize int, -每页数据条数PagesTotal int output, -总页数RecordTotal int output -总记录数asbegin-记录总数select RecordTotal=COUNT(1) from UserInfo a-总页数set PagesTotal=CEILING(RecordTotal*1.0/PageSize)-分页sqlselect top (PageSize) * from (select ROW_NUMBER() over(order by a.Id) as r

8、owid,a.* from UserInfo a) as Twhere T.rowid(PageIndex-1)*PageSizeend-删除存储过程drop procedure sp_paging-执行存储过程declare records intdeclare pages intexec sp_paging 1,10,pages output,records outputselect records as 记录总数, pages as 总页数结果:3. 分页存储过程 2-分页-存储过程create procedure sp_paging2TableName varchar(100), -分

9、页的表名称TableColumn varchar(2000), -要返回的字段名称,* 为全部返回OrderColumn varchar(1000), -排序,必填, 不含order by 字符，如id asc,userid desc，必须指定asc或descWhere varchar(2000),-筛选条件,不含where,如id1 and len(userid)10PageIndex int, -页码PageSize int, -每页数据条数PagesTotal int output, -总页数RecordTotal int output -总记录数asbegindeclare SqlCo

10、unt nvarchar(2000)declare Sql varchar(8000)declare TempTop intset TempTop=(PageIndex-1)*PageSize-判断查询条件是否为空if ISNULL(Where,)+cast(TempTop as varchar)print(Sql)print(SqlCount)exec(Sql)end-删除存储过程drop procedure sp_paging2-执行存储过程declare records intdeclare pages intexec sp_paging2 UserInfo,UserId,Email,A

11、ddress,Birthday asc,“Birthday1980-09-01“,1,3,pages output,records outputselect records as 记录总数, pages as 总页数结果:4. 分页存储过程 3网上搜的,只能查询一个表CREATE PROC sp_paging/*nzperfect no_mIss 高效通用分页存储过程(双向检索 )敬告：适用于单一主键或存在唯一值列的表或视图ps:Sql语句为字节, 调用时请注意传入参数及sql 总长度不要超过指定范围*/TableName VARCHAR(200), -表名FieldList VARCHAR(

12、2000), -显示列名，如果是全部字段则为 *PrimaryKey VARCHAR(100), -单一主键或唯一值键Where VARCHAR(2000), -查询条件不含where字符，如id10 and len(userid)9Order VARCHAR(1000), -排序不含order by字符，如id asc,userid desc，必须指定asc 或desc -注意当SortType=3时生效，记住一定要在最后加上主键，否则会让你比较郁闷SortType INT, -排序规则1:正序asc 2:倒序desc 3:多列排序方法RecorderCount INT, -记录总数0:会返

13、回总记录PageSize INT, -每页输出的记录数PageIndex INT, -当前页数TotalCount INT OUTPUT, -记返回总记录TotalPageCount INT OUTPUT -返回总页数ASSET NOCOUNT ONIF ISNULL(TotalCount,) = SET TotalCount = 0SET Order = RTRIM(LTRIM(Order)SET PrimaryKey = RTRIM(LTRIM(PrimaryKey)SET FieldList = REPLACE(RTRIM(LTRIM(FieldList), ,)WHILE CHARIN

14、DEX(, ,Order) 0 OR CHARINDEX( ,Order) 0BEGINSET Order = REPLACE(Order, ,)SET Order = REPLACE(Order, ,) ENDIF ISNULL(TableName,) = OR ISNULL(FieldList,) = OR ISNULL(PrimaryKey,) = OR SortType 3OR RecorderCount 0BEGINSET new_order1 = ORDER BY + OrderSET new_order2 = Order + , SET new_order2 = REPLACE(

15、REPLACE(new_order2,ASC,ASC,),DESC,DESC,) SET new_order2 = REPLACE(REPLACE(new_order2,ASC,DESC,),DESC,ASC,)SET new_order2 = ORDER BY + SUBSTRING(new_order2,1,LEN(new_order2)-1) IF FieldList 0BEGINIF CHARINDEX(SUBSTRING(,+new_order3,1,CHARINDEX(,new_order3),+FieldList+,)0BEGIN SET FieldList = FieldLis

16、t + , + SUBSTRING(new_order3,1,CHARINDEX(,new_order3) ENDSET new_order3 = SUBSTRING(new_order3,CHARINDEX(,new_order3)+1,LEN(new_order3)ENDSET FieldList = SUBSTRING(FieldList,2,LEN(FieldList) END ENDSET SqlCount = SELECT TotalCount=COUNT(*),TotalPageCount=CEILING(COUNT(*)+0.0)/+ CAST(PageSize AS VARC

17、HAR)+) FROM + TableName + new_where1IF RecorderCount = 0BEGINEXEC SP_EXECUTESQL SqlCount,NTotalCount INT OUTPUT,TotalPageCount INT OUTPUT,TotalCount OUTPUT,TotalPageCount OUTPUTENDELSEBEGINSELECT TotalCount = RecorderCount ENDIF PageIndex CEILING(TotalCount+0.0)/PageSize)BEGINSET PageIndex = CEILING

18、(TotalCount+0.0)/PageSize)ENDIF PageIndex = 1 OR PageIndex = CEILING(TotalCount+0.0)/PageSize)BEGINIF PageIndex = 1 -返回第一页数据BEGINSET Sql = SELECT TOP + STR(PageSize) + + FieldList + FROM + TableName + new_where1 + new_order1ENDIF PageIndex = CEILING(TotalCount+0.0)/PageSize) -返回最后一页数据BEGINSET Sql =

19、SELECT TOP + STR(PageSize) + + FieldList + FROM ( + SELECT TOP + STR(ABS(PageSize*PageIndex-TotalCount-PageSize) + + FieldList + FROM + TableName + new_where1 + new_order2 + ) AS TMP + new_order1 END END ELSEBEGINIF SortType = 1 -仅主键正序排序BEGINIF PageIndex + (SELECT MAX( + PrimaryKey + ) FROM (SELECT

20、TOP + STR(PageSize*(PageIndex-1) + + PrimaryKey + FROM + TableName+ new_where1 + new_order1 + ) AS TMP) + new_order1ENDELSE -反向检索BEGINSET Sql = SELECT TOP + STR(PageSize) + + FieldList + FROM ( + SELECT TOP + STR(PageSize) + + FieldList + FROM + TableName + new_where2 + PrimaryKey + + (SELECT MAX( +

21、 PrimaryKey + ) FROM (SELECT TOP + STR(TotalCount-PageSize*PageIndex) + + PrimaryKey + FROM + TableName+ new_where1 + new_order2 + ) AS TMP) + new_order2 + ) AS TMP + new_order1END END IF SortType = 3 -多列排序，必须包含主键，且放置最后，否则不处理BEGINIF CHARINDEX(, + PrimaryKey + , + Order) = 0 BEGIN PRINT(ERR_02) RETUR

22、N ENDIF PageIndex 0“),new SqlParameter(“PageIndex“,pageindex),new SqlParameter(“PageSize“,pagesize),new SqlParameter(“PagesTotal“,SqlDbType.Int),new SqlParameter(“RecordTotal“,SqlDbType.Int), ;/pa0.Value = “UserInfo“;/pa1.Value = “UserId,Email,Address,Phone,NickName,Sex,Birthday,Status“;/pa2.Value =

23、 “UserId“;/pa3.Value = “Id0“;/pa4.Value = pageindex;/pa5.Value = pagesize;pa6.Direction = ParameterDirection.Output;pa7.Direction = ParameterDirection.Output;DataSet ds = SqlDbHelper.BaseHelper.ExecuteDataSet(“sp_paging2“, CommandType.StoredProcedure, pa);recourdtotal = Convert.ToInt32(pa7.Value);pagetotal = Convert.ToInt32(pa6.Value);return ds;