DataAdapter .Fill is slow. How to make something similar with DataReader?

Question

I have a utility class with SQLDataAdapter reading data. Problem is when I try to return data from a SP using DataAdapter.fill, it almost takes around 1min,16 seconds to do the Fill operation and return the data.( it takes 5-6 seconds to execute the SP in SSMS and return 6 datasets total of <5000 records, total of all 6 datasets) Why is this difference happening?

public DataSet ExecuteQuery(string connName, string sp, List<SqlParameter> params)
{
    DataSet ds = new DataSet();
    using (SqlConnection cn = GetConnection(connName))
    {
        using (SqlCommand cmd = cn.CreateCommand())
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = sp;
            cmd.CommandTimeout = 600;
            // assign parameters passed in to the command
            
            foreach (var param in params)
            {
                cmd.Parameters.Add(param);
            }
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                da.Fill(ds);
            }
        }
    }
    return ds;
}

Since this is a common function I thought of creating new function using SQLDataReader which uses .Load but again even that is slow ( infact felt more slow than DataAdapter). I have to give the result within 10-15 seconds so how do I resolve this ?

Answer 1

For me the solution was to create another helper class but with running SET ARITHABORT ON first and then running my query and dataadapter.

public DataSet ExecuteQuery(string connName, string sp, List<SqlParameter> params)
{
    DataSet ds = new DataSet();
    using (SqlConnection cn = GetConnection(connName))
    {
        using (SqlCommand arithAbortCMD = cn.CreateCommand())
        {
            arithAbortCMD.CommandText = "SET ARITHABORT ON";
            arithAbortCMD.ExecuteNonQuery();
        }
        using (SqlCommand cmd = cn.CreateCommand())
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = sp;
            cmd.CommandTimeout = 600;
            // assign parameters passed in to the command
            
            foreach (var param in params)
            {
                cmd.Parameters.Add(param);
            }
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                da.Fill(ds);
            }
        }
    }
    return ds;
}

Answer 2

DataReader should be slighly faster, but not noticeable. 5000 records is not little, but also not too much, it should work.

My suspicion is that this is slow for another reason, with the SQL query the first suspect. Even 7 seconds is a lot. But when you test that, did you test this on the server itself or from remote? Maybe the data transfer itself is very slow. That is not a very common problem either. You also may want to check your connection string to see how you connect.

In any case, to run a datareader, you need something like this:

   private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
    {
        //create a command and prepare it for execution
        SqlCommand cmd = new SqlCommand();
        cmd.CommandTimeout = connection.ConnectionTimeout;  //leo - so setting the connecting timeout is all that we need.
        PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);
    
        //create a reader
        SqlDataReader dr;
    
        // call ExecuteReader with the appropriate CommandBehavior
        if (connectionOwnership == SqlConnectionOwnership.External)
        {
            dr = cmd.ExecuteReader();
        }
        else
        {
            dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
    
        // detach the SqlParameters from the command object, so they can be used again.
        cmd.Parameters.Clear();
    
        return dr;
    }


private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters)
{
    //if the provided connection is not open, we will open it
    if (connection.State != ConnectionState.Open)
    {
        connection.Open();
    }

    //associate the connection with the command
    command.Connection = connection;

    //set the command text (stored procedure name or SQL statement)
    command.CommandText = commandText;

    //if we were provided a transaction, assign it.
    if (transaction != null)
    {
        command.Transaction = transaction;
    }

    //set the command type
    command.CommandType = commandType;

    //attach the command parameters if they are provided
    if (commandParameters != null)
    {
        AttachParameters(command, commandParameters);
    }

    return;
}

You will probably find easier examples now you know what to look for. some important notes:

1. The command timeout is in addition to the connection timeout. The first one to reach the limit will raise an error. That is why I set them to the same.
2. Use Using() for the datareader, if you leave it open it will really stay open, you will run out of connections.