Mssql 碰见的一些问题

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[Module_Common_PagerNew]
    @tableName   varchar(500),          -- 表名
    @tableFields varchar(5000) = '*',  -- 字段名(全部字段为*)
    @sqlWhere    varchar(5000) = NULL, -- 条件语句(不加 where，可带 group by 分组条件，分组条件需要带 group by 关键字)
    @orderFields varchar(5000),        -- 排序字段(必须，支持多字段，不加 order by)
    @pageSize    int,                  -- 页大小(每页多少条记录)
    @pageIndex   int = 1 ,             -- 指定当前为第几页
    @totalPage   int output,           -- 返回总页数
    @totalRecord int output
AS
BEGIN
    Begin Tran -- 开始事务

    Declare @sql nvarchar(4000);
--    Declare @totalRecord int;

    -- 计算总记录数
    if (@sqlWhere = '' or @sqlWhere = NULL)
        set @sql = 'select @totalRecord = count(*) from ' + @tableName
    else
        BEGIN
            if(CHARINDEX('group by', LOWER(@sqlWhere)) > 0)
                set @sql = 'select @totalRecord = count(*) from (select ' + @tableFields + ' from ' + @tableName + ' where ' + @sqlWhere + ') as Tab_GroupTable'
            else
                set @sql = 'select @totalRecord = count(*) from ' + @tableName + ' where ' + @sqlWhere
        END

    --print @Sql

    EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT

    -- 计算总页数
    select @totalPage=CEILING((@totalRecord+0.0)/@pageSize)

    -- 处理页数超出范围情况
    if @pageIndex<=0
        Set @pageIndex = 1
    if @pageIndex>@totalPage
        Set @pageIndex = @totalPage

    -- 处理开始点和结束点
    Declare @startRecord int
    Declare @endRecord int
    set @startRecord = (@pageIndex-1)*@pageSize + 1
    set @endRecord = @startRecord + @pageSize - 1

    -- 合成sql语句
    if (@sqlWhere = '' or @sqlWhere = NULL)
        set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @orderFields + ') as rowId,' + @tableFields + ' from ' + @tableName
    else
        set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @orderFields + ') as rowId,' + @tableFields + ' from ' + @tableName + ' where ' + @sqlWhere

    set @Sql = @Sql + ') as Tab_TotalTable where rowId between ' + Convert(varchar(50),@startRecord) + ' and ' +  Convert(varchar(50),@endRecord)

    print @Sql

    Exec(@Sql)

    if @@Error <> 0
        BEGIN
            RollBack Tran
            Return -1
        END
    else
        BEGIN
            Commit Tran
            Return @totalRecord --- 返回记录总数
        END
END

还可以提供另外一个存储分页方法，这执行的方式各有所不同

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER proc [dbo].[SqlDataPaging]
@tbName varchar(255),        --表名
@tbFields varchar(1000),      --返回字段
@OrderField varchar(255),  --排序的字段名
@PageSize int,                --页尺寸
@PageIndex int,                --页码
@OrderType bit,                --排序类型,1是升序,0是降序
@strWhere varchar(1500),    --查询条件
@Total int output            --返回总记录数
as
declare @strSql varchar(5000)    --主语句
declare @strOrder varchar(200)    --排序
declare @strSqlCount nvarchar(500)--查询记录总数主语句
--------------排序-----------------1是降序,0未升序
if @OrderType !=0
begin
set @strOrder='order by ' + @OrderField + ' asc '
end
else
begin
set @strOrder='order by '+ @OrderField + ' desc '
end
--------------总记录数---------------
if @strWhere !=''
begin
set @strSqlCount='Select @TotalCout=count(*) from  ' + @tbName + ' where '+ @strWhere
end
else
begin
set @strSqlCount='Select @TotalCout=count(*) from  ' + @tbName
end
--------------开始分页------------
if @PageIndex <= 0
begin
  set @PageIndex = 1
end
if @strWhere !=''
begin
set @strSql='Select * from (Select  row_number() over('+@strOrder+') rowId,'+ @tbFields
+' from ' + @tbName + ' where ' + @strWhere+' ) tb where tb.rowId >'+str((@PageIndex-1)*@PageSize)
+' and tb.rowId <= ' +str(@PageIndex*@PageSize)
end
else
begin
set @strSql='Select * from (Select  row_number() over('+@strOrder+') rowId,'+ @tbFields
+' from ' + @tbName +' ' +' ) tb where tb.rowId > '+str((@PageIndex-1)*@PageSize)
+' and tb.rowId <= ' +str(@PageIndex*@PageSize)
end
exec sp_executesql @strSqlCount,N'@TotalCout int output',@Total output
exec(@strSql)

　对上面所需要参数的一个补充

   SqlParameter[] parm = new SqlParameter[]{new SqlParameter ("@tbName","tezt"),new SqlParameter("@tbFields","*"),new SqlParameter("@OrderField","time"),
                new  SqlParameter("@PageSize",200),new SqlParameter("@PageIndex",100),new SqlParameter("@OrderType","0"),
                new SqlParameter("@strWhere",""),new SqlParameter("@Total",SqlDbType.Int,4)};

　　

　

已经碰见过好几次大存储数据量问题，优化数据存储，分页等问题。　

1：调整数据结构的设计，对于经常访问的数据库表是否需要建立索引等。

2：调整应用程序结构设计，分清楚不同的应用程序，所对的数据库资源访问格式各有所不同。

3：调整数据库SQL语句。应用程序的执行最终将归结为数据库中的SQL语句执行

4：调整服务器内存分配，调整硬盘I/O的读写

上述问题只是针对数据库的基本操作。