SqlServer性能优化 Sql语句优化（十四）

SqlServer性能优化 Sql语句优化（十四）

一：在较小的结果集上上操作

1.仅返回需要的列

2.分页获取数据

EF实现分页：

public object getcp(int skiprows,int currentpagerows) { HRUser dbcontext = new HRUser(); var cps = dbcontext.Product.Join(dbcontext.ProductCategory, a => a.ProductSubcategoryKey, ar => ar.ProductCategoryKey, (a, ar) => new { CName = ar.EnglishProductCategoryName, PName = a.EnglishProductName, Color = a.Color, Size = a.Size }); return cps.OrderBy(p=>p.PName).Skip(skiprows).Take(currentpagerows).ToList(); }

上一页：

protected void Button4_Click(object sender, EventArgs e) { TextBox1.Text = (int.Parse(TextBox1.Text.Trim()) + 1).ToString(); DataBinding(); }

下一页：

protected void Button4_Click(object sender, EventArgs e) { TextBox1.Text = (int.Parse(TextBox1.Text.Trim()) + 1).ToString(); DataBinding(); }

绑定：

private void DataBinding() { var cps = p.getcp(int.Parse(TextBox1.Text.Trim())*10,10); GridView1.DataSource = cps; GridView1.DataBind(); }

避免出现左侧计算：

select * from EmployeeOp where VacationHours>=10*10select * from EmployeeOp where VacationHours/10>=10

建立合适的主外键：

select c.EnglishProductCategoryName,p.EnglishProductName from Product as pinner join ProductCategory as c on c.ProductCategoryKey=p.ProductSubcategoryKey--0.214alter table ProductCategoryadd constraint pk_c_id primary key(ProductCategoryKey)alter table Product--不用去检查是否符合主外键的要求with nocheckadd constraint fk_p_id foreign key(ProductSubcategoryKey) references ProductCategory(ProductCategoryKey)select c.EnglishProductCategoryName,p.EnglishProductName from Product as pinner join ProductCategory as c on c.ProductCategoryKey=p.ProductSubcategoryKey--性能好

验证数据存在时使用Exists替换Count():

if ((select count(*) from EmployeeOp where VacationHours=100)>0)print 'hello'if exists(select * from EmployeeOp where VacationHours=100)print 'hello1'

关闭受影响的行数：

set nocount onselect * from Product

添加稀疏列：

create table t1(c1 int identity(1,1),c2 int sparse)declare @count int set @count=0 while @count<50000 begin --定义稀疏列 insert t1 values(null) set @count=@count+1 end --查看表空间 sp_spaceused 't1' --1408 --删除稀疏列 alter table t1 alter column c2 drop sparse sp_spaceused 't1' --1712 --添加稀疏列 alter table t1 alter column c2 add sparse sp_spaceused 't1' --1712 dbcc shrinkdatabase('HRDB',1) --列集 create table Student(id int,name varchar(500),sex varchar(500),sae int sparse, school varchar(500) sparse,optiondata xml column_set for all_sparse_columns) insert Student values (1,'caojian','man','35cdschool') select *from Student select id,name,sex,sae,school,optiondata from Student update Student set optiondata ='36' update Student set school='sunliyuan' create table Sales(id int identity(1,1),amount int) alter table Sales add constraint ck_sales_amount check(amount>0) create table Sales1(id int identity(1,1),amount int) --创建规则 很多表都可以使用 create rule amountrule as @amount>0 --通过系统定义的方式 绑定 exec sp_bindrule amountrule, 'Sales1.amount' --插入数据的时候就报错 insert Sales1 values(0)

提高文件访问性能文件流：

打开SqlServer 配置管理工具：

找到如下的目录打开：

配置访问级别：

--配置访问级别 数据库级别 sp_configure 'filestream access level',2 reconfigure select * from AdventureWorks2014.Production.Product select * from AdventureWorks2014.Production.ProductPhoto --链接表 select * from AdventureWorks2014.Production.ProductProductPhoto --存在文件系统中 --1.创建数据库 drop database HRSales create database HRSales on primary ( name='HRSales_data', filename='f:\HRSales_Data.mdf' ), --做文件组 --包含filestreamfilegroup filestreamfilegroup contains filestream(name='HRSaels_Blob',filename='f:\HRSalesblob') use HRSales go create table Product(ID uniqueidentifier RowGUIDCol unique not null, name varchar(500), image varbinary(max) filestream ) --插入记录 insert into Product select NEWID(),p.Name,pp.LargePhoto from AdventureWorks2014.Production.Product as p inner join AdventureWorks2014.Production.ProductProductPhoto as ppp on p.ProductID=ppp.ProductID inner join AdventureWorks2014.Production.ProductPhoto as pp on ppp.ProductPhotoID=pp.ProductPhotoID select * from Product

查看IO：

set statistics io onselect * from AdventureWorks2014.Production.ProductPhoto--io 52set statistics io off

set statistics io onselect * from Product--7set statistics io off

创建数据库备份设备：

--创建备份设备sp_addumpdevice 'disk','hrsalesbak','d:\hrsalesbak.bak'--备份backup database HRSales to hrsalesbak with name='HRSales Full',format

--恢复restore database HRSales from hrsalesbak with file=1,recovery

文件进行了恢复：

在.Net中的显示：

Model层：

SalesDbcontext dbcontext = new SalesDbcontext(); public object GetAllProduct() { var allproducts = dbcontext.Product; return allproducts.ToList(); } public byte[] GetImageByProductID(Guid id) { var product = dbcontext.Product.Where(p => p.ID == id).FirstOrDefault(); return product.image; }

后台的调用代码：

///

/// ImageHandler 的摘要说明 ///

public class ImageHandler : IHttpHandler { Product p = new Product(); MemoryStream memorystream = new MemoryStream(); public void ProcessRequest(HttpContext context) { var id = Guid.Parse(context.Request.QueryString["ID"]); var image = p.GetImageByProductID(id); memorystream.Write(image, 0, image.Length); context.Response.Buffer = true; context.Response.BinaryWrite(image); memorystream.Dispose(); } public bool IsReusable { get { return false; } } }

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。