Post reply

Did you say denormalize, why?

  • April 24, 2012 at 8:02 pm

    #256964

    Comments posted to this topic are about the item Did you say denormalize, why?

    Cheers,
    John Esraelo

  • April 25, 2012 at 7:12 am

    #1478945

    We have code similar to this to manage EAV tables.*

    I believe the dynamic SQL executing DDL in a loop reduces your database to a very expensive scripting environment for running procedural code.

    I would propose moving this operation outside the database to either a reporting engine or a scripting/app tier. I can appreciate that this posted solution "works" - but I questions whether this kind of code should be allowed. I know, it's a single ad-hoc solution... but this technique can quickly become standard procedure. When there are a dozen developers writing ad-hoc solutions into production, you may be paying a high price for such 'convenience.'

    *

  • April 25, 2012 at 9:21 am

    #1479100

    You are absolutely right.

    What I should have added as a comment or a warning is that .. "... this is for training purposes and should not be considered as a standard practice as it already violates several normalization norms and rules."

    Of course as part of the title of this posting indicated that "WHY".

    I leave the judgement to you folks as experts to allow / disallow and I do appreciate the comment actually.

    Cheers,
    John Esraelo

  • April 25, 2012 at 1:28 pm

    #1479310

    Couldn't you also accomplish the same thing using the PIVOT command? Wouldn't that perform better?

  • April 25, 2012 at 3:33 pm

    #1479381

    This is just a sample piece to demonstrate how to "denormalize" data.

    Really a fun thing to play with and not taken seriously as a practical script for production.

    As a matter of fact I am not fund of using "cursors" in large heavily used tables as will largely affect the performance.

    What you are referring to is perhaps Matrix / cross-tab / pivot that always requires some sort of a quantifiable / statistical value in the middle which this script does not care about that.

    But, by all means, please share with us if you find a PIVOT method that does denormalize 2 or more tables into 1.

    This result may also be achieved in SSAS. But WHY? 😉

    have fun!

    Cheers,
    John Esraelo

  • April 27, 2012 at 5:20 am

    #1480296

    John,

    Thanks for the article, interesting read. We had a similar item here not long ago for a customized report, and I had the developer create a dynamic Pivot statement to achieve the results he needed using a string. This solution appears to produce similar results, but can be made more dynamic.

    As you mentioned, I too detest the use of cursors and would change that to be a simple loop.

    Whether or not this kind of script should be used in production is up to the developer (and their dba) but it provides great food for thought.

    ________________________________________________

    We passed upon the stair - and I was that man who sold the world

  • April 30, 2012 at 12:13 pm

    #1481398

    Thank you for the comment and yes, you are right, it depends on the individual scenario / special cases / etc. and that traffic and the IO is not too bad and can be displaying the data in that fashion.

    Cheers,
    John Esraelo

  • May 10, 2016 at 1:55 pm

    #1877969

    Thanks for the info.

Viewing 8 posts - 1 through 7 (of 7 total)

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