September 10, 2012 at 9:39 am
Lynn Pettis (9/7/2012)
Phil Parkin (9/7/2012)
Lynn's solution is the way to go.In her solution, basedata is a CTE - effectively a subquery in this case - from which the normalised data is being selected.
If you wanted to do the entire thing in SSIS, that is also possible, though quite advanced, through the use of an asynchronous Script Component. Advanced ... and slower - but possible.
Should read:
In
herhis solution, basedata is a CTE - effectively a subquery in this case - from which the normalised data is being selected.Not a biggie, happens rather frequently.
Eek, I apologise for this!:blush:
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 10, 2012 at 9:55 am
ScottPletcher (9/10/2012)
The UNPIVOT (one table scan) looks much better to me than fully scanning the table once for every grade column.
Probably, but I am still new at writing pivot/unpivot queries so I will leave that one to others.
September 10, 2012 at 9:57 am
Phil Parkin (9/10/2012)
Lynn Pettis (9/7/2012)
Phil Parkin (9/7/2012)
Lynn's solution is the way to go.In her solution, basedata is a CTE - effectively a subquery in this case - from which the normalised data is being selected.
If you wanted to do the entire thing in SSIS, that is also possible, though quite advanced, through the use of an asynchronous Script Component. Advanced ... and slower - but possible.
Should read:
In
herhis solution, basedata is a CTE - effectively a subquery in this case - from which the normalised data is being selected.Not a biggie, happens rather frequently.
Eek, I apologise for this!:blush:
Like I said, not a problem. It happens regularly with me, in fact it happened this weekend before a soccer game I was officiating. One of the other refs though we were waiting for a lady to join us while the third official was working the game before ours.
September 10, 2012 at 12:30 pm
Why not start with something like this to build your query or inserts around...?
SELECT name,addy,ph,subject,grade
FROM #test CROSS APPLY (VALUES('subject 1',grade1),('subject 2',grade2),('subject 3',grade3),('subject 4',grade4),('subject 5',grade5)) AS x(subject,grade)
September 10, 2012 at 4:14 pm
ScottPletcher (9/10/2012)
The UNPIVOT (one table scan) looks much better to me than fully scanning the table once for every grade column.
Yep, as I said, I put that out there for academic purposes only, as it can be useful. However, for this situation, I would probably go with Lynn's solution.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
September 10, 2012 at 4:18 pm
Greg Snidow (9/10/2012)
ScottPletcher (9/10/2012)
The UNPIVOT (one table scan) looks much better to me than fully scanning the table once for every grade column.Yep, as I said, I put that out there for academic purposes only, as it can be useful. However, for this situation, I would probably go with Lynn's solution.
Why? Is there any advantage to that method, which requires multiple scans of the table rather than a single one for UNPIVOT?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 10, 2012 at 4:24 pm
ScottPletcher (9/10/2012)
Greg Snidow (9/10/2012)
ScottPletcher (9/10/2012)
The UNPIVOT (one table scan) looks much better to me than fully scanning the table once for every grade column.Yep, as I said, I put that out there for academic purposes only, as it can be useful. However, for this situation, I would probably go with Lynn's solution.
Why? Is there any advantage to that method, which requires multiple scans of the table rather than a single one for UNPIVOT?
I would say that there is an opportunity for another test, wouldn't you? Perhaps you will take this one as I still have one to perform.
September 10, 2012 at 4:28 pm
Lynn Pettis (9/10/2012)
ScottPletcher (9/10/2012)
Greg Snidow (9/10/2012)
ScottPletcher (9/10/2012)
The UNPIVOT (one table scan) looks much better to me than fully scanning the table once for every grade column.Yep, as I said, I put that out there for academic purposes only, as it can be useful. However, for this situation, I would probably go with Lynn's solution.
Why? Is there any advantage to that method, which requires multiple scans of the table rather than a single one for UNPIVOT?
I would say that there is an opportunity for another test, wouldn't you? Perhaps you will take this one as I still have one to perform.
I don't see any need for a test. One full table scan per column can't possibly compete with a single table scan for everything.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 10, 2012 at 4:50 pm
ScottPletcher (9/10/2012)
Greg Snidow (9/10/2012)
ScottPletcher (9/10/2012)
The UNPIVOT (one table scan) looks much better to me than fully scanning the table once for every grade column.Yep, as I said, I put that out there for academic purposes only, as it can be useful. However, for this situation, I would probably go with Lynn's solution.
Why? Is there any advantage to that method, which requires multiple scans of the table rather than a single one for UNPIVOT?
Probably the main reason, albeit not a valid one, is, similar to what Lynn said, I've never used PIVOT/UNPIVOT, I just happened to help someone with PIVOT on another forum a couple of days ago, and this question seemed like a good opportunity to try UNPIVOT. Other than that I don't know anything about it from a performance perspective. Sounds like a good opportunity for a million row test. Are there any indexes on the table in question?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
September 10, 2012 at 5:04 pm
ScottPletcher (9/10/2012)
Lynn Pettis (9/10/2012)
ScottPletcher (9/10/2012)
Greg Snidow (9/10/2012)
ScottPletcher (9/10/2012)
The UNPIVOT (one table scan) looks much better to me than fully scanning the table once for every grade column.Yep, as I said, I put that out there for academic purposes only, as it can be useful. However, for this situation, I would probably go with Lynn's solution.
Why? Is there any advantage to that method, which requires multiple scans of the table rather than a single one for UNPIVOT?
I would say that there is an opportunity for another test, wouldn't you? Perhaps you will take this one as I still have one to perform.
I don't see any need for a test. One full table scan per column can't possibly compete with a single table scan for everything.
And that is exactly why you should run a million row test. Prove that UNPIVOT is the better solution. A developer must know, not guess. Hmmm, where have I heard that before?
September 10, 2012 at 5:09 pm
Lynn Pettis (9/10/2012)
ScottPletcher (9/10/2012)
Lynn Pettis (9/10/2012)
ScottPletcher (9/10/2012)
Greg Snidow (9/10/2012)
ScottPletcher (9/10/2012)
The UNPIVOT (one table scan) looks much better to me than fully scanning the table once for every grade column.Yep, as I said, I put that out there for academic purposes only, as it can be useful. However, for this situation, I would probably go with Lynn's solution.
Why? Is there any advantage to that method, which requires multiple scans of the table rather than a single one for UNPIVOT?
I would say that there is an opportunity for another test, wouldn't you? Perhaps you will take this one as I still have one to perform.
I don't see any need for a test. One full table scan per column can't possibly compete with a single table scan for everything.
And that is exactly why you should run a million row test. Prove that UNPIVOT is the better solution. A developer must know, not guess. Hmmm, where have I heard that before?
So does one also need to run tests to prove that:
SELECT a, b, c, d, e FROM dbo.table1
is more efficient than:
SELECT a, null, null, null, null FROM dbo.table1 UNION ALL
SELECT null, b, null, null, null FROM dbo.table1 UNION ALL
SELECT null, null, c, null, null FROM dbo.table1 UNION ALL
SELECT null, null, null, d, null FROM dbo.table1 UNION ALL
SELECT null, null, null, null, e FROM dbo.table1
??
Because that's a roughly equivalent comparison.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 10, 2012 at 8:21 pm
ScottPletcher (9/10/2012)
So does one also need to run tests to prove that:SELECT a, b, c, d, e FROM dbo.table1
is more efficient than:
SELECT a, null, null, null, null FROM dbo.table1 UNION ALL
SELECT null, b, null, null, null FROM dbo.table1 UNION ALL
SELECT null, null, c, null, null FROM dbo.table1 UNION ALL
SELECT null, null, null, d, null FROM dbo.table1 UNION ALL
SELECT null, null, null, null, e FROM dbo.table1
??
Because that's a roughly equivalent comparison.
Maybe. All I'm saying is that I don't know anything about PIVOT/UNPIVOT. Although I have not tested it, it might only be doing one table scan, but what else is it doing? It could be you are absolutely correct, but were I the OP, I would certainly want to test it for myself.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
September 10, 2012 at 8:54 pm
I'm not sure about performance from Lynn's solution against UNPIVOT.
But there's another solution in here that has been proved to be better than UNPIVOT by Dwain in this article.
http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
Could that be enough? Or someone is willing to make a complete test?
September 11, 2012 at 11:47 am
Set up a quick test here as well:
UNION method:
Elapsed Time (ms) 1339
UNPIVOT method:
Elapsed Time (ms) 1181
Ran it several times with similar results. Sorry, didn't set up a test for Dwain Camps method.
September 11, 2012 at 11:58 am
Something changed on my VM, but not sure what because I didn't change anything. All of a sudden this is the normal results:
UNION method:
Elapsed Time (ms) 2665
UNPIVOT method:
Elapsed Time (ms) 1257
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply