填充数据适配器的方法运行缓慢。 [英] Fill method of data adapter operating slow.

问题描述

我正在维护旧的3层拱门中的应用程序。



创建一个通用方法来执行存储过程并返回数据集并绑定gridview < br $>

I am mantaining an application which is in old 3 tier arch.

Have a common method created that executes the store proc and returns dataset and binds the gridview

public DataSet Execute_StoreProc_DataSet(string psStoreProcName)
		{
			DataSet dsResult= new DataSet(); 
			UtilParams objParams;
			SqlConnection objConnection=null;
			SqlParameter objSqlParams=null;
			SqlCommand objCommand=null;
			SqlDataAdapter objSqlDataAdapter=null;

			try
			{
				objConnection=getConnection();
				objCommand= new SqlCommand(psStoreProcName,objConnection);  
				objCommand.CommandType=CommandType.StoredProcedure;
                objCommand.CommandTimeout = 0;
				
				IDictionaryEnumerator en = mhtParamCollection.GetEnumerator();

				while (en.MoveNext())
				{
					objParams=(UtilParams)en.Value; 
					objSqlParams = new SqlParameter();
					objSqlParams.ParameterName=objParams.ParamName;
					objSqlParams.SqlDbType =(SqlDbType)objParams.ParamType;
					objSqlParams.Direction=objParams.Direction;
					if (objParams.Direction==ParameterDirection.Output)
					{
						objSqlParams.Size=objParams.ParamSize;   
					
					}
		
					objSqlParams.Value= objParams.ParamValue;
					objCommand.Parameters.Add(objSqlParams);  
				}
				objSqlDataAdapter= new SqlDataAdapter(objCommand);
				objSqlDataAdapter.Fill(dsResult);  
				return dsResult;
			}
			catch(Exception Ex)
			{
				//ErrorHandler objERR = new ErrorHandler();
				//objERR.WriteError(DateTime.UtcNow +  ", DAL.DBUtility-Execute_StoreProc_DataSet(): " +  Ex.Message.ToString() + " " + Environment.UserName, Environment.UserName);
				throw;
			}
			finally
			{
				objCommand.Dispose();
				objSqlDataAdapter.Dispose();
				if (objConnection.State ==ConnectionState.Open)
					objConnection.Close();  
			}
			
		}

首先我认为Store Proc表现缓慢。如果我从后端执行

，它需要10秒钟才能在SSMS中显示结果。

但调试模式下的相同时间太长因此导致数据绑定太晚（超过4-5分钟。



数据阅读器是否会提供帮助？



我尝试了什么：



我在桌面上创建了一个索引。但是在用户界面结束时，我不知道可能是什么工作

First I thought the Store Proc is performing slow. If I execute from the back end
it takes 10 seconds to display the result in SSMS.
But the same on debug mode takes too long consequently binds the data too late (more than 4-5 mins).

Is Data reader going to help?

What I have tried:

I created an index on table. But at the UI end , I am not aware what could be the work around

推荐答案

从查看SP开始。它是做什么的，它是如何做到的。

通过SSMS执行它并查看它需要多长时间以及它返回的内容 - 在开始改变之前需要确定瓶颈的确切位置性能。

如果SP需要5分钟才能运行，那么你就是在浪费时间来优化你的C＃代码！

如果SP需要几秒钟，但返回的金额巨大然后你需要查看为什么你正在处理这么多可能是问题而且没有多少调整会改变这一点 - 你需要改变你的应用程序的整个工作方式。例如，将大量数据加载到用户显示中总是一个缓慢的过程。

所以获取一些数据（使用C＃中的Stopwatch类来获取一组可靠的时序数据）并专注于缓慢的区域。

我们不能为您做到这一点：我们无法访问您的数据或您的代码！

Start by looking at the SP. What it does, how it does it.
Execute it via SSMS and see how long it takes, and what it returns - you need to establish where exactly the bottleneck is before you can start changing things to improve performance.
If the SP take 5 minutes to run, then you are wasting your time trying to optimise your C# code!
If the SP takes seconds, but returns huge amounts of data then you need to look at why you are processing so much as that is likely to be the problem and no amount of tweaking will change that - you would need to change the whole way that your app works. Loading huge amounts of data into user displays is always going to be a slow process for example.
So get some data (use the Stopwatch class in C# to get a solid set of timing data where you can) and concentrate on the slow areas.
We can''t do that for you: we don''t have access to your data, or your code!

这篇关于填充数据适配器的方法运行缓慢。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！