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;
}