一：在较小的结果集上上操作
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*10
select * from EmployeeOp where VacationHours/10>=10
建立合适的主外键：
select c.EnglishProductCategoryName,p.EnglishProductName from Product as p
inner join ProductCategory as c on c.ProductCategoryKey=p.ProductSubcategoryKey--0.214 alter table ProductCategory
add constraint pk_c_id primary key(ProductCategoryKey) alter table Product
--不用去检查是否符合主外键的要求
with nocheck
add constraint fk_p_id foreign key(ProductSubcategoryKey) references ProductCategory(ProductCategoryKey) select c.EnglishProductCategoryName,p.EnglishProductName from Product as p
inner 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 on
select * 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','<sae>35</sae><school>cdschool</school>')
select *from Student select id,name,sex,sae,school,optiondata from Student update Student set optiondata ='<sae>36</sae>' 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'
),
--做文件组 --包含filestream
filegroup 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 on
select * from AdventureWorks2014.Production.ProductPhoto--io 52
set statistics io off
set statistics io on
select * from Product--7
set 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中的显示：
<asp:Content ID="BodyContent" ContentPlaceHolderID="MainContent" runat="server">
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="显示" />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="ID" HeaderText="ID" Visible="False" />
<asp:BoundField DataField="name" HeaderText="产品名" />
<asp:TemplateField HeaderText="样图">
<ItemTemplate>
<asp:Image ID="Image1" runat="server" ImageUrl='<%# "ImageHandler.ashx?ID="+Eval("ID") %>' />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</asp:Content>
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;
}
后台的调用代码：
/// <summary>
/// ImageHandler 的摘要说明
/// </summary>
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;
}
}
}