Best table structure for a tall table that needs to be pivoted

Question

Best table structure for a tall table that needs to be pivoted

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 1

Dolfandave wrote:

Yes and the Value field is varchar(02), it needs to hold "1", "2", or "NA".

It would save 2/3rds of the memory required if it could be a numeric 0, 1, 2 (0 or NULL for "NA") and a third of the memory if it could be a CHAR(2) instead of a VARCHAR(2) to hold your "1", "2", of "NA" requirements. That also means more rows per page for faster processing. Any chances of either of those happening?

And, to be sure, you don't have to search for something like a "1 " (1 followed by a blank) because trailing spaces are simply ignored for equality and in-equality searches and aggregations. Only the use of LIKE would cause an issue and I can't see that happening here.

This reply was modified 2 years ago by Jeff Moden. Reason: Corrected the savings estimates

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 2

Damn forum software ate my response due to paging... hopefully, this post will make it appear.

Yep... that did the trick.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dolfandave SSC Enthusiast Points: 179 More actions · Answer 3

Dolfandave

SSC Enthusiast

Points: 179

December 24, 2022 at 11:33 pm

#4129910

Ok, thanks again. Happy Holidays.

Jonathan AC Roberts SSCoach Points: 18363 More actions · Answer 4

Jeff Moden wrote:

Dolfandave wrote:
Yes and the Value field is varchar(02), it needs to hold "1", "2", or "NA".
It would save 2/3rds of the memory required if it could be a numeric 0, 1, 2 (0 or NULL for "NA") and a third of the memory if it could be a CHAR(2) instead of a VARCHAR(2) to hold your "1", "2", of "NA" requirements. That also means more rows per page for faster processing. Any chances of either of those happening?
And, to be sure, you don't have to search for something like a "1 " (1 followed by a blank) because trailing spaces are simply ignored for equality and in-equality searches and aggregations. Only the use of LIKE would cause an issue and I can't see that happening here.

He could even make it CHAR(1) and store 'NA' as 'N'. Or could make it a BIT and store 'NA' as NULL. If there are a lot of NULLs it would save space to make the columns SPARCE.

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 5

Jonathan AC Roberts wrote:

Jeff Moden wrote:
Dolfandave wrote:
Yes and the Value field is varchar(02), it needs to hold "1", "2", or "NA".
It would save 2/3rds of the memory required if it could be a numeric 0, 1, 2 (0 or NULL for "NA") and a third of the memory if it could be a CHAR(2) instead of a VARCHAR(2) to hold your "1", "2", of "NA" requirements. That also means more rows per page for faster processing. Any chances of either of those happening?
And, to be sure, you don't have to search for something like a "1 " (1 followed by a blank) because trailing spaces are simply ignored for equality and in-equality searches and aggregations. Only the use of LIKE would cause an issue and I can't see that happening here.
He could even make it CHAR(1) and store 'NA' as 'N'. Or could make it a BIT and store 'NA' as NULL. If there are a lot of NULLs it would save space to make the columns SPARCE.

The columns would need to be more than 60% NULL to make it even break even because 4 bytes are added to every non-Sparse value, not to mention the "Hey, this one isn't Sparse" computational overhead associated with Sparse columns.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 6

@dolfandave ,

It sounds like the dynamic technique of using a CROSSTAB would fit the bill here. Have a look at the following article as an introduction and let me know what you think.

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

Instead of SUM(), we'd use MAX(). Instead of using CASE, we could use IIF to shorten up the code (although IIF resolves to CASE behind the scenes. That's not an issue here but worth mentioning because it results in no performance improvement... just shorter code).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18363 More actions · Answer 7

Jeff Moden wrote:

Jonathan AC Roberts wrote:
Jeff Moden wrote:
Dolfandave wrote:
Yes and the Value field is varchar(02), it needs to hold "1", "2", or "NA".
It would save 2/3rds of the memory required if it could be a numeric 0, 1, 2 (0 or NULL for "NA") and a third of the memory if it could be a CHAR(2) instead of a VARCHAR(2) to hold your "1", "2", of "NA" requirements. That also means more rows per page for faster processing. Any chances of either of those happening?
And, to be sure, you don't have to search for something like a "1 " (1 followed by a blank) because trailing spaces are simply ignored for equality and in-equality searches and aggregations. Only the use of LIKE would cause an issue and I can't see that happening here.
He could even make it CHAR(1) and store 'NA' as 'N'. Or could make it a BIT and store 'NA' as NULL. If there are a lot of NULLs it would save space to make the columns SPARCE.
The columns would need to be more than 60% NULL to make it even break even because 4 bytes are added to every non-Sparse value, not to mention the "Hey, this one isn't Sparse" computational overhead associated with Sparse columns.

Yes, Sparse columns are not such a good idea, but using a bit instead of 2 bytes will have a huge space saving.

ScottPletcher SSC Guru Points: 101123 More actions · Answer 8

Definitely use char(2) for the values, as suggested (varchar requires two bytes of extra overhead (to store length) per column).

If they're not already encoded, encode the names. That is, "Code1" becomes a smallint (or int if necessary), =1, with a separate lookup table to convert the numbers to "Code1", "Code2", etc..

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".

Jonathan AC Roberts SSCoach Points: 18363 More actions · Answer 9

ScottPletcher wrote:

Definitely use char(2) for the values, as suggested (varchar requires two bytes of extra overhead (to store length) per column).
If they're not already encoded, encode the names. That is, "Code1" becomes a smallint (or int if necessary), =1, with a separate lookup table to convert the numbers to "Code1", "Code2", etc..

Wouldn't it be better to use a bit for the values if only three values 1, 2 and NA (NULL) are required?

The Code1, Code2 etc. are going to be the column names in the table so I can't see the point in having a lookup table to convert them to numbers?