用的是一个通用的分页查询存储过程,在其他视图、表上查询速度都很正常,查询这个视图就很奇怪。这个视图如果查询所有数据很正常,分页查询速度奇慢,存储过程没有问题。一共2000多条数据。视图如下:
vi_EmailState
SELECT EmailID, EmailTitle, UserID, CreateDateTime, EmailState, SendCount, ContinuousFailed,
(SELECT COUNT(*) AS Expr1
FROM dbo.ContributeList
WHERE (EmailID = dbo.EmailQueue.EmailID)) AS Total,
(SELECT COUNT(*) AS Expr1
FROM dbo.ContributeList AS ContributeList_2
WHERE (EmailID = dbo.EmailQueue.EmailID) AND (Sended = 1)) AS Sended,
(SELECT COUNT(*) AS Expr1
FROM dbo.ContributeList AS ContributeList_2
WHERE (EmailID = dbo.EmailQueue.EmailID) AND (IsRead = 1)) AS IsRead, Email, Email2, Email3
FROM dbo.EmailQueue视图中使用到的表结构如下:CREATE TABLE [dbo].[EmailQueue](
[EmailID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[EmailTitle] [varchar](100) NOT NULL,
[EmailBody] [text] NOT NULL,
[Email] [varchar](36) NULL,
[EmailName] [varchar](36) NULL,
[EmailPassword] [varchar](20) NULL,
[Email2] [varchar](36) NULL,
[EmailName2] [varchar](36) NULL,
[EmailPassword2] [varchar](20) NULL,
[Email3] [varchar](36) NULL,
[EmailName3] [varchar](36) NULL,
[EmailPassword3] [varchar](20) NULL,
[CreateDateTime] [smalldatetime] NULL,
[EmailState] [tinyint] NOT NULL CONSTRAINT [DF_EmailQueue_EmailState] DEFAULT (0),
[SendCount] [tinyint] NOT NULL CONSTRAINT [DF_EmailQueue_SendCount] DEFAULT (0),
[ContinuousFailed] [tinyint] NOT NULL CONSTRAINT [DF_EmailQueue_ContinuousFailed] DEFAULT (0)
)CREATE TABLE [dbo].[ContributeList](
[ContributeListID] [int] IDENTITY(1,1) NOT NULL,
[EmailID] [int] NOT NULL,
[NewspaperID] [int] NOT NULL,
[Employ] [bit] NOT NULL CONSTRAINT [DF_ContributeList_Employ] DEFAULT (0),
[Sended] [bit] NOT NULL CONSTRAINT [DF_ContributeList_Sended] DEFAULT (0),
[IsRead] [bit] NOT NULL CONSTRAINT [DF_ContributeList_IsRead] DEFAULT (0)
)
vi_EmailState
SELECT EmailID, EmailTitle, UserID, CreateDateTime, EmailState, SendCount, ContinuousFailed,
(SELECT COUNT(*) AS Expr1
FROM dbo.ContributeList
WHERE (EmailID = dbo.EmailQueue.EmailID)) AS Total,
(SELECT COUNT(*) AS Expr1
FROM dbo.ContributeList AS ContributeList_2
WHERE (EmailID = dbo.EmailQueue.EmailID) AND (Sended = 1)) AS Sended,
(SELECT COUNT(*) AS Expr1
FROM dbo.ContributeList AS ContributeList_2
WHERE (EmailID = dbo.EmailQueue.EmailID) AND (IsRead = 1)) AS IsRead, Email, Email2, Email3
FROM dbo.EmailQueue视图中使用到的表结构如下:CREATE TABLE [dbo].[EmailQueue](
[EmailID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[EmailTitle] [varchar](100) NOT NULL,
[EmailBody] [text] NOT NULL,
[Email] [varchar](36) NULL,
[EmailName] [varchar](36) NULL,
[EmailPassword] [varchar](20) NULL,
[Email2] [varchar](36) NULL,
[EmailName2] [varchar](36) NULL,
[EmailPassword2] [varchar](20) NULL,
[Email3] [varchar](36) NULL,
[EmailName3] [varchar](36) NULL,
[EmailPassword3] [varchar](20) NULL,
[CreateDateTime] [smalldatetime] NULL,
[EmailState] [tinyint] NOT NULL CONSTRAINT [DF_EmailQueue_EmailState] DEFAULT (0),
[SendCount] [tinyint] NOT NULL CONSTRAINT [DF_EmailQueue_SendCount] DEFAULT (0),
[ContinuousFailed] [tinyint] NOT NULL CONSTRAINT [DF_EmailQueue_ContinuousFailed] DEFAULT (0)
)CREATE TABLE [dbo].[ContributeList](
[ContributeListID] [int] IDENTITY(1,1) NOT NULL,
[EmailID] [int] NOT NULL,
[NewspaperID] [int] NOT NULL,
[Employ] [bit] NOT NULL CONSTRAINT [DF_ContributeList_Employ] DEFAULT (0),
[Sended] [bit] NOT NULL CONSTRAINT [DF_ContributeList_Sended] DEFAULT (0),
[IsRead] [bit] NOT NULL CONSTRAINT [DF_ContributeList_IsRead] DEFAULT (0)
)
解决方案 »
- DevExpress的aspxgridview问题
- godaddy空间与域名怎么用
- jquery +autocomplete 的问题 。。
- 求助 asp.net中datalist问题
- 为GridView动态生成模板列,在模板列中动态生成两个按钮,为按钮绑定服务器事件时,完全无效.
- 求教:使用框架集,导航条传值问题(急用,在线等。解决后马上给分),谢谢
- 请教一个网络常识问题?
- 怎样把页面中html控件中的值取出来?
- 急,DropDownList的索引问题.
- 请问用VS建一个项目时,那个Styles.css样式表怎么样运用到我的ASPX等文件里面。
- datagrid嵌套datalist 只有一个数据表,怎么实现?
- EnableViewStateMAC=true、EnableViewStateMAC=false的ViewState为什么是一样的?
boblaw能不能解释一下为什么查询所有数据速度正常,分页就有问题了?
你的“不要用视图了,直接在存储过程内部完成字段统计功能.”这个怎么做?
CREATE PROCEDURE BN_QueryPayFeeInfo
@Condition VARCHAR(300), --查询条件
@PageIndex INT, --分页所引
@PageSize INT --每一页显示纪录条数AS
DECLARE @IDSelectString VARCHAR(1000) --查询语句
DECLARE @ItemCountSelectString VARCHAR(1000) --统计符合查询条件的纪录条数
SET @IDSelectString = 'SELECT ID FROM BN_PayFeeInfo {0} ORDER BY ID'
SET @ItemCountSelectString = 'SELECT COUNT(*) AS ItemCount FROM BN_PayFeeInfo {0}'
DECLARE @StartRow INT --起始行
DECLARE @StopRow INT --终止行/**************************************************************/
/* 说明:把查询到的纪录ID放到临时表中 */
/**************************************************************/
SET @StartRow = @PageSize * @PageIndex + 1
SET @StopRow = @PageSize * (@PageIndex + 1)
CREATE TABLE #Paging_Table(RowNo INT IDENTITY(1, 1), ID VARCHAR(10) COLLATE Chinese_PRC_CI_AS)
--truncate table #Paging_TableSET RowCount @StopRowSET @IDSelectString = REPLACE(@IDSelectString, '{0}', @Condition)
INSERT #Paging_Table
EXECUTE (@IDSelectString)/**************************************************************/
/* 说明:查询结果列表中所显示的字段 */
/**************************************************************/
Select A.* from BN_PayFeeInfo a
INNER JOIN
(SELECT ID FROM #Paging_Table WHERE RowNo >= @StartRow)AS B
on A.ID = b.IDSET @ItemCountSelectString = REPLACE(@ItemCountSelectString, '{0}', @Condition) --以实际查询条件
EXEC (@ItemCountSelectString)
GO
用这种存储过程分页吧,把红色部分当参数传进来就可以通用了
create proc up_PublicTurnPageWebSite(
@TBName nvarchar(100)='', --表名,如 pinyin
@PageSize int=10, --每页的记录数,默认为 10
@CurPage int=1, --表示当前页 1
@KeyField nvarchar(100)='ID', --关键字段名,默认为 ID,该字段要求是表中的索引 或 无重复和不为空的字段
@KeyAscDesc nvarchar(4)='ASC', --关键字的升、降序,默认为升序 ASC , 降序为 DESC
@Fields nvarchar(500)='*', --所选择的列名,默认为全选
@Condition nvarchar(3000)='', --where 条件,默认为空
@Order nvarchar(200)='' --排序条件,默认为空
) with encryption as
if @TBName = ''
begin
raiserror('请指定表名!',11,1)
return
end
if @PageSize <=0 or @CurPage <0
begin
raiserror('当前页数和每页的记录数都必须大于零!',11,1)
return
end
if @KeyAscDesc = 'DESC'
set @KeyAscDesc = '<'
else
set @KeyAscDesc = '>'
if @Condition <> ''
set @Condition = ' where ' + @Condition
declare @SQL nvarchar(3000)
set @SQL = ''
if @CurPage = 1
set @SQL = @SQL + 'SELECT Top ' + cast(@PageSize as nvarchar(20)) + ' ' + @Fields + ' FROM ' + @TBName + @Condition + ' ' + @Order
else
begin
declare @iTopNum int
set @iTopNum = @PageSize * (@CurPage - 1)
set @SQL = @SQL + 'declare @sLastValue nvarchar(100)' + char(13)
set @SQL = @SQL + 'SELECT Top ' + cast(@iTopNum as nvarchar(20)) + ' @sLastValue=' + @KeyField + ' FROM ' + @TBName + @Condition + ' ' + @Order + char(13)
declare @Condition2 nvarchar(3000)
if @Condition = ''
set @Condition2 = ' where ' + @KeyField + @KeyAscDesc + '@sLastValue '
else
set @Condition2 = ' and ' + @KeyField + @KeyAscDesc + '@sLastValue '
set @SQL = @SQL + 'SELECT Top ' + cast(@PageSize as nvarchar(20)) + ' ' + @Fields + ' FROM ' + @TBName + @Condition + @Condition2 + @Order
end
EXECUTE sp_executesql @SQL
如你所述,ContributeList的記錄有10多w,你的視圖中有多處子查詢是查詢該表,而且都是查詢EmailID或EmailID與其他字段的組合,直接到該視圖使用分頁,雖然沒有看到你分頁的查詢,但是估計會大大增加ContributeList表的查詢次數,如果不建立索引的話,速度必然會大受影響。
至於你所述的查詢全部數據就正常,我認為仍然是不太正常的,不建索引肯定會比建索引慢不少,衹是這種情況下,沒有感覺到太過明顯,效率尚可接受而已。