March 13, 2012 at 11:17 am
SQLKnowItAll (3/13/2012)
Well, I'd like to turn the topic to why this would be used. Would I do a top 100 with a FAST 100? It seems needless to want to return 100 or 1000 rows before others since you have to wait for the query to finish before really doing anything anyway. Anyone have a business case for this?
It depends. Asynchronous query execution? Some search which returns data for the first page quick...
Actually never needed to use this option, paging is usually done at SQL Server level, but I guess everything possible...
March 13, 2012 at 11:19 am
SQLKnowItAll (3/13/2012)
Well, I'd like to turn the topic to why this would be used. Would I do a top 100 with a FAST 100? It seems needless to want to return 100 or 1000 rows before others since you have to wait for the query to finish before really doing anything anyway. Anyone have a business case for this?
Then may I suggest you start another thread? You could always post a link here to it for those that may be interested.
March 13, 2012 at 11:23 am
Lynn Pettis (3/13/2012)
SQLKnowItAll (3/13/2012)
Well, I'd like to turn the topic to why this would be used. Would I do a top 100 with a FAST 100? It seems needless to want to return 100 or 1000 rows before others since you have to wait for the query to finish before really doing anything anyway. Anyone have a business case for this?Then may I suggest you start another thread? You could always post a link here to it for those that may be interested.
Being that this topic is directly related to the FAST option, I think this is a perfect place for it. We've told the OP why it is not doing what they expect, so let's follow it up with what to expect and why you may use it. Show the proper usage of the option. The topic is pointless if we don't do this.
Jared
CE - Microsoft
March 13, 2012 at 11:25 am
And it could potentially confuse others that may come to this thread.
March 13, 2012 at 11:36 am
Lynn Pettis (3/13/2012)
And it could potentially confuse others that may come to this thread.
I'm sorry, but I disagree. I think anyone coming to this thread will be wondering in what cases or how to use this. So, does anyone have a real-world scenario for this? Does it make sense to do something like this?
SELECT TOP 1000 *
FROM TableA OPTION(FAST 1000)
In the sense that "maybe" it will use a nested loop to return these rows faster than possibly a hash join?
Jared
CE - Microsoft
March 13, 2012 at 11:49 am
Then we have to agree to disagree. Personally, I guess I should just bow out as I find the arguments so far counter productive and everyone is just trying to defend their position when actually at the end of it all you all have been saying basically the same thing.
Personally, I haven't had the need for the fast option as it doesn't necessarily use the best paln for querying the data, which really depends on each individual query. Some queries it may and others it may not.
March 13, 2012 at 1:32 pm
I have yet to find a good post on a practical application of using the fast option. Trust me, I have dug hard. I have demonstrated a clear change in execution plan, but certainly no improvements in time or IO.
USE tempdb
GO
--Create a quick test table
CREATE TABLE test (cola INT);
CREATE CLUSTERED INDEX i1 ON test(cola);
GO
SET NOCOUNT ON;
--Quick population of table
DECLARE @i INT = 0;
WHILE @i < 10000
BEGIN
INSERT INTO test
SELECT @i;
SET @i = @i + 1;
END
GO
--SET STATISTICS IO ON
--SET STATISTICS TIME ON
--RUN THIS SEPARATELY-- Hash Join
SELECT a.*
FROM test a
INNER JOIN test b
ON a.cola = b.cola;
--RUN THIS SEPARATELY-- Nested LoopJoin
SELECT a.*
FROM test a
INNER JOIN test b
ON a.cola = b.cola
OPTION(FAST 1);
--DROP TABLE test;
My best guess is that this can be used for quick logical debugging of some script that is meant to process a lot of rows, but removed once logic is confirmed. I would suggest not using this in a production environment as it may result in unpredictable or inefficient execution plans.
Jared
CE - Microsoft
March 13, 2012 at 1:56 pm
Sorry if it appeared I was being argumentative for its own sake, I just thought it was an important distinction as it made it sound like it can't hurt to use the hint...anyway, moving on...
There is a use for it as others have said, which is if you have an end user query that returns a lot of results, but they're most likely to be interested in the first page(s) then by giving this information to the optimiser, it can save a considerable amount of time retrieving the first n results by choosing a different approach, lazy loading the rest of the result set while the user isn't waiting.
By definition, it will give you a slower overall query execution (unless the cost estimates are incorrect), you can't measure whether it's a useful optimisation by timing/costing the time to return the whole resultset, of course it will be quicker when it's specifically optimised for the whole result set (e.g. the default). It performs within the context of what you've told it to optimise (the first row in the example above), so you can only tell if it's done that by timing until the first result is up, not when the last result is finished.
March 13, 2012 at 2:01 pm
Hmm... So not being an app developer I have a question. Can the developer use the returned results in say the first 10 rows of what will effectively be 1000 row result set and display that to the user before the execution is finished?
Jared
CE - Microsoft
March 13, 2012 at 2:12 pm
Absolutely. A data grid can return as soon as it has enough results to populate the first page.
A data reader just receives results in a pipe and can do whatever action it wants as the results are coming through.
Incidentally, in your example, using TOP also affects the choices the optimiser will make, so a fast n should be redundant if n is the same for both the query hint and the top , although it looks like you are getting different plans which is odd (I guess it still goes through a slightly different decision making process). I might dig into that if I get some time 🙂
March 13, 2012 at 2:13 pm
SQLKnowItAll (3/13/2012)
Does it make sense to do something like this?
SELECT TOP 1000 *FROM TableA OPTION(FAST 1000)
No, makes no sense whatsoever. You're saying get me the first 1000 rows as soon as you can and then get me the rest of the rows (all 0 of them) afterwards.
As for where it's useful, two words: Client-side paging.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 13, 2012 at 2:27 pm
HowardW (3/13/2012)
Absolutely. A data grid can return as soon as it has enough results to populate the first page.A data reader just receives results in a pipe and can do whatever action it wants as the results are coming through.
Incidentally, in your example, using TOP also affects the choices the optimiser will make, so a fast n should be redundant if n is the same for both the query hint and the top , although it looks like you are getting different plans which is odd (I guess it still goes through a slightly different decision making process). I might dig into that if I get some time 🙂
Ahh... I forgot to take out the TOP that I was screwing around with. Will edit. Thanks to you and Gail for your responses! Client-side paging. Got it! (only 1 example of course)
Jared
CE - Microsoft
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply