从Microsoft SQL Server获取/绑定数据集的时间缩短 [英] Improving time when get/bind a dataset from Microsoft SQL server

问题描述

大家好.在Webform中使用gridview控件显示dataset时，我一直在处理一个问题.

基本上，这花了太长时间，我正在使用存储过程从数据库中获取结果集.当使用SQL Server Management Studio在数据库中直接执行结果集时，存储过程需要1到3秒的时间才能返回结果集，但是如果我只是在调用数据适配器的bind方法之前以及在从数据库获取响应之后才测量时间，数据库服务器大约需要2分钟.

有谁知道这是否与我的Web服务器和数据库服务器之间的通信有关，或者与视图状态有关或其他什么……任何想法?

:confused:

Hi everyone. I''ve been dealing with a problem when displaying a dataset using a gridview control within a webform.

Basically it is taking too long, I''m using a stored procedure to get my result set from the database. The stored procedure takes 1 to 3 secs to return the result set when excecuting it directly in the database using SQL Server Management Studio, but if I measure the time just before calling the Data Adapter''s bind method and after getting the response from the database server is taking around 2 minutes.

Does anyone know if this has something to do with the communication between my web server and the database server, or maybe something related to the viewstate or something......Any ideas??

:confused:

推荐答案

SQL Server返回数据集需要三秒钟之久.您拥有的记录越多，所需的时间就越长，这也意味着绑定.

Three seconds for SQL Server to return a dataset is a LONG time. The more records you have, the longer everything is going to take, and that means binding as well.

好，我的存储过程看起来像这样

ok my store procedure looks like this

DECLARE @StartDate datetime,@EndDate datetime,@RTSelection int,@LeadSource nvarchar(255),@SubLeadSource nvarchar(255), @employeeid int, @AffinityGroup nvarchar(255)


SET @StartDate=''2009-01-01''
SET @EndDate=''2009-12-31''
SET @RTSelection=''900''
SET @LeadSource=''%''
SET @SubLeadSource=''''
SET @employeeid=''9904''
SET @AffinityGroup=''-2''


SELECT 
RTRIM(E.LastName) + '', '' + SUBSTRING (E.FirstName, 1 , 1) + ''.'' AS "Employee name",
SUM(Case A.ActivityTypeCode When ''1'' Then 1 Else 0 End) as Appels, 
SUM(Case WHEN A.ActivityTypeCode = ''1'' AND A.ActivityResult = ''1''  Then 1 Else 0 End) as "Contacts",
SUM(Case A.ActivityTypeCode When ''2'' Then 1 Else 0 End)  as r_v, 
SUM(Case WHEN A.ActivityTypeCode = ''2'' AND (A.ActivityResult = ''1'' or  A.ActivityResult = ''2''  OR A.ActivityResult = ''3'' OR A.ActivityResult = ''9'') Then 1 Else 0 End) as Pres,
SUM(Case  When A.ActivityResult = ''3''  THEN  (case when ISNULL(L.SProductVol,0) > 0  THEN 1 ELSE 0 END +
case when  ISNULL(L.AEProductVol,0) > 0 THEN 1 ELSE 0 END + case when  ISNULL(L.FFProductVol,0) > 0  THEN 1 ELSE 0 END ) Else 0 END) as Contracts,
SUM(Case WHEN A.ActivityResult = ''3'' Then 1 Else 0 End) as Ventes,
SUM(Case WHEN A.ActivityResult = ''3'' THEN ISNULL(L.SProductVol,0) ELSE 0 END) as TotalSProduct,
SUM(Case WHEN A.ActivityResult = ''3'' THEN ISNULL(L.AEProductVol,0) ELSE 0 END) as TotalAEProduct,
SUM(Case WHEN (A.ActivityResult = ''3'' AND ISNULL(L.LeadSource,0)!=7) THEN ISNULL(L.FFProductVol,0) ELSE 0 END) as TotalFFProduct,
SUM(Case WHEN A.ActivityResult = ''3'' THEN ISNULL(L.SProductVol,0) + 

		ISNULL(L.AEProductVol,0) + ISNULL(L.FFProductVol,0) ELSE 0 END)  as NetTotal,	
(select SUM(Case WHEN L1.ActivityTypeCode = ''2'' AND A1.ActivityTypeCode = ''0'' AND
	DATEADD(day,-7,L1.ScheduledStart)  Between @StartDate And DATEADD(day,1,@EndDate) THEN 1 ELSE 0 END)from lead l1
inner join activitybase a1 on a1.objectid = l1.guid RIGHT JOIN [Employees]  E1 ON L1.AssignedTo = E1.Guid where e.guid = E1.Guid GROUP BY E1.Guid)as RVSP,

SUM(Case When (A.ActivityResult = ''3''  AND ISNULL(L.FFProductVol,0) > 0 AND ISNULL(L.LeadSource,0)!=7) 
							THEN 1 ELSE 0 END) as FFContracts,
SUM(CASE WHEN A.ActivityResult = ''3'' THEN 
		CASE L.SCelebris WHEN 1 THEN 1 ELSE 0 END +
		   CASE L.AECelebris WHEN 1 THEN 1 ELSE 0 END +		 		
			  CASE L.FFCelebris WHEN 1 THEN 1 ELSE 0 END ELSE 0 END)  as CelebrisContracts,
SUM(CASE WHEN A.ActivityResult =  ''-1'' THEN 1 ELSE 0 END ) as LeadCounter,
SUM(Case When (A.ActivityResult = ''3''  AND ISNULL(L.SProductVol,0) > 0) 
							THEN 1 ELSE 0 END) as SContracts,
SUM(Case When (A.ActivityResult = ''3''  AND ISNULL(L.AEProductVol,0) > 0) 
							THEN 1 ELSE 0 END) as AEContracts,
MIN(CASE WHEN E.RoleID = 3 THEN E.ObjAppels END) as "ObjAppels",
MIN(CASE WHEN E.RoleID = 3 THEN E.ObjContacts END) as "ObjContacts", 
MIN(CASE WHEN E.RoleID = 3 THEN E.Objr_v END) as Objr_v, MIN(E.ObjPres) as ObjPres, 
MIN(CASE WHEN E.RoleID = 3 THEN E.ObjContracts END) as ObjContracts, MIN(E.ObjVentes) as ObjVente,
MIN(CASE WHEN E.RoleID = 3 THEN E.ObjTotalSProduct END) as ObjTotalSProduct,
MIN(CASE WHEN E.RoleID = 3 THEN E.ObjTotalAEProduct END) as ObjTotalAEProduct, 
MIN(CASE WHEN E.RoleID = 3 THEN E.ObjTotalFFProduct END) as ObjTotalFFProduct,
MIN(CASE WHEN E.RoleID = 3 THEN E.ObjNetTotal END) as ObjNetTotal,
E.Guid AS "EmployeeGuid"
FROM Lead L  
INNER JOIN [ActivityBase] A ON L.GUID = A.ObjectID
RIGHT JOIN [Employees] E ON L.AssignedTo = E.Guid
INNER JOIN ([Territories] T
INNER JOIN ([Region] R
INNER JOIN  regionACL ra ON ((ra.RegionId = R.RegionId) AND (ra.employeeid = @employeeid)))
ON  T.RegionID = R.RegionID)
ON  L.TerritoryID = T.TerritoryID 

Where  A.Createdon  Between @StartDate And DATEADD(day,1,@EndDate)	
		 AND ( (@RTSelection = 900) OR (T.RegionID = (@RTSelection - 900 )))  AND ( @LeadSource=''%'' or L.LeadSource LIKE @LeadSource) AND ( @SubLeadSource='''' or @SubLeadSource=''%'' or L.SubLeadSource Like @SubLeadSource)
GROUP BY E.Guid, E.LastName, E.FirstName
ORDER BY E.LastName, E.FirstName

在网页中，即时消息仅调用这样的存储过程

In the web page im only calling the store procedure like this

EXEC Reports_GetInitialLeadsByTerritory ''1/1/2009'',''12/31/2009'',''900'',''%'','''',''104'',''-2''

并即时将结果集绑定到网格视图

就是这样

我希望这可以帮助您帮助我:sigh:

and im binding the result set to a grid view

and thats it

I hope this can help u to help me :sigh:

请解释一下您对该SQL语句的处理方式.对于一个sp来说，这是很多聚合和case语句.获取原始数据并将逻辑移至应用程序可能会更好.如果您使用的表很大，那么它变慢也就不足为奇了.还有其他因素(网络连接，服务器硬件等).

Please explain what you are doing with that SQL statement. That is a lot of aggregate and case statements for one sp. It might be better to get the raw data and move the logic out to the application. If the tables you are working with are very large it isn''t surprising that it would be slow. There are other factors as well (network connections, server hardware, etc).

这篇关于从Microsoft SQL Server获取/绑定数据集的时间缩短的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！