统计功能的存储过程帮忙优化下

[wcq1987wang]

两个表，一个company（下面用C代替）表存放企业的基本信息，结构如下
CREATE TABLE [dbo].[companyInfo] (
[C_id] [int] NOT NULL ,
[C_name] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[C_code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
.......此处省略统计中无用字段
) ON [PRIMARY]
另一个表CompanyTogather（下面用T代替） ，存储企业下不同专业类别的建造师人数信息，有10个专业，也就是每个企业在T表中对应10行数据。
CREATE TABLE [dbo].[CompanyTogather] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[G_c_id] [int] NOT NULL ,
[G_noReg] [int] NULL ,
[G_haveReg] [int] NULL ,
[G_waitSignUp] [int] NULL ,
[G_haveSignUp] [int] NULL ,
[G_haveTrain] [int] NULL ,
[G_toCard] [int] NULL ,
[G_reRegister] [int] NULL ,
[G_specialize] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
表C插入数据时通过触发器向T表插入数据，执行统计的存储过程使用的sql如下

 SET @strSql ='select top '+str(@pageSize)+' dbo.CompanyTogather.G_c_id,  
SUM(dbo.CompanyTogather.G_noReg) AS G_noReg,  
SUM(dbo.CompanyTogather.G_haveReg) AS G_haveReg,  
SUM(dbo.CompanyTogather.G_waitSignUp) AS G_waitSignUp,  
SUM(dbo.CompanyTogather.G_haveSignUp) AS G_haveSignUp,  
SUM(dbo.CompanyTogather.G_haveTrain) AS G_haveTrain,  
SUM(dbo.CompanyTogather.G_toCard) AS G_toCard,  
SUM(dbo.CompanyTogather.G_reRegister) AS G_reRegister,  
dbo.companyInfo.C_name, dbo.companyInfo.C_id,  
dbo.V_ComTotalPeople.allCount 
 from dbo.companyInfo LEFT OUTER JOIN dbo.V_ComTotalPeople 
ON  dbo.companyInfo.C_id = dbo.V_ComTotalPeople.C_id 
LEFT OUTER JOIN dbo.CompanyTogather ON  dbo.companyInfo.C_id = dbo.CompanyTogather.G_c_id  where
 companyInfo.C_id>(select max(C_id)
   from (select top '+str((@currPage-1)*@pageSize)+' dbo.CompanyTogather.G_c_id,  
SUM(dbo.CompanyTogather.G_noReg) AS G_noReg,
SUM(dbo.CompanyTogather.G_haveReg) AS G_haveReg,  
SUM(dbo.CompanyTogather.G_waitSignUp) AS G_waitSignUp,  
SUM(dbo.CompanyTogather.G_haveSignUp) AS G_haveSignUp,  
SUM(dbo.CompanyTogather.G_haveTrain) AS G_haveTrain,  
SUM(dbo.CompanyTogather.G_toCard) AS G_toCard,  
SUM(dbo.CompanyTogather.G_reRegister) AS G_reRegister,  
dbo.companyInfo.C_name, dbo.companyInfo.C_id,  
dbo.V_ComTotalPeople.allCount 
 from dbo.companyInfo LEFT OUTER JOIN dbo.V_ComTotalPeople 
ON  dbo.companyInfo.C_id = dbo.V_ComTotalPeople.C_id 
LEFT OUTER JOIN dbo.CompanyTogather ON  dbo.companyInfo.C_id = dbo.CompanyTogather.G_c_id 
  where '+@strWhere+@groupby+' order by companyInfo.C_id ) as T ) and ('+@strWhere+')'+@groupby+' 
order by companyInfo.C_id'

为题是查询第二页就会超时，请高人帮忙看下，如何优化

[Mr_Nice]

[Quote=引用楼主 wcq1987wang 的回复:]
两个表，一个company（下面用C代替）表存放企业的基本信息，结构如下
CREATE TABLE [dbo].[companyInfo] (
[C_id] [int] NOT NULL ,
[C_name] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[C_code] [varchar] (50) COLLATE Chinese_PR……
[/Quote]

根据LZ的情况，如果是纯统计，有很多聚合的话。使用索引视图应该会带来比较好的效率。

[wcq1987wang]

具体点怎么写？比较急

[幸运的意外]

相关表的具体结构给出来吧。不然不太好优化。

[wcq1987wang]

这是V_ComTotalPeople视图的代码

SELECT COUNT(dbo.UserInfo.I_name) AS allCount, dbo.companyInfo.C_name, 
      dbo.companyInfo.C_id
FROM dbo.companyInfo INNER JOIN
      dbo.UserInfo ON dbo.companyInfo.C_id = dbo.UserInfo.I_company
GROUP BY dbo.companyInfo.C_name, dbo.companyInfo.C_id

没别的了C表其他字段都没用上

[Mr_Nice]

[Quote=引用 2 楼 wcq1987wang 的回复:]

具体点怎么写？比较急
[/Quote]

--创建视图
--视图上创建索引

CREATE VIEW XXX
as
select  dbo.CompanyTogather.G_c_id,  
		SUM(dbo.CompanyTogather.G_noReg) AS G_noReg,  
		SUM(dbo.CompanyTogather.G_haveReg) AS G_haveReg,  
		SUM(dbo.CompanyTogather.G_waitSignUp) AS G_waitSignUp,  
		SUM(dbo.CompanyTogather.G_haveSignUp) AS G_haveSignUp,  
		SUM(dbo.CompanyTogather.G_haveTrain) AS G_haveTrain,  
		SUM(dbo.CompanyTogather.G_toCard) AS G_toCard,  
		SUM(dbo.CompanyTogather.G_reRegister) AS G_reRegister,  
		dbo.companyInfo.C_name, dbo.companyInfo.C_id,  
		dbo.V_ComTotalPeople.allCount 
from dbo.companyInfo 
LEFT OUTER JOIN dbo.V_ComTotalPeople ON  dbo.companyInfo.C_id = dbo.V_ComTotalPeople.C_id 
LEFT OUTER JOIN dbo.CompanyTogather ON  dbo.companyInfo.C_id = dbo.CompanyTogather.G_c_id



--其中V_ComTotalPeople处需要替换掉，因为索引视图中不能引用视图
--如果不用索引视图的方法，LZ重点监测一下原 where语句后面的部分。
--看起来好像不是很合理。
--具体还需要表结构等信息

[wcq1987wang]

C表的结构 除了 id和name外 其他没什么用

CREATE TABLE [dbo].[companyInfo] (
	[C_id] [int] NOT NULL ,
	[C_name] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[C_code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[C_address] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[C_conName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[C_conTel] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[C_regAdr] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[C_represent] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[C_permitNum] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[C_busiType] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[C_city] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[C_remark] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[C_Telephone] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[C_QQ] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,
	[C_Email] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[C_pwd] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]

[wcq1987wang]

哪位大侠再帮忙看看啊