BIG problem with huge storage procedure execute on SQL Server 2000 SP4

[gicio]

BIG problem with huge storage procedure execute on SQL Server 2000 SP4

 

Hi!

 

I have a big problem with one of my storage procedures.

 

First of all some information about the server:

 

Dual XEON

4 gig RAM

Windows 2003

SQL Server 2000 with SP4

 

On the database work at the same time 30 peoples�. But not all execute storage procedure.

 

 

The storage procedure with that I have problems is executed only one time a day.

The storage procedure is so huge that the SQL Server needs 11 minutes to execute it.

The storage procedure do some work with data on the DB and return no values.

 

The problem:

 

When I execute the storage procedure directly on the SQL server ( with query analyzer ):

execute OC_Auto

the storage procedure works fine.

BUT when I excetute the storage procedure with C# doesn�t work fine.

The storage procedure runs only 2 � 4 minute and do not his complete work.

But I don�t get any error messages.

 

To execute the storage procedure I use the newest version of �Microsoft.ApplicationBlocks.Data� and �GotDotNet.ApplicationBlocks.Data�

 

And this is the code:

 

	internal void Auto()
	{
		SqlConnection currentSqlConnection = ADOConnection.Connection;

		try 
		{		
			SqlHelper.ExecuteDataset(currentSqlConnection, "OC_Auto");
		}
		catch( SqlException ex )
		{
			System.Diagnostics.Debug.WriteLine( ex.Message );
			Common.Logger.Log(Common.Logger.ERROR_TYPE ,"SQL Error",ex.InnerException);
		}
		catch( Exception ex2 )
		{
			System.Diagnostics.Debug.WriteLine( ex2.Message );
			Common.Logger.Log(Common.Logger.ERROR_TYPE ,"SQL Error",ex2.InnerException);
		}
		finally 
		{
			currentSqlConnection.Close();
		}
	}



	public static DataSet ExecuteDataset(SqlConnection connection, string spName, params object[] parameterValues)
	{
		return new SqlServer().ExecuteDataset( connection, spName, parameterValues );
	}


	/// <summary>
	/// Execute a stored procedure via an IDbCommand (that returns a resultset) against the specified IDbConnection 
	/// using the provided parameter values.  This method will query the database to discover the parameters for the 
	/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
	/// </summary>
	/// <remarks>
	/// This method provides no access to output parameters or the stored procedure's return value parameter.
	/// </remarks>
	/// <example>
	/// <code>
	/// DataSet ds = helper.ExecuteDataset(conn, "GetOrders", 24, 36);
	/// </code></example>
	/// <param name="connection">A valid IDbConnection</param>
	/// <param name="spName">The name of the stored procedure</param>
	/// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param>
	/// <returns>A DataSet containing the resultset generated by the command</returns>
	/// <exception cref="System.ArgumentNullException">Thrown if spName is null</exception>
	/// <exception cref="System.ArgumentException">Thrown if the parameter count does not match the number of values supplied</exception>
	/// <exception cref="System.ArgumentNullException">Thrown if connection is null</exception>
	public virtual DataSet ExecuteDataset(IDbConnection connection, string spName, params object[] parameterValues)
	{
		if( connection == null ) throw new ArgumentNullException( "connection" );
		if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

		// If we receive parameter values, we need to figure out where they go
		if ((parameterValues != null) && (parameterValues.Length > 0)) 
		{
			IDataParameter[] iDataParameterValues = GetDataParameters(parameterValues.Length);

			// if we've been passed IDataParameters, don't do parameter discovery
			if (AreParameterValuesIDataParameters(parameterValues, iDataParameterValues))
			{
				return ExecuteDataset(connection, CommandType.StoredProcedure, spName, iDataParameterValues);
			}
			else
			{
				// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
				bool includeReturnValue = CheckForReturnValueParameter(parameterValues);
				IDataParameter[] commandParameters = GetSpParameterSet(connection, spName, includeReturnValue);

				// Assign the provided values to these parameters based on parameter order
				AssignParameterValues(commandParameters, parameterValues);

				// Call the overload that takes an array of IDataParameters
				return ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
			}
		}
		else 
		{
			// Otherwise we can just call the SP without params
			return ExecuteDataset(connection, CommandType.StoredProcedure, spName);
		}
	}


	/// <summary>
	/// Execute an IDbCommand (that returns a resultset and takes no parameters) against the provided IDbConnection. 
	/// </summary>
	/// <example>
	/// <code>
	/// DataSet ds = helper.ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders");
	/// </code></example>
	/// <param name="connection">A valid IDbConnection</param>
	/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
	/// <param name="commandText">The stored procedure name or SQL command</param>
	/// <returns>A DataSet containing the resultset generated by the command</returns>
	/// <exception cref="System.ArgumentNullException">Thrown if commandText is null</exception>
	/// <exception cref="System.ArgumentNullException">Thrown if connection is null</exception>
	public virtual DataSet ExecuteDataset(IDbConnection connection, CommandType commandType, string commandText)
	{
		// Pass through the call providing null for the set of IDataParameters
		return ExecuteDataset(connection, commandType, commandText, (IDataParameter[])null);
	}



	/// <summary>
	/// Execute an IDbCommand (that returns a resultset) against the specified IDbConnection 
	/// using the provided parameters.
	/// </summary>
	/// <example>
	/// <code>
	/// DataSet ds = helper.ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new IDataParameter("@prodid", 24));
	/// </code></example>
	/// <param name="connection">A valid IDbConnection</param>
	/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
	/// <param name="commandText">The stored procedure name or SQL command</param>
	/// <param name="commandParameters">An array of IDataParameters used to execute the command</param>
	/// <returns>A DataSet containing the resultset generated by the command</returns>
	/// <exception cref="System.InvalidOperationException">Thrown if any of the IDataParameters.ParameterNames are null, or if the parameter count does not match the number of values supplied</exception>
	/// <exception cref="System.ArgumentNullException">Thrown if commandText is null</exception>
	/// <exception cref="System.ArgumentException">Thrown if the parameter count does not match the number of values supplied</exception>
	/// <exception cref="System.ArgumentNullException">Thrown if connection is null</exception>
	public virtual DataSet ExecuteDataset(IDbConnection connection, CommandType commandType, string commandText, params IDataParameter[] commandParameters)
	{
		if( connection == null ) throw new ArgumentNullException( "connection" );

		// Create a command and prepare it for execution
		IDbCommand cmd = connection.CreateCommand();
		bool mustCloseConnection = false;
		PrepareCommand(cmd, connection, (IDbTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );
		CleanParameterSyntax(cmd);
   			
		DataSet ds = ExecuteDataset(cmd);

		if( mustCloseConnection )
			connection.Close();

		// Return the DataSet
		return ds;
	}


	/// <summary>
	/// Execute an IDbCommand (that returns a resultset) against the database specified in 
	/// the connection string. 
	/// </summary>
	/// <param name="command">The IDbCommand object to use</param>
	/// <returns>A DataSet containing the resultset generated by the command</returns>
	/// <exception cref="System.ArgumentNullException">Thrown if command is null.</exception>
	public virtual DataSet ExecuteDataset(IDbCommand command)
	{
		bool mustCloseConnection = false;

		// Clean Up Parameter Syntax
		CleanParameterSyntax(command);

		if (command.Connection.State != ConnectionState.Open)
		{
			command.Connection.Open();
			mustCloseConnection = true;
		}

		// Create the DataAdapter & DataSet
		IDbDataAdapter da = null;
		try
		{
			da = GetDataAdapter();
			da.SelectCommand = command;

			da.SelectCommand.CommandTimeout = 0;

			DataSet ds = new DataSet();

			try
			{
				// Fill the DataSet using default values for DataTable names, etc
				da.Fill(ds);
			}
			catch (Exception ex)
			{
				// Don't just throw ex.  It changes the call stack.  But we want the ex around for debugging, so...
				Debug.WriteLine(ex);
				throw;
			}
			
			// Detach the IDataParameters from the command object, so they can be used again
			// Don't do this...screws up output params -- cjb 
			//command.Parameters.Clear();

			// Return the DataSet
			return ds;
		}
		finally
		{
			if (mustCloseConnection)
			{
				command.Connection.Close();
			}
			if( da != null )
			{
				IDisposable id = da as IDisposable;
				if( id != null )
					id.Dispose();
			}
		}
	}

 

What is wrong? Why run this storage procedure only good with the SQL Query analyzer?