Slow performant select query, yet apparently simple.
-
agustingarzon (3/1/2010)
The exec plan for your suggestion is the same as NewExec I just posted. The approach is ok, but it's making a clust-ix-scan that scans through the first 15.000 records based on your example. But if I add an ORDER BY num.IXX ASC as Paul suggested, the exec plan changes the large scan for a seek operation to get the exact 20 required rows.That helps to explain things a lot. Would you mind running the code I posted before, and posting it's actual execution plan? I will understand if this is not practical, but it would help completely nail this down. Here is the code again, in case you missed it:
WITH Numbered
AS (
SELECT CF.ID,
rn = ROW_NUMBER() OVER (ORDER BY CF.ID ASC)
FROM dbo.ContactForms CF
),
Keys
AS (
SELECT TOP (20)
N.ID
FROM Numbered N
WHERE N.rn >= 30000
ORDER BY
N.rn
)
SELECT data.*
FROM Keys K
INNER
LOOP
JOIN dbo.ContactForms data
ON data.ID = K.ID
OPTION (FORCE ORDER);
agustingarzon (3/1/2010)
I haven't run the test to see if it gets any faster, will do in a couple hours and post back.That information is pretty crucial!
agustingarzon (3/1/2010)
If nvarchar-max columns are stored off row as you said and as explained in the documentation, why would the clustered index behave in such a way ? The 3 nvarchar(255) fields add up to 0.4 kbs in the largest row. I can't find a way to get the table "large value types out of row" value, I know of the sp_tableoption but it's only for setting the value, perhaps it's storing in row but that's not the default.Please...post the CREATE TABLE statement, and tell us what approximate length of data is stored in each NVARCHAR(MAX) column. As far as determining what the setting of "large value types out of row" is concerned:
SELECT name, large_value_types_out_of_row, *FROM sys.tables;
Paul
-
RBarryYoung (3/1/2010)
agustingarzon (3/1/2010)
Dear Barry, the exec plan for your suggestion is the same as NewExec I just posted.The approach is ok, but it's making a clust-ix-scan that scans through the first 15.000 records based on your example.
But if I add an ORDER BY num.IXX ASC as Paul suggested, the exec plan changes the large scan for a seek operation to get the exact 20 required rows.
I haven't run the test to see if it gets any faster, will do in a couple hours and post back.
Paul, if nvarchar-max columns are stored off row as you said and as explained in the documentation, why would the clustered index behave in such a way ? The 3 nvarchar(255) fields add up to 0.4 kbs in the largest row. I can't find a way to get the table "large value types out of row" value, I know of the sp_tableoption but it's only for setting the value, perhaps it's storing in row but that's not the default.
Just to clarify, are you saying that the ORDER BY fixes it?
Hmm, maybe that's all it is then? The ORDER BY is supposed to be required for TOP, but they don't enforce that syntactically. So maybe the optimizer just gets dumb sometimes, without it?
Hmm, though now that I look again, I do see that it still has the same incorrect rowcounts, it just made a better plan. In fact every plan posted so far thinks there's only 66 rows in that table (instead of the 15020 that it actually finds). I really would like to understand what causes that...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
RBarryYoung (3/1/2010)
In fact every plan posted so far thinks there's only 66 rows in that table (instead of the 15020 that it actually finds). I really would like to understand what causes that...The QO estimates 66 rows will be needed from the clustered index scan to reach the 20 rows specified by the later TOP opertor. It is a matter of cardinality/selectivity/statistics.
There's a really good blog around somewhere about such plans with 'row goals', I'll see if I can find it. The 'row goal' here is 20, in case I'm not being clear.
-
Paul, adding the ORDER BY boosted the query times !
I'm adding the plans Before and After. Before (The original Jeff's query) took like 1 minutes and the After was instant.
I'm also attaching the plan for the query you suggested (named Pauls), it felt instant too, but I can't confirm it wasn't a cache thing because I run similar queries before, but will run it a few more times in order to confirm / discard this.
Please...post the CREATE TABLE statement, and tell us what approximate length of data is stored in each NVARCHAR(MAX) column. As far as determining what the setting of "large value types out of row" is concerned:
Attached. The 3 nvarchar-max fields add up to as much as 110kb~, the other string fields add up to as much as 0.4kbs ~
Scanning the sys.tables catalog shows a value of zero on the large_value_types_out_of_row column, which would mean the first 8kb are stored in-row and the remainin outside, please correct me if I'm wrong.
The plans I'm attaching are the actual plans, so the row count should look more accurate now
It's been such an educational process for me... I appreciate so much all your contributions 🙂
-
agustingarzon
Adding the ORDER BY improved the query timesSo I see.
Without the ORDER BY, SQL Server is free to return the TOP (N) rows in any order it thinks makes most sense. In this case, it makes a poor choice of plan in that situation.
The lesson here is to always specify the order for a TOP. When we tell it to return the TOP (N) IDs by the row number, we are giving the optimizer more, and better, information to work with. It rewards us by producing a plan that executes very quickly.
As an aside, I still prefer my query for somewhat contrary reasons: by specifying not only what we want, but also how we want it to be retrieved, there is little scope for a poor plan.
agustingarzon
Scanning the sys.tables catalog shows a value of zero on the large_value_types_out_of_row column, which would mean the first 8kb are stored in-row and the remainin outside, please correct me if I'm wrong.Not quite. The full answer is somewhat involved, I'm afraid:
The storage for a single column in a row is always either completely in-row, or completely off-row. There is no question of some of it being stored in-row, and the rest off-row (though data stored off-row does have a small pointer to it left in-row).
For each row in a table, when the actual stored data in a single MAX column (of any type) exceeds 8000 bytes (that's 4000 Unicode characters), the row data is definitely stored entirely off row. If the data uses less than 8000 bytes, it might be stored in-row with the rest of the non-LOB table data.
The second case (where the data for a column in a row uses less than 8000 bytes) is a bit more complex. The data may be stored entirely in row, but as soon as the total row size (including all columns) tries to exceed 8060 bytes, SQL Server may move the data for a column to special row-overflow storage. This is not the same as off-row LOB storage, by the way.
The exception to that rule is that if the column being moved to overflow storage happens to be a MAX data type, the row-overflow storage is considered to be normal off-row LOB storage.
So, in summary, the current storage arrangement for your table is likely to be somewhat complex. If the data in the three MAX data type columns is always more than 4000 Unicode characters, they must all be off-row. That 'always' means in every column and every row - remember that these rules are applied on a per-row basis.
If the MAX columns sometimes use fewer than 4000 characters, but the total row size exceeds 8060 bytes (very likely) then one of more of them will be in row-overflow-LOB allocation units, off row - but one of them might fit on the row depending on its exact size and the data in other columns on that row.
If the MAX columns and the other columns on a row sometimes fit into the 8060 row size limit, you might have some rows that are stored entirely in-row...but they are enormous rows of course.
In short, it's a bit of a mess.
If your MAX columns store more data between them than will comfortably fit on a single 8060 byte row (with all the other column data too) I would be very tempted to move them all off row. I can't say I would definitely do it without knowing more about how often the are accessed, and how often you (partially) scan the clustered index.
My other worry here is that your column data types seem rather arbitrary. Do all those columns really need a maximum size of 255? SQL Server often has to plan for the maximum row size it might encounter - you can help it by specifying realistic maximum sizes. Ignoring overheads, just the three NVARCHAR(255) columns might use 3 * 255 * 2 = 1530 bytes. You might not be using all that, but SQL Server has to allow for the possibility that some day you will.
It's a complex subject, and the optimal settings depend heavily on the details. As always.
agustingarzon
It's been such an educational process for me... I appreciate so much all your contributionsYou are welcome, for my part. There does come a time, however, when the best thing to do is to consider getting someone more experienced in - if only for a week or two, to help you with complex issues like this. If this is an option for your employer, perhaps you might suggest it? Don't take that the wrong way - we all have to learn, I am just conscious that a forum is probably not the best place for questions as complex as this one.
Paul
-
Paul, did you come with anything on the bad rowcount issue? I'm afraid it's really bugging me... :unsure:
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Barry,
Are you asking about the Row Goal thing I said I'd have a look for? I didn't look yet :blush:
But it really is just a case of guesswork by the optimizer. It has to predict how may rows it will have to scan from each input for the merge join to result in 20 rows.
Once twenty rows are produced the execution stops, naturally.
Is your question concerning how it decides how many rows it will need to scan? IIRC it takes a compete guess at selectivity when it hasn't got any useful information, and the figure of 30% springs to mind.
It might not be complete co-incidence that the reciprocal of 30% as a decimal is 3.3333333333333333333333333333333 and that multiplied by 20 target rows is 66.666666666666666666666666666667. That figure should be familiar...;-)
Paul
-
Yes, 30% is the hard-coded guess. There's loads more information here:
http://msdn.microsoft.com/en-us/library/dd535534.aspx
edit:
Specifically, it's the selectivity of the Filter operator that implements the "WHERE num.IXX > 15000" condition in the queries. The guess for 'greater than' is 30%. Changing the WHERE clause to:
WHERE num.IXX BETWEEN 15000 AND 15020
will reduce the selectivity guess to 9%, which might produce a better plan. Or it might not.
Paul
-
Paul White (3/2/2010)
Barry,Are you asking about the Row Goal thing I said I'd have a look for? I didn't look yet :blush:
But it really is just a case of guesswork by the optimizer. It has to predict how may rows it will have to scan from each input for the merge join to result in 20 rows.
Once twenty rows are produced the execution stops, naturally.
But in most of these plans, there is NO predicate on the Scan. How can it stop execution/scanning if it has no predicate? And the Actuals bear that out, as they show that it was scanning all 15000 (or 30000, later) every time.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Personally, I am about convinced that this is an Optimizer bug of some kind, and that it is also the direct cause of the bad plan in this case.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
RBarryYoung (3/2/2010)
But in most of these plans, there is NO predicate on the Scan. How can it stop execution/scanning if it has no predicate? And the Actuals bear that out, as they show that it was scanning all 15000 (or 30000, later) every time.Right ok. This is the Row Goal thing. The thing that stops execution is that final TOP. It's a Row Goal TOP - as soon as the rows flowing through the plan produce the twenty rows needed by the TOP, the production line gets switched off. Remember that (packets of) rows flow from right to left through non-blocking operators.
Let me take the before.sqlplan posted as an example. We should start by noticing that none of the operators in this plan are blocking. Blocking operators need to consume their entire input before producing rows on their output. An example of a blocking operator is Sort.
Execution starts with the two ordered scans of the clustered index on the far right of the plan. We can tell it is an ordered scan (by ID) since the properties of the operator include Ordered:True.
The lower ordered scan one feeds rows into the branch with the Segment and Sequence Project operators which implement the ROW_NUMBER function. The filter in this branch passes only rows with an assigned row number greater than 30,000. Any rows that meet this condition start arriving at the merge join.
Simultaneously, rows produced from the upper scan also start arriving at the merge join. Because both inputs are sorted on ID, the Merge can flow rows through to the Row Goal TOP, as soon as the same IDs have been received on both branches.
This can be a very efficient alogorithm for this type of plan, so it's no great surprise that the QO is happy with it.
Unfortunately, it has to guess at the selectivity of that Filter operator. It might be obvious to us that 30,000 ordered rows will be required before rows start to get through, but without the final ORDER BY for the TOP, the QO cannot make that same assessment. We asked for any random 20 rows, see?
Paul
-
RBarryYoung (3/2/2010)
Personally, I am about convinced that this is an Optimizer bug of some kind, and that it is also the direct cause of the bad plan in this case.It's a bug in the query! Bear with me.
In the estimated plan, you are expecting to see at least 30,000 rows coming from the scan that feeds the row numbering, right? You know that each row from the scan will be assigned a row number (IXX), in sequence, and the filter contains a condition specifying IXX > N. Therefore, more than 30,000 rows will be required, right?
We can produce the plan you are after, but we have to think like the optimizer. The problem is that although the row number assignment has a definite order to it (specified in the OVER clause), SQL Server does not guarantee that order for flows on to other operators.
Therefore, any assertion based on that ordering cannot be relied upon. In principle, rows could arrive in any order at the filter. So, the QO resorts to a guess. It is a fairly subtle point I'm afraid, but it is there.
We can, of course, save ourselves all this noodling over implementation details, if we write the query correctly. Here is a test rig that illustrates my point:
USE tempdb;
GO
IF OBJECT_ID(N'tempdb.dbo.ContactForms', N'U')
IS NOT NULL
DROP TABLE tempdb.dbo.ContactForms;
GO
CREATE TABLE dbo.ContactForms
(
id INTEGER IDENTITY PRIMARY KEY,
data NVARCHAR(MAX) NOT NULL
);
-- Add 10,000 rows of data
-- (takes around 15 seconds)
WITH Numbers (rn)
AS (
SELECT rn = ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM master.sys.columns C1,
master.sys.columns C2,
master.sys.columns C3
)
INSERT dbo.ContactForms
(data)
SELECT TOP (10 * 1000)
REPLICATE(CONVERT(NVARCHAR(MAX), NCHAR(N.rn % 26 + 65)), 9000)
FROM Numbers N
ORDER BY
N.rn;
-- Show any 10 rows
SELECT TOP (10) * FROM dbo.ContactForms;
-- Before (produces same plan as in the thread)
WITH cteNumber
AS (
SELECT id,
IXX = ROW_NUMBER() OVER (ORDER BY ID ASC)
FROM dbo.ContactForms
)
SELECT TOP (20)
CF.*
FROM dbo.ContactForms CF
JOIN cteNumber NUM
ON CF.id = NUM.id
WHERE NUM.IXX > 3000;
-- Before with ORDER BY
-- (estimates still off)
WITH cteNumber
AS (
SELECT id,
IXX = ROW_NUMBER() OVER (ORDER BY ID ASC)
FROM dbo.ContactForms
)
SELECT TOP (20)
CF.*
FROM dbo.ContactForms CF
JOIN cteNumber NUM
ON CF.id = NUM.id
WHERE NUM.IXX > 3000
ORDER BY
NUM.IXX;
-- My (completed) implementation
-- Good plan and correct estimates
WITH Numbered
AS (
SELECT TOP (3020)
CF.ID,
rn = ROW_NUMBER() OVER (ORDER BY CF.ID ASC)
FROM dbo.ContactForms CF
ORDER BY
rn
)
SELECT data.*
FROM Numbered N
INNER
LOOP
JOIN dbo.ContactForms data
ON data.ID = N.ID
ORDER BY
N.rn ASC
OPTION (FORCE ORDER);
GO
IF OBJECT_ID(N'tempdb.dbo.ContactForms', N'U')
IS NOT NULL
DROP TABLE tempdb.dbo.ContactForms;
Notice how the last query properly guarantees row order from the row numbering section, and produces the correct estimated row count. The query plan produced for it is this:
Paul
-
OK, I think that I have it now. We were talking about slightly different things, but what you've explained did manage to kick me into seeing it correctly. I was really stuck in some kind of paradigm/POV/tunnel-vision thing (there's some fancy word for that, but I can't recall it) where I was just looking at it from the wrong perspective, embarrassing, but there it is. (Though not as bad as what I only realized this morning was the same thing that I did in an even bigger way on another thread this past week, but I'll apologize for that there ... :blush:).
What I was hung up on why all of the plans seemed to be estimating that there was only 66 rows in that table, but then obviously scanning tens of thousands of rows. Especially since on the {Before} query as the TOP 20 had no ORDER BY clause, meaning it could take ANY 20, so why wasn't it just taking the first 20 from Contact forms and being done with it? duh.
Well, because:
1) The "Estimated Rows" on an Index Scan aren't an estimate of how many rows are in the table, they're an estimate of how many rows it is going to scan, (I knew this) and,
2) It can scan less than the whole index/table based on a predicate (I knew this), OR,
3) It gets cut-off upstream, as it was estimating that it would in this case (I have to admit, I overlooked this), and,
4) The WHERE clause is dependent on the ROW_NUMBER on the table, and,
5) TOP clauses are required to be logically after WHERE clauses (complete mental misfire: for some reason I kept thinking that they could be before a WHERE clause, which since they are obviously dependent on ORDER BYs, makes no sense at all, DUH), therefore,
6) The TOP clause could NOT be pushed down to the index-scan directly by the optimizer, because the WHERE clause + ROW_NUMBER dependency prevents that. DUH, so ...
7) Finally, the "66" is just how many rows the optimizer thought that it would take to get 20 rows past the WHERE clause. Which we can easily see is crazy given the ROW_NUMBER dependence, but it must not know to make that particular implication.
In other words, exactly what Paul said and was trying to tell me. Again, duh. *sigh* something definitely wrong with the old noggin this week, ... 🙁
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
After thinking about this for a minute I was a little confused about why everyone is trying to use a ranking function when the data is already 'rankable' via the Id Column.
I whipped up a very simple example to verify that my syntax is valid but I'm not sure if this will meet your needs.
1. Create a test table
CREATE TABLE TestTable(
Id int not null primary key identity,
Col1 varchar(10) null,
Col2 varchar(10) null,
Col3 varchar(10) null
)
2. Fill TestTable with data
DECLARE
@Idx INT,
@Limit INT
SET @Idx=0
SET @Limit=50000
WHILE @Idx<@Limit BEGIN
INSERT INTO TestTable (Col1,Col2,Col3) VALUES ('c1-'+CAST(@Idx AS VARCHAR), 'C2-'+CAST(@Idx AS VARCHAR), 'C3-'+CAST(@Idx AS VARCHAR))
SET @Idx = @Idx+1
END
3. Select * from TestTable to verify that the data is there.
4. This is the query that I used to pull the top 20 rows ranked by id starting at 30,000
SELECT
t1.Id,
t1.Col1,
t1.Col2,
t1.Col3
FROM
TestTable t1
JOIN (SELECT TOP(20)Id FROM TestTable WHERE Id>30000 ORDER BY Id) AS t2 on t2.Id=t1.Id
ORDER BY Id
I think that both order by clauses can even be removed because the table has a clustered index on the Id column so it should come out in Id order even without the order by clause.
Hopefully this helps.
-
William Plourde (3/3/2010)
After thinking about this for a minute I was a little confused about why everyone is trying to use a ranking function when the data is already 'rankable' via the Id Column.Because the ID column is not guaranteed to have values that run from 1...N in a single contiguous block.
Paul
Viewing 15 posts - 31 through 45 (of 46 total)
You must be logged in to reply to this topic. Login to reply