3

I have a query that looks like the following:

Select t1.field1, t2.field2, sum(t1.field3)
From  t1 inner join t2 on t1.id = t2.id
Group by t1.field1, t2.field2

It's simplified, normally there are a few more joins and attributes on it. My question is now, how much is the impact of adding a TOP N clause to the query instead of stopping the query after N rows are returned to my C# code.

I know, that adding the clause is better for the server, but I'm interested in a rough estimate of how much impact the clause has. My guess is, that because I have an aggregation, the server has to process all rows anyways, therefore the impact is not that high.

Edit: C# Snippet how we stop the execution

var cmd = new SqlCommand();
cmd.CommandText = sqlStatement;
cmd.Connection = connectionString;
SqlDataReader dr;
dr = cmd.ExecuteReader();
while (dr.HasRows)
{
    while (dr.Read())
    {
        MyDataRow newRow = new MyDataRowDataRow();
        newRow.Parse(dr); //Parse the value from datarow to our format
        result.Add(newRow);
        if (topN.HasValue)
        {
            if (result.Count >= topN.Value)
            {
                break; //stop reading!
            }
        }
    }
    dr.NextResult();
}
  • 4
    It depends. Which TOP N rows do you want? Your query currently has no ORDER BY, so currently the rows will come in a non-deterministic order (and so just adding TOP will return a non-deterministic set). If the ORDER BY clause is on an underlying column with an index, it might help the optimizer with joining, filtering, or ordering, but the details provided are far too generic to know. – Aaron Bertrand Aug 15 '15 at 21:22
  • 1
    @AaronBertrand: The OP is asking about TOP N vs stopping the query after getting N rows. So, currently they are already getting rows in an undeterministic order, therefore it may not matter that they will be using TOP N without an ORDER BY? – Andriy M Aug 15 '15 at 21:28
  • The use case is that we show the user in an edit-view just a partial result set to save sql-server and network-traffic ressources. The interesting point here is, how many sql-server ressources are we saving (if any). Order Bys might be applied, in the case that Order By is applied i see that there might be some performance boosts as the result set gets deterministic. Do you anything about the case without order by? – kamahl Aug 15 '15 at 22:39
  • Well, you could always test it, I guess. We can't test your scenario (again, nowhere near enough info), so anything we'd guess would be, well, a guess. – Aaron Bertrand Aug 16 '15 at 1:49
  • 2
    How are you "stopping" a query in progress via C#? Unless you are using a cursor somehow that we don't see I don't think you can stop a query before it completes. Are you just interrupting the stream returned from the database? – Erik Aug 16 '15 at 3:33
9

My guess is, that because I have an aggregation, the server has to process all rows anyways, therefore the impact is not that high.

Speaking from a SQL Server perspective, it depends. Here is an overview of what it depends on, and why:

Row Goals

Adding a top-level TOP (n) clause (with or without ORDER BY) has the same row goal effect as if a FAST (n) query hint had been specified. A row goal causes the optimizer to estimate plan costs on the basis of returning n rows quickly.

A small row goal therefore favours serial execution using non-blocking (pipelined) operators like (sort-free) merge or nested loops join, and semi-blocking (per group) operators like stream aggregate, over fully blocking operators like sort and eager hash distinct.

Combination operators, like hash join (which is blocking on its hash table build input, and pipelined on its probe input) may be chosen if the build input is expected to be small, and the build input is not expected to rebind much (or at all).

Pipelined execution

Where the logic of the query allows it, and suitable indexes exist, the optimizer may well be able to find a fully pipelined (or perhaps, semi-blocking) execution plan. A semi-blocking plan that blocks per group is potentially just fine, so long as the groups are small.

Even with optimal indexing, in some cases, it may be necessary to express the query using just the right syntax, perhaps also using various query and table hints, to obtain the most pipelined execution plan possible. It can be very tricky to achieve in all but the simplest cases.

A pipelined/semi-blocking plan means the SqlDataReader.Read() can fetch the first and subsequent rows with minimal latency. Essentially, both the C# code and the SQL Server execution engine are streaming rows. This is the case where specifying TOP (n) will make the most difference.

Default strategy

Where TOP (n) is not specified, the query optimizer targets total execution time for the full potential set of rows the query is expected to produce. This change in optimization goal tends to favour parallelism, fully blocking sorts & eager hash aggregation, and partially-blocking hash joins, for example. This would typically produce the full set significantly quicker than running a pipelined/semi-blocking plan (if available) to its final conclusion.

Where one or more blocking operators appears in a plan, the time-to-first-row may be a sizeable fraction of the potential overall execution time. This means the first SqlDataReader.Read() call will block for a long time. Stopping the DataReader after n rows are received by the client will have a relatively minor effect on elapsed time in this case, because the majority of the SQL Server work is already one before the first row becomes available.

Conclusion

How much effect adding TOP (n) has therefore depends at least on:

  • There being a pipelined execution plan solution (even in principle)
  • Suitable access methods (indexing)
  • Query syntax and optimizer features / limitations
  • The skill level and experience of the query developer

Example

Using the ContosoRetailDW sample dataset, where table FactOnlineSales has 12,627,600 rows. A query based on the template in the question is:

SELECT
    FOS.ProductKey, 
    DS.StoreKey, 
    SUM(FOS.SalesAmount)
FROM dbo.DimStore AS DS
JOIN dbo.FactOnlineSales AS FOS
    ON DS.StoreKey = FOS.StoreKey
GROUP BY 
    FOS.ProductKey,
    DS.StoreKey
ORDER BY
    FOS.ProductKey,
    DS.StoreKey;

Adding a useful index:

CREATE INDEX i 
ON dbo.FactOnlineSales 
    (ProductKey, StoreKey) 
INCLUDE 
    (SalesAmount);

The execution plan is:

Default Plan

This runs for 1650ms. Notice the sort and hashing operations.

With TOP (n)

Choosing n = 50 arbitrarily, the query is now:

SELECT TOP (50)
    FOS.ProductKey, 
    DS.StoreKey, 
    SUM(FOS.SalesAmount)
FROM dbo.DimStore AS DS
JOIN dbo.FactOnlineSales AS FOS
    ON DS.StoreKey = FOS.StoreKey
GROUP BY 
    FOS.ProductKey,
    DS.StoreKey
ORDER BY
    FOS.ProductKey,
    DS.StoreKey;

and the execution plan:

Pipelined plan

This runs for 57ms using pipelined iterators and per-group semi-blocking stream aggregate (instead of blocking eager hash).

Related question: How (and why) does TOP impact an execution plan?

1

Paul White's answer is wonderful from the SqlServer point of view. I know this site isn't really about C# code review but I feel like I would be remiss if I didn't mention a couple of things about your data access code. Plus some errors in your C# code affect your stated assumptions.

Firstly, your sample code may have been edited for brevity but just in case... you should always dispose disposable objects. Both the data reader and the connection are disposable. The code snippet you shared definitely won't be aborting the query as such SqlServer will continue to work even though you are ignoring the results. As such TOP(N) will be much more efficient, because you aren't asking it to continue producing results you won't ever see... Furthermore, not disposing the disposables can lead to several application layer problems including memory leaks, connections not being returned to the connection pool, etc. Adding using statements ensures disposables will be disposed.

Secondly, I did some research into how you can abort an ongoing query from C#. According to this documentation closing/disposing readers and/or command objects doesn't necessarily abort an ongoing query. This is what i thought. However, I also learned that you can abort a query via C# code using the Cancel method discussed in the link above.

Below is updated code with the various C# errors corrected, which gives you the behavior you expected.

using(var cmd = new SqlCommand()) // Added using
{
    cmd.CommandText = sqlStatement;
    cmd.Connection = connectionString;
    using(var dr = cmd.ExecuteReader()) // Added using
    {
        while (dr.HasRows)
        {
            while (dr.Read())
            {
                MyDataRow newRow = new MyDataRowDataRow();
                newRow.Parse(dr); //Parse the value from datarow to our format
                result.Add(newRow);
                if (topN.HasValue)
                {
                    if (result.Count >= topN.Value)
                    {
                        cmd.Cancel(); // Added Cancel
                        break; //stop reading!
                    }
                }
            }
            dr.NextResult();
        }
    }
}
  • hi. Thank you for the code review and your initative! The usings got stripped away due to brevity, you were right, but the findings of yours with the cancel method are a really really good catch! Thank you very mutch! – kamahl Aug 18 '15 at 0:35
  • hej Erik. You didn't catch one thing: the break is only for the inner Read() loop, i'm still querying for the next-result which throws an error. Didn't get it myself until now :D – kamahl Sep 7 '15 at 13:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.