How to get some results mid query using SqlCommand

Question

I am using SqlCommand to perform a query, however this query is very slow in general - taking about 50 seconds to complete - is there anyway I can read the results as they come in one by one?

using (SqlConnection connection = new SqlConnection(ExportMetrics.CreateConnectionString()))
{
    SqlCommand command = new SqlCommand(sqlQuery, connection);
    connection.Open();
    SqlDataReader reader = command.ExecuteReader();

    try
    {
        while (reader.Read())
        {
                //Read results
        }
        catch (Exception e)
        {
            //Exception
        }
        finally
        {  
            reader.Close();
        }
    }
}

Short answer: no. It sounds like you need to optimize your query, add indexes, and/or cache your result sets. You can also try making your query asynchronously: https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.executereaderasync?view=netframework-4.7.2 — paulsm4, Nov 20 '18 at 17:23
You may be able to squeak out performance by converting it to a stored procedure, memory optimized tables, indexes, etc. Really depends what your query looks like and why the query is slow but you can't look at those records they're all returned as a group. — Nathan Champion, Nov 20 '18 at 17:29

Joel Coehoorn · Answer 1 · 2018-11-20T19:09:58.507

Reading results "as they come in one by one" is what an SqlDataReader already does.

Whether this helps depends on the query. Queries with GROUP BY+aggregate functions or ORDER BY¹ clauses generally have to materialize the entire result set in server memory before they can begin returning results. Other language constructs might require this, too. Queries without those language features may be able to start returning results faster, depending on the execution plan.

But 50 seconds is a looooong time in the world of SQL. There's almost always a way to re-write the query or tune indexes to run much faster. Of course, we can't help with that unless we can see the query, the table structure, and the indexing all listed as part of the question.

^{1 If the ORDER BY clause matches the primary key or other important indexes relative to JOINs, such that the requested order matches the order used for the query's working set, and this can be mathematically shown, you might still be okay.}

"Reading results "as they come in one by one" is what an SqlDataReader already does.". Yes - but the reader isn't going to return the FIRST record until the ENTIRE result set is available. In other words, it isn't going to help with the "50 seconds"... — paulsm4, Nov 20 '18 at 19:08
@paulsm4 Yes, it **can** begin returning results before the entire set is available, under the conditions described in this answer. — Joel Coehoorn, Nov 20 '18 at 19:09

How to get some results mid query using SqlCommand

1 Answers1