|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 1:27 PM
Points: 1,278,
Visits: 1,522
|
|
Paul White (11/1/2009)
Although the 2008 method is undoubtedly impressive, I'm very aware of the fact that it is a relatively small tweak to the original (awesome) solution from Peter. He should get the credit for the method really.
You know, it is this attitude that I see so much from the "regulars" here, and it's one of the reasons I love this site. But Paul, none of the others participating in this thread saw this... YOU did. YOU realized that this little tweak could be done, and that it would make a huge difference. So Paul, it might have been a "minor" tweak to an already good solution, but YOU made it, and ended up with the best plan yet (so far...). So, take credit where credit is due.
...but I thought I should add the above comments to ensure we are fair in our assessments.
and these are some excellent comments. My question is... how have you figured out all these different options? They aren't used very much...
Wayne
For better assistance in answering your questions, click here
For performance problems, please read this.
|
|
|
|
|
SSChasing Mays
Group: General Forum Members
Last Login: Yesterday @ 11:17 PM
Points: 612,
Visits: 1,433
|
|
Paul White (11/1/2009)
3. Peter's method also suffers at the hands of the optimizer for large sets. The lazy spool on the #Update table is a bit silly - we can fix it by adding OPTION (FORCE ORDER) to the query text, or by creating a (very reasonable) PRIMARY KEY on the RowID column of the #Update table.
Finally got the time to look at the test results. Great job Wayne, also very useful comments Paul. I'm learning all the time. About the lazy spool this. In my original cross join solution I used
cross join
(
select 1 column_no union all select 2 union all select 3
) n
which was changed into
CROSS JOIN (select TOP 30 column_no = row_number() over (order by object_id) from sys.objects) n
for the 30 column problem, which looks like the problem here. The Constant Scan is abandonned and the order of both table scans is reversed.
I suggest using the original code expanded to 30 constants i.e.
CROSS JOIN (
select 1 column_no union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10 union all
select 11 union all
select 12 union all
select 13 union all
select 14 union all
select 15 union all
select 16 union all
select 17 union all
select 18 union all
select 19 union all
select 20 union all
select 21 union all
select 22 union all
select 23 union all
select 24 union all
select 25 union all
select 26 union all
select 27 union all
select 28 union all
select 29 union all
select 30
) n
This will again result in a Constant Scan in the inner loop. It takes some coding but it's consistent and no need for query hints.
Peter
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Today @ 4:15 AM
Points: 2,069,
Visits: 2,040
|
|
WayneS (11/2/2009)
You know, it is this attitude that I see so much from the "regulars" here, and it's one of the reasons I love this site. But Paul, none of the others participating in this thread saw this... YOU did. YOU realized that this little tweak could be done, and that it would make a huge difference. So Paul, it might have been a "minor" tweak to an already good solution, but YOU made it, and ended up with the best plan yet (so far...). So, take credit where credit is due.
Ok, ok - appropriate credit taken 
Just make sure you take credit for the test rigs, testing, and publishing of the results!
WayneS (11/2/2009)
My question is... how have you figured out all these different options? They aren't used very much...
Just past experience with the optimizer! Once you put yourself in its place, and try to reason out why it made the choices it made, it becomes relatively easy to add appropriate hints to lead it in the right direction - as sometimes is necessary. The optimizer generally makes good choices given the information it has available. Sometimes though, the DBA knows something about the data that can only be passed on to the optimizer through the use of hints, extra statistics, or a constraint (like the PK I used, for example). More on this later.
The quality of the answers is directly proportional to the quality of the question.
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Today @ 4:15 AM
Points: 2,069,
Visits: 2,040
|
|
Here is the 2005-syntax equivalent of the 2008 code I posted (it produces an identical plan on 2005 to the 2008 version):
SELECT U.row_id, CA.column_nm, CA.old, CA.new
FROM #Update U
CROSS
APPLY (
SELECT 'column01', CONVERT(SQL_VARIANT, U.column01_old), CONVERT(SQL_VARIANT, U.column01_new) UNION ALL
SELECT 'column02', U.column02_old, U.column02_new UNION ALL
SELECT 'column03', U.column03_old, U.column03_new UNION ALL
SELECT 'column04', U.column04_old, U.column04_new UNION ALL
SELECT 'column05', U.column05_old, U.column05_new UNION ALL
SELECT 'column06', U.column06_old, U.column06_new UNION ALL
SELECT 'column07', U.column07_old, U.column07_new UNION ALL
SELECT 'column08', U.column08_old, U.column08_new UNION ALL
SELECT 'column09', U.column09_old, U.column09_new UNION ALL
SELECT 'column10', U.column10_old, U.column10_new UNION ALL
SELECT 'column11', U.column11_old, U.column11_new UNION ALL
SELECT 'column12', U.column12_old, U.column12_new UNION ALL
SELECT 'column13', U.column13_old, U.column13_new UNION ALL
SELECT 'column14', U.column14_old, U.column14_new UNION ALL
SELECT 'column15', U.column15_old, U.column15_new UNION ALL
SELECT 'column16', U.column16_old, U.column16_new UNION ALL
SELECT 'column17', U.column17_old, U.column17_new UNION ALL
SELECT 'column18', U.column18_old, U.column18_new UNION ALL
SELECT 'column19', U.column19_old, U.column19_new UNION ALL
SELECT 'column20', U.column20_old, U.column20_new UNION ALL
SELECT 'column21', U.column21_old, U.column21_new UNION ALL
SELECT 'column22', U.column22_old, U.column22_new UNION ALL
SELECT 'column23', U.column23_old, U.column23_new UNION ALL
SELECT 'column24', U.column24_old, U.column24_new UNION ALL
SELECT 'column25', U.column25_old, U.column25_new UNION ALL
SELECT 'column26', U.column26_old, U.column26_new UNION ALL
SELECT 'column27', U.column27_old, U.column27_new UNION ALL
SELECT 'column28', U.column28_old, U.column28_new UNION ALL
SELECT 'column29', U.column29_old, U.column29_new UNION ALL
SELECT 'column30', U.column30_old, U.column30_new
) CA (column_nm, old, new)
ORDER BY U.row_id, CA.column_nm;
The quality of the answers is directly proportional to the quality of the question.
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Today @ 4:15 AM
Points: 2,069,
Visits: 2,040
|
|
Finally, here's my script for what I believe to be the absolute fastest possible way to populate the table and return the desired results:
The key point is that all the sorts are eliminated...
USE tempdb;
GO
-- Drop the temporary table if it exists
IF OBJECT_ID(N'tempdb..#Update', N'U') IS NOT NULL DROP TABLE #Update;
GO
-- Create the temporary table, complete with primary key
CREATE TABLE #Update
(
row_id BIGINT NOT NULL PRIMARY KEY CLUSTERED,
column01_old DATETIME NOT NULL,
column01_new DATETIME NOT NULL,
column02_old BIT NOT NULL,
column02_new BIT NOT NULL,
column03_old DECIMAL(10,0) NOT NULL,
column03_new DECIMAL(10,0) NOT NULL,
column04_old DATETIME NOT NULL,
column04_new DATETIME NOT NULL,
column05_old BIT NOT NULL,
column05_new BIT NOT NULL,
column06_old DECIMAL(10,0) NOT NULL,
column06_new DECIMAL(10,0) NOT NULL,
column07_old DATETIME NOT NULL,
column07_new DATETIME NOT NULL,
column08_old BIT NOT NULL,
column08_new BIT NOT NULL,
column09_old DECIMAL(10,0) NOT NULL,
column09_new DECIMAL(10,0) NOT NULL,
column10_old DATETIME NOT NULL,
column10_new DATETIME NOT NULL,
column11_old DATETIME NOT NULL,
column11_new DATETIME NOT NULL,
column12_old BIT NOT NULL,
column12_new BIT NOT NULL,
column13_old DECIMAL(10,0) NOT NULL,
column13_new DECIMAL(10,0) NOT NULL,
column14_old DATETIME NOT NULL,
column14_new DATETIME NOT NULL,
column15_old BIT NOT NULL,
column15_new BIT NOT NULL,
column16_old DECIMAL(10,0) NOT NULL,
column16_new DECIMAL(10,0) NOT NULL,
column17_old DATETIME NOT NULL,
column17_new DATETIME NOT NULL,
column18_old BIT NOT NULL,
column18_new BIT NOT NULL,
column19_old DECIMAL(10,0) NOT NULL,
column19_new DECIMAL(10,0) NOT NULL,
column20_old DATETIME NOT NULL,
column20_new DATETIME NOT NULL,
column21_old DATETIME NOT NULL,
column21_new DATETIME NOT NULL,
column22_old BIT NOT NULL,
column22_new BIT NOT NULL,
column23_old DECIMAL(10,0) NOT NULL,
column23_new DECIMAL(10,0) NOT NULL,
column24_old DATETIME NOT NULL,
column24_new DATETIME NOT NULL,
column25_old BIT NOT NULL,
column25_new BIT NOT NULL,
column26_old DECIMAL(10,0) NOT NULL,
column26_new DECIMAL(10,0) NOT NULL,
column27_old DATETIME NOT NULL,
column27_new DATETIME NOT NULL,
column28_old BIT NOT NULL,
column28_new BIT NOT NULL,
column29_old DECIMAL(10,0) NOT NULL,
column29_new DECIMAL(10,0) NOT NULL,
column30_old DATETIME NOT NULL,
column30_new DATETIME NOT NULL,
);
GO
-- Generate test rows (5,000 in this example)
-- There is *no sort* in the plan!
;WITH CTE AS
(
SELECT TOP (5000)
n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
master.sys.allocation_units A1,
master.sys.allocation_units A2,
master.sys.allocation_units A3
ORDER BY
n ASC
)
INSERT #Update
SELECT CTE.N,
DATEADD(MINUTE, CTE.N, '2000-01-01'), DATEADD(HOUR, CTE.N, '2000-01-01'), 0, 1, CTE.N + 0.1, CTE.N + 0.3,
DATEADD(MINUTE, CTE.N, '2000-01-01'), DATEADD(HOUR, CTE.N, '2000-01-01'), 0, 1, CTE.N + 0.1, CTE.N + 0.3,
DATEADD(MINUTE, CTE.N, '2000-01-01'), DATEADD(HOUR, CTE.N, '2000-01-01'), 0, 1, CTE.N + 0.1, CTE.N + 0.3,
DATEADD(MINUTE, CTE.N, '2000-01-01'), DATEADD(HOUR, CTE.N, '2000-01-01'),
DATEADD(MINUTE, CTE.N, '2000-01-01'), DATEADD(HOUR, CTE.N, '2000-01-01'), 0, 1, CTE.N + 0.1, CTE.N + 0.3,
DATEADD(MINUTE, CTE.N, '2000-01-01'), DATEADD(HOUR, CTE.N, '2000-01-01'), 0, 1, CTE.N + 0.1, CTE.N + 0.3,
DATEADD(MINUTE, CTE.N, '2000-01-01'), DATEADD(HOUR, CTE.N, '2000-01-01'), 0, 1, CTE.N + 0.1, CTE.N + 0.3,
DATEADD(MINUTE, CTE.N, '2000-01-01'), DATEADD(HOUR, CTE.N, '2000-01-01'),
DATEADD(MINUTE, CTE.N, '2000-01-01'), DATEADD(HOUR, CTE.N, '2000-01-01'), 0, 1, CTE.N + 0.1, CTE.N + 0.3,
DATEADD(MINUTE, CTE.N, '2000-01-01'), DATEADD(HOUR, CTE.N, '2000-01-01'), 0, 1, CTE.N + 0.1, CTE.N + 0.3,
DATEADD(MINUTE, CTE.N, '2000-01-01'), DATEADD(HOUR, CTE.N, '2000-01-01'), 0, 1, CTE.N + 0.1, CTE.N + 0.3,
DATEADD(MINUTE, CTE.N, '2000-01-01'), DATEADD(HOUR, CTE.N, '2000-01-01')
FROM CTE
ORDER BY n ASC;
GO
-- The final query (again no sort!)
SELECT U.row_id, CA.column_nm, CA.old, CA.new
FROM #Update U WITH (TABLOCK)
CROSS
APPLY (
SELECT 'column01', CONVERT(SQL_VARIANT, U.column01_old), CONVERT(SQL_VARIANT, U.column01_new) UNION ALL
SELECT 'column02', U.column02_old, U.column02_new UNION ALL
SELECT 'column03', U.column03_old, U.column03_new UNION ALL
SELECT 'column04', U.column04_old, U.column04_new UNION ALL
SELECT 'column05', U.column05_old, U.column05_new UNION ALL
SELECT 'column06', U.column06_old, U.column06_new UNION ALL
SELECT 'column07', U.column07_old, U.column07_new UNION ALL
SELECT 'column08', U.column08_old, U.column08_new UNION ALL
SELECT 'column09', U.column09_old, U.column09_new UNION ALL
SELECT 'column10', U.column10_old, U.column10_new UNION ALL
SELECT 'column11', U.column11_old, U.column11_new UNION ALL
SELECT 'column12', U.column12_old, U.column12_new UNION ALL
SELECT 'column13', U.column13_old, U.column13_new UNION ALL
SELECT 'column14', U.column14_old, U.column14_new UNION ALL
SELECT 'column15', U.column15_old, U.column15_new UNION ALL
SELECT 'column16', U.column16_old, U.column16_new UNION ALL
SELECT 'column17', U.column17_old, U.column17_new UNION ALL
SELECT 'column18', U.column18_old, U.column18_new UNION ALL
SELECT 'column19', U.column19_old, U.column19_new UNION ALL
SELECT 'column20', U.column20_old, U.column20_new UNION ALL
SELECT 'column21', U.column21_old, U.column21_new UNION ALL
SELECT 'column22', U.column22_old, U.column22_new UNION ALL
SELECT 'column23', U.column23_old, U.column23_new UNION ALL
SELECT 'column24', U.column24_old, U.column24_new UNION ALL
SELECT 'column25', U.column25_old, U.column25_new UNION ALL
SELECT 'column26', U.column26_old, U.column26_new UNION ALL
SELECT 'column27', U.column27_old, U.column27_new UNION ALL
SELECT 'column28', U.column28_old, U.column28_new UNION ALL
SELECT 'column29', U.column29_old, U.column29_new UNION ALL
SELECT 'column30', U.column30_old, U.column30_new
) CA (column_nm, old, new)
ORDER BY U.row_id
OPTION (MAXDOP 1);
I have coded this example for 5K rows (my laptop is no match for Wayne's machine!) but nothing changes with 1M rows...or more.
Paul
The quality of the answers is directly proportional to the quality of the question.
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 4:58 PM
Points: 2,279,
Visits: 2,832
|
|
Wow... I wander off and enjoy the PASS Summit and come back to find this !? Why don't one of you guys take this opportunity to write a "Beyond Unpivot" article?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Whenever possible, I prefer to eat the rude. -- Hannibal Lecter
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Today @ 4:15 AM
Points: 2,069,
Visits: 2,040
|
|
Hey Bob,
I think we pretty much did already 
Glad you enjoyed PASS.
Paul
The quality of the answers is directly proportional to the quality of the question.
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 4:58 PM
Points: 2,279,
Visits: 2,832
|
|
I'll say you did. The end result is sweet, too.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Whenever possible, I prefer to eat the rude. -- Hannibal Lecter
|
|
|
|
