澳门新蒲京娱乐


关于SQLSTATE 57017的解决

Server查询前N条记录的常用方法小结

存储过程分页澳门新蒲京娱乐:,sql千万级数据库分页与性能优化分析

在这里在此以前面包车型大巴标题到深入分析,及缓慢解决方案,大家能够精通对千万级数据分页的管理,最器重的一个评估方面正是,现实意义,必要多站在使用者角度拆解分析难点,参考成功案例,进而找到有效的施工方案。

  • @SelectFields +’ from ‘+ @TableName +’ where ‘ +@strWhere+’ order by
    ‘+@OrderField +’ asc ‘ +’,’+@IdField+ ‘ asc’ print(@Sql) end
    –print(@Sql) exec sp_executeSql @Sql end else Begin set
    @nRd=@PageSize* (@CurrentPage-1) print(@nRd) set RowCount @PageSize set
    @Sql=’select ‘ + @SelectFields +’ from ‘+ @TableName +’ where ‘
    +@strWhere+’ and ‘+@IdField + ‘ not in (select top ‘+ cast(@nRd as
    nvarchar(10))+’ ‘+@IdField+’ from ‘+@TableName+’ where ‘+ @strWhere+’
    order by ‘+@OrderField +’ ‘+@OrderSql+’,’+@IdField +’ asc) ‘ + ‘ order
    by ‘+ @OrderField + ‘ ‘ +@OrderSql+’,’+@IdField +’ asc’ exec
    sp_executeSql @Sql –Print(@sql) End end GO

看个实例

cs页面调用代码: 复制代码 代码如下:
public int TotalPage = 0; public int PageCurrent = 1; public int
PageSize = 25; public int RowsCount = 0; string userid, username; public
DataTable dt = new DataTable(卡塔尔; public string path, userwelcome; public
string opt,cid; protected void Page_Load(object sender, EventArgs e) {
if (!IsPostBack) { if (Request.Params[“page”] == null ||
Request.Params[“page”].ToString().Equals(“”)) PageCurrent = 1; else
PageCurrent=int.Parse(Request.Params[“page”].ToString(卡塔尔State of Qatar;
this.getPage(out TotalPage, out RowsCount, PageSize, PageCurrent卡塔尔国; } }
//调用存款和储蓄进度的函数 private void getPage(out int totalPage, out int
rowsCount, int pageSize, int currentPage卡塔尔 { SqlParameter[] parameters
= { new SqlParameter(“@TotalPage”, SqlDbType.Int,4), new
SqlParameter(“@RowsCount”, SqlDbType.Int,4), new
SqlParameter(“@PageSize”, SqlDbType.Int,4), new
SqlParameter(“@CurrentPage”, SqlDbType.Int,4), new
SqlParameter(“@SelectFields”, SqlDbType.NVarChar,700), new
SqlParameter(“@IdField”,SqlDbType.NVarChar,50), new
SqlParameter(“@OrderField”, SqlDbType.NVarChar,200), new
SqlParameter(“@OrderType”, SqlDbType.NVarChar,2), new
SqlParameter(“@TableName”, SqlDbType.NVarChar,300), new
SqlParameter(“@strWhere”, SqlDbType.NVarChar,300), };
parameters[0].Direction = ParameterDirection.Output;
parameters[1].Direction = ParameterDirection.Output;
parameters[2].Value = pageSize; parameters[3].Value = currentPage;
parameters[4].Value =
“a.RLId,a.companyName,a.webSite,a.isRL,a.ordernum,a.isrl,a.userid”;
parameters[5].Value = “a.RLId”; parameters[6].Value = ” a.isrl asc ,
a.orderNum “; parameters[7].Value = “1”; parameters[8].Value =
“qiYeRenling a”; parameters[9].Value = “1=1”;// DataSet ds =
Wm23Abc.DBUtility.DbHelperSQL.RunProcedure(“getRecordByPage”,
parameters, “dt”); dt = ds.Tables[0]; totalPage =
int.Parse(parameters[0].Value.ToString()); rowsCount =
int.Parse(parameters[1].Value.ToString(卡塔尔(قطر‎卡塔尔(قطر‎; } .aspx页面代码: table
trtdb公司名称/b/tdtdb集团网站/b/tdtdb认领状态/b/td/tr %for (int i = 0; i
dt.Rows.Count; i++卡塔尔 { % tr td%= dt.Rows[i][“companyName”].ToString()
%排序值:%= dt.Rows[i][“ordernum”].ToString() %/td td%=
dt.Rows[i][“webSite”].ToString(卡塔尔(قطر‎ % 是不是认领:%=dt.Rows
[i][“userid”].ToString () %/td td%=
dt.Rows[i][澳门新蒲京娱乐 ,”isRL”].ToString().Equals(“0”) ? “a
href=\”javascript:;\”
onclick=\”renLing(event,'”+dt.Rows[i][“RLId”].ToString()+”‘);\”认领该商城/a”
: “font color=\”red\”该铺面已被收养/font”%/td /tr % } % /table /div
div 第 %=PageCurrent % 页 共 %=RowsCount % 条 共 %=TotalPage% 页 % if
(PageCurrent != 1) { % a href=”test.aspx”首 页/a a
href=”test.aspx?page=%=PageCurrent-1%20%”/a % } if (PageCurrent !=
TotalPage卡塔尔国 { % a href=”test.aspx?page=%=PageCurrent+1%20%”/a a
href=”test.aspx?page=%=TotalPage%”末 页/a % } % /div 存款和储蓄进程代码:
复制代码 代码如下: CREATE proc
[dbo].[getRecordByPage] @TotalPage int output,–总页数 @RowsCount
int output,–总条数 @PageSize int,–每页多少多少 @CurrentPage
int,–当前页数 @Select菲尔德s nvarchar(1000卡塔尔国,–select
语句不过不包蕴select @IdField nvarchar(50State of Qatar,–主键列 @OrderFieldnvarchar(50State of Qatar,–排序字段,若是是多个字段,除最终一个字段外,后边都要加排序条件,不分包order
by,最后叁个排序字段不用加排序条件 @OrderType nvarchar(4卡塔尔(قطر‎,–1升序,0降序
@TableName nvarchar(200卡塔尔国,–表名 @strWhere nvarchar(300State of Qatar–条件 As Begin
declare @RecordCount float declare @PageNum int –分页依赖数 Declare
@Compare nvarchar(50卡塔尔国–比较字段分别min大概max Declare @Compare1
nvarchar(2卡塔尔 –大于号“” 或然小于号”“ Declare @OrderSql
nvarchar(10State of Qatar–排序字段 declare @Sql nvarchar(4000卡塔尔 Declare @TemSql
nvarchar(1000State of Qatar Declare @n奥迪Q5d int declare @afterRows int declare
@tempTableName nvarchar(10卡塔尔 if(@OrderType=’1’卡塔尔 Begin set @OrderSql=’
asc’ End Else Begin set @OrderSql= ‘ desc’ End if(isnull(@strWhere,
”卡塔尔”卡塔尔国 Set @strWhere = @strWhere if(@strWhere=”卡塔尔(قطر‎ Set @strWhere=’ 1=1 ‘
Set @TemSql=’Select @RecordCount=Count(1卡塔尔国 from ‘+@TableName +’ where
‘+@strWhere exec sp_executesql @TemSql,N’@RecordCount float
output’,@RecordCount output Set @RowsCount=@RecordCount Set @TotalPage=
ceiling(@RecordCount/@PageSize) if(@CurrentPage@TotalPage) Set
@CurrentPage=@TotalPage if(@CurrentPage1) Set @CurrentPage=1
if(@PageSize1) Set @PageSize=1 print(@RecordCount) if(@CurrentPage=1)
Begin set Rowcount @PageSize set @Sql=’select ‘+ @SelectFields +’ from
‘+ @TableName +’ where ‘ +@strWhere+’ order by ‘+@OrderField +’
‘+@OrderSql +’,’+@IdField +’ asc’ –print(@Sql) exec sp_executeSql @Sql
End else if(@CurrentPage=@TotalPage) begin set
@afterRows=@RowsCount-(@CurrentPage-1)*@PageSize set RowCount
@afterRows if(@OrderType=’1’卡塔尔国 begin set
@OrderField=REPLACE(@OrderField,’asc’,’lai512343975’卡塔尔(قطر‎//这里用变量将asc和desc沟通,哈哈,太神了
set @OrderField=REPLACE(@OrderField,’desc’,’asc’卡塔尔国 set
@OrderField=REPLACE(@OrderField,’lai512343975′,’desc’卡塔尔 set @Sql=’select
‘ + @SelectFields +’ from ‘+ @TableName +’ where ‘ +@strWhere+’ order by
‘+@OrderField +’ desc’+’,’+@IdField +’ asc’ end else begin set
@OrderField=REPLACE(@OrderField,’desc’,’lai512343975’卡塔尔 set
@OrderField=REPLACE(@Order菲尔德,’asc’,’desc’卡塔尔 set
@OrderField=REPLACE(@OrderField,’lai512343975′,’asc’卡塔尔(قطر‎ set @Sql=’select ‘

if (object_id(N’syspr_TablePaginationSearch’,N’P’) is not null)drop
procedure dbo.syspr_TablePaginationSearchgocreate procedure
syspr_TablePaginationSearch(@SelectField nvarchar(512)=’*’ —
Select语句字段,调用的时候不要输入select ,@FormTables nvarchar(512卡塔尔国 —
Form子句,表名,蕴涵结构名,调用的时候绝不输入from ,@WhereFieldnvarchar(512State of Qatar=null — Where语句、决断字段,调用的时候绝不输入where
—————————————————–,@OverPartitionFieldnvarchar(512卡塔尔=null — 分区字段,,@OverOrderField nvarchar(512卡塔尔(قطر‎ —
分页字段,首要排序字段,必须,@GroupField nvarchar(512State of Qatar=null —
分组字段,@HavingField nvarchar(512卡塔尔=null — 分组决断典型,@OrderFieldnvarchar(512卡塔尔(قطر‎=null — 排序字段
—————————————————–,@PageSize int=10
— 页长,@Page int=1 —
页—————————————————–,@ErrorProcedure
nvarchar(128卡塔尔(قطر‎=null output — 错误存储进度名,@ErrorNumber int=0 output —
错误编号,@ErrorMessage nvarchar(512卡塔尔(قطر‎=null output —
错误音讯State of Qatarasbegin–定义第意气风发行记录declare @firstRow
int;–定义最终一条记下declare @lastRow int;–设置第一条记录set
@firstRow=((@Page-1卡塔尔*@PageSize卡塔尔(قطر‎+1;–设置最终一条记录set
@lastRow=@firstRow+@PageSize;–定义查询SQL字段declare @sqlString
nvarchar(512State of Qatar;–定义Select语句,内查询利用declare @selectString
nvarchar(512卡塔尔;–定义Where字段,内查询利用declare @whereString
nvarchar(512卡塔尔国;–定义Form子句,内查询利用declare @formString
nvarchar(512State of Qatar;–定义分组字段,内查询利用declare @groupString
nvarchar(512State of Qatar;–定义Having子句,内查询利用declare @havingString
nvarchar(512卡塔尔;–定义排序字段,外查询利用declare @OrderString
nvarchar(512卡塔尔(قطر‎;–组装Select子句———————————–set
@SelectField=isnull(@SelectField,N’ * ‘);if(@SelectField=”) set
@SelectField=N’ *’ ;–组装select子句到row_number()set @selectString=N’
select ‘+ @SelectField+ ‘, Row_Number(卡塔尔(قطر‎ over( ‘;–组装到分区partition
by 表明式set @OverPartitionField=isnull(@OverPartitionField,N”State of Qatar;if
(@OverPartitionField”卡塔尔(قطر‎ set @selectString= @selectString+ ‘ partition by
‘+ @OverPartitionField;–组装到排序分页order by 表明式set
@OverOrderField=isnull(@OverOrderField,N”卡塔尔;if (@OverOrderField”State of Qatar set
@selectString= @selectString+ ‘ order by ‘+ @OverOrderField+ N’卡塔尔(قطر‎ as
RowNumber
‘;–over———————————————–组装Form子句————————————set
@formString= N’ from ‘+
@FormTables;–组装Where剖断子句——————————set
@Where菲尔德=isnull(@WhereField,N”卡塔尔国;if(@Where菲尔德”卡塔尔(قطر‎ set
@whereString=N’ where ‘+ @WhereField;else set
@whereString=N”;–where判别子句组装达成—————————组装group
by分组子句————————–set
@GroupField=isnull(@GroupField,N”卡塔尔;if (@GroupField”State of Qatarbegin set
@groupString= N’ group by ‘+ @GroupField; –组装having分组判别规范 set
@HavingField= isnull(@HavingField,N”卡塔尔国; if (@HavingField N”卡塔尔国 set
@havingString= N’ having ‘+ @Having菲尔德; else set
@havingString=N”;endelsebegin set @groupString= N”; set
@havingString=N”;end–over———————————————组装order
by向外排水序子句———————–set
@OrderField=isnull(@Order菲尔德,N”卡塔尔国;if (@OrderField”卡塔尔 set @OrderString=
N’ order by ‘+ @OverOrder菲尔德+ N’ , ‘+ @OrderField;else set
@OrderString= N”;–借使@groupString不为空,则向外排水序不起效率if
(@groupStringN”State of Qatar set
@OrderString=N”;–over———————————————组装分页查询语句set
@sqlString= N’select * ‘+ N’from ‘+ N’ ( ‘+ @selectString+ @formString+
@whereString+ @groupString+ @havingString+ N’ 卡塔尔国 as TB ‘+ N’where ‘+ N’
TB.RowNumber=’+cast(@firstRow as nvarchar(10State of Qatar卡塔尔+ N’ and
TB.RowNumber’+cast(@lastRow as nvarchar(10卡塔尔国卡塔尔+
@orderString–over———————————————实践SQL,重临影响行数;如有非常,则抛出,并赋值输出参数begin
try exec (@sqlStringState of Qatar; return @@rowcount;end trybegin catch set
@ErrorProcedure=Error_Procedure(); set @ErrorNumber=error_number();
set @ErrorMessage=error_message(卡塔尔国;end
catch;endgo————————————————————-简单的测验,查询AdventureWorks库的Person.Address表。–以addressid字段顺序分页、并按PostalCode
,StateProvinceID等字段排序。exec dbo.syspr_TablePaginationSearch
@SelectField=’*’,@FormTables=’AdventureWorks.Person.Address’,@whereField=’addressid100′,@OverOrderField=’addressid
asc’,@OrderField=’PostalCode
,StateProvinceID’,@Page=1,@PageSize=10go–轻巧的测量试验,分组总括addressid字段,并分页突显结果集。exec
dbo.syspr_TablePaginationSearch @SelectField=’count(addressid卡塔尔 as
addressCount,city’,@FormTables=’AdventureWorks.Person.Address’,@OverOrderField=’count(addressid卡塔尔(قطر‎desc’,@GroupField=’city’,@Page=1,@PageSize=10go–连接查询并分页exec
dbo.syspr_TablePaginationSearch @SelectField=N’ addr.AddressLine1,
addr.AddressLine2, addr.City, sp.Name as ProvinceName, addr.PostalCode,
addr.rowguid, addr.ModifiedDate’,@FormTables=N’
AdventureWorks.Person.Address as addr inner join
AdventureWorks.Person.StateProvince as sp on
addr.StateProvinceID=sp.StateProvinceID’,@OverOrderField=’addressid
asc’,@Page=1,@PageSize=10go

相关文章

No Comments, Be The First!
近期评论
    功能
    网站地图xml地图