3

I've normalized a single table to reduce the number of nullable columns from 3 down to 0. The idea was to save a little drive space but I'm seeing a 45% decrease in space needed to store the same amount of data between the two schemes but it just seems too good to be true.

Am I calculating or going about this incorrectly?

First I'm using this query to determine size of the tables, so its possible this is the problem.

There is a total of 88324 rows. 1187 are considered root posts and have values for Title, ProductId, TotalViews and have a NULL for ParentPostId. 87137 are considered replies and have NULL for Title, ProductId, TotalViews and have a value for ParentPostId.

At any rate the query, linked to above, outputs the following results in Kilobytes which equates to a 45% improvement which seems to big of an improvement to me.

+---------+--------+
|Current  |Future  |
+---------+--------+
|94,936   |42,784  |
+---------+--------+

My table structure went from a current state of:

enter image description here

to future state of:

enter image description here

1
  • Seems like a we thought out thing to do. What sort of answer are you expecting? "Yes"? :P Implement it. Not as if you're dealing with a pen drive worth of data
    – Philᵀᴹ
    Commented Jan 31, 2014 at 1:10

2 Answers 2

1

Turned out that the query I was using to calculate space combines indexes and data sizes into one. I used the sp_spaceused procedure as well as the below query to track down the the vast size discrepancy to two very large indexes on the current state table that I think were created by the tuning adviser. Those two indexes accounted for 60Mb!!, twice the size of the actual data, lol.

Without those huge indexes current state performs horribly while on the other hand future state doesn't require any additional indexes to perform as fast if not faster. Future state only added about 1.7Mb increase in storage needs which is far below the current states requirements.

query to find index sizes:

SELECT i.[name] AS IndexName
      ,SUM(s.[used_page_count]) * 8 AS IndexSizeKB 
FROM sys.dm_db_partition_stats AS s 
JOIN sys.indexes AS i 
ON s.[object_id] = i.[object_id] 
AND s.[index_id] = i.[index_id] 
GROUP BY i.[name] 
ORDER BY i.[name] 
GO

result shows two huge indexes:

query result

sp_spaceused 'Post_Old' result confirms that the those two indexes account for the discrepancy:

sp_spaceused

0

Your denormalised environment may have indexes you're not considering, to help performance... I doubt you're comparing like with like right now.

There is a price to pay in joining those tables, and you may find that heavy querying is a higher load now. But inserts and updates are going to be nicer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.