Click here to Skip to main content
15,886,608 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

C#
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();  
			}
			
		}




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
Posted
Updated 6-Jun-19 23:40pm

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!
 
Share this answer
 
Use the DataReader and its Execute reader method to fill Dataset, instead of the DataAdapter.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900