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: learn.microsoft.com/en-us/dotnet/api/… — paulsm4
– paulsm4, Commented Nov 20, 2018 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
– Nathan Champion, Commented Nov 20, 2018 at 17:29

Joel Coehoorn · Accepted Answer · 2018-11-20 19:09:58Z

2

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

edited Nov 20, 2018 at 19:09

answered Nov 20, 2018 at 17:29

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

paulsm4 Over a year ago

"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"...

Joel Coehoorn Over a year ago

@paulsm4 Yes, it can begin returning results before the entire set is available, under the conditions described in this answer.

Collectives™ on Stack Overflow

How to get some results mid query using SqlCommand

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related