I would have guessed that when a query includes TOP n the database
engine would run the query ignoring the the TOP clause, and then at
the end just shrink that result set down to the n number of rows that
was requested. The graphical execution plan seems to indicate this is
the case -- TOP is the "last" step. But it appears there is more going
on.
The way the above is phrased makes me think you may have an incorrect mental picture of how a query executes. An operator in a query plan is not a step (where the full result set of a previous step is evaluated by the next one.
SQL Server uses a pipelined execution model, where each operator exposes methods like Init(), GetRow(), and Close(). As the GetRow() name suggests, an operator produces one row at a time on demand (as required by its parent operator). This is documented in the Books Online Logical and Physical Operators reference, with more detail in my blog post Why Query Plans Run Backwards. This row-at-a-time model is essential in forming a sound intuition for query execution.
My question is, how (and why) does a TOP
n clause impact the execution
plan of a query?
Some logical operations like TOP
, semi joins and the FAST n
query hint affect the way the query optimizer costs execution plan alternatives. The basic idea is that one possible plan shape might return the first n rows more quickly than a different plan that was optimized to return all rows.
For example, indexed nested loops join is often the fastest way to return a small number of rows, though hash or merge join with scans might be more efficient on larger sets. The way the query optimizer reasons about these choices is by setting a Row Goal at a particular point in the logical tree of operations.
A row goal modifies the way query plan alternatives are costed. The essence of it is that the optimizer starts by costing each operator as if the full result set were required, sets a row goal at the appropriate point, and then works back down the plan tree estimating the number of rows it expects to need to examine to meet the row goal.
For example, a logical TOP(10)
sets a row goal of 10 at a particular point in the logical query tree. The costs of operators leading up to the row goal are modified to estimate how many rows they need to produce to meet the row goal. This calculation can become complex, so it is easier to understand all this with a fully worked example and annotated execution plans. Row goals can affect more than the choice of join type or whether seeks and lookups are preferred to scans. More details on that here.
As always, an execution plan selected on the basis of a row goal is subject to the optimizer's reasoning abilities and the quality of information provided to it. Not every plan with a row goal will produce the required number of rows faster in practice, but according to the costing model it will.
Where a row goal plan proves not to be faster, there are usually ways to modify the query or provide better information to the optimizer such that the naturally selected plan is best. Which option is appropriate in your case depends on the details of course. The row goal feature is generally very effective (though there is a bug to watch out for when used in parallel execution plans).
Your particular query and plan may not be suitable for detailed analysis here (by all means provide an actual execution plan if you wish) but hopefully the ideas outlined here will allow you to make forward progress.
The query takes a lot of time because it finds no rows.
The plan first is setup top pick one row from tabSparePart
where sparepartname = '1270-3132'
. It then moves along to the index scan of tabData. For each row fetched from tabData
there is a clustered index seek in locSL
followed by a clustered index seek in tabDataDetail
which also checks for not null on gsp_part_price
and finally returns the values of fiSparePart
and GSP_Part_Price
. fiSparePart
is then compared against the idSparePart
returned from the Index Seek in tabSparePart
. If they are the same the query immediately terminates and return the gsp_part_price
. If they are not the same the index scan in tabData
moves to the next record and all the checks are executed again. That keeps happening until there is a row returned or all rows in tabData
have been processed.
As it turns out you get a totally different query plan when you do top(1000).
You can use the optimize for
hint to make SQL Server think you actually want 1000 rows when you only need one.
Your scalar valued function would then look something like this:
CREATE FUNCTION [dbo].[FunctionName]()
RETURNS INT
AS
BEGIN
DECLARE @TOP INT
DECLARE @RET INT
SET @TOP = 1
SELECT TOP (@TOP) @RET = dd.gsp_part_price
FROM tabdata
INNER JOIN locsl
ON tabdata.fisl = locsl.idsl
INNER JOIN locgsp
ON locsl.figsp = locgsp.idgsp
INNER JOIN tabdatadetail dd
ON dd.fidata = tabdata.iddata
INNER JOIN tabsparepart sp
ON dd.fisparepart = sp.idsparepart
WHERE sp.sparepartname = '1270-3132'
AND dd.gsp_part_price IS NOT NULL
ORDER BY Claim_Submitted_Date DESC
OPTION (OPTIMIZE FOR(@TOP = 1000))
RETURN @RET
END
Best Answer
I have other ideas than a plan guide and I'll post them here for your consideration, but I cannot recommend actually implementing them. There are at least two different ways of bumping up row estimates from tablea. The first involves manually setting statistics and the second involves redirecting the third party app to use a view that you defined instead of accessing the tablea directly.
Both CREATE STATISTICS and UPDATE STATISTICS have a STATS_STREAM option. Here is what Books Online says about it:
It's completely understandable if that turns you off from using it, but what it allows you to do is to transplant the statistics from one object to another. Suppose that you want to increase cardinality estimates from queries that use tablea by 50X. You could take the data from tablea, duplicate it 50 times, gather stats with FULLSCAN, then update the statistics on tablea using the STATS_STREAM value from the other table along with the NORECOMPUTE option. You probably want to change the ROWCOUNT value as well. This should have a pretty large effect on all queries that reference the table. You could run into problems with other queries, with the underlying data changing in the table, with statistics on indexes not getting updated, and so on. This isn't a good option.
Depending on how the third party application connects to SQL Server, you may be able to create a view with a different schema from tablea and to direct that part of the application to use in the view. The view should have all of the same columns as tablea but should have additional code which inflates the number of rows returned. I didn't try that hard to get this to work because it seems very impractical, but this approach was inspired by Adam Machanic's article about forcing parallel plans:
I wasn't really satisfied with that query but didn't spend too much time on it. In that form it does a single scan of the table but the query optimizer overestimates the cardinality estimate by 100X. TF 8690 is there to prevent a pointless table spool, but this probably isn't appropriate in a view or in a larger query. If the same application inserts data into this table you'd need to deal with that somehow. This isn't a good option.
To reiterate, I don't think that you should attempt either of these options. It would be much better to talk to the vendor, or if that doesn't work, to suck it up and try again with a plan guide. I don't have any experience with those so I can't be of help.