Post reply

Reporting Tables (Denormalize tables)

  • November 5, 2007 at 3:26 am

    #120945

    Hi guys!

    I'm doing a report table which is a denormalize version of the transactional tables. The requirement doesn't require real time data on reports so I think that doing a seperate table for reports to read on is not an issue.

    Denormalizing the transactional tables means that there will be more fields/columns on the table, so do I really still need to consider the 8060 bytes limit per row or could I just place all the fields on a single table eventhough it would exceed the limit?

    Any tips?

    Thanks in advance.

  • November 5, 2007 at 3:47 am

    #748163

    table created successfully with some warning message. Try it.

  • November 5, 2007 at 4:11 am

    #748170

    Hi Kishore,

    That's what actually adding to my confusion, it was showing a warning message but the table is created actually. What does this warning message implies? Would there be any values truncated from any columns? Would I still be able to SELECT all this columns?

    Let say i have 3 columns:

    Col1 nvarchar(4000)

    Col2 nvarchar(4000)

    Col3 nvarchar(4000)

    These three columns are filled up with data that is almost the max of their capacity.

    Would it still be possible to SELECT the three of them at one time without any truncation?

    Sorry for asking this, I don't have SQL 2000 on this desktop, I only have SQL 2005, and as I've read in one article here, SQL 2005 handles the row limit size differently(e.g. not showing any warning message if table row created exceed the limit).

    Thanks.

  • November 5, 2007 at 4:39 am

    #748172

    In SQL 2005 have no problem, but have problem in SQL 2000 and below versions. so better to move SQL Server 2005.

    🙂

  • November 5, 2007 at 5:04 am

    #748180

    In SQL 2000, maximum row-size limit is 8060 (actually, it is a little less than what actually documented), this means you cannot insert rows with row-sizes greater than the limit.

    When try to insert, it raises an error saying the row-size exceeds the limit and the statement has been terminated.

    But in 2005, this limitation is longer exists for variable length columns (but the limit is applicable to tables with only fixed length columns).

    --Ramesh

  • November 5, 2007 at 6:34 am

    #748207

    Anyone there who have done report tables in SQL 2000? How did you denormalized your tables?

    I'll be doing some test now. Thanks for the input guys.

  • November 5, 2007 at 6:46 am

    #748211

    BTW, just a note, the limitation still exists in SQL 2005

    http://qa.sqlservercentral.com/articles/Development/2862/

  • November 5, 2007 at 1:58 pm

    #748410

    wackoyacky (11/5/2007)

    Anyone there who have done report tables in SQL 2000? How did you denormalized your tables?

    I'll be doing some test now. Thanks for the input guys.

    Why tables?

    Views work just fine.

    And it's actually way faster to select from view than from fat table with heaps of repeating character fields.

    _____________
    Code for TallyGenerator

  • November 5, 2007 at 6:21 pm

    #748463

    Hi Serg,

    But wouldn't it be an additional hit on the transactional tables, especially if the traffic on that table is very high? But I will consider it as an option. Thanks!

  • November 5, 2007 at 8:56 pm

    #748484

    wackoyacky (11/5/2007)

    Hi Serg,

    But wouldn't it be an additional hit on the transactional tables, especially if the traffic on that table is very high? But I will consider it as an option. Thanks!

    You gonna have to populate you "report" table anyway, right?

    View will create exactly the same hit on those tables, except it will add to the locking time because you will need to write the data into another table.

    And because no writing involved in views Server not gonna start transaction - no recording into LOG file, another resource saved, another boost for performance.

    _____________
    Code for TallyGenerator

  • November 6, 2007 at 12:07 am

    #748526

    wackoyacky (11/5/2007)

    BTW, just a note, the limitation still exists in SQL 2005

    http://qa.sqlservercentral.com/articles/Development/2862/%5B/quote%5D

    As I said earlier, the limit only applies for fixed length columns....(i.e. the total length of fixed length columns)

    --Ramesh

  • November 7, 2007 at 7:59 am

    #749108

    The objective of denomralising tables, is to reduce the number of joins to tables. Transactional tables are normalised and designed for speed of writing small amounts of data to the tables. Reporting tables are designed for fast reads, and that is why de-normalising the table is better for reports.

    My advice would be to create a new reporting database that creates a star schema structure for your reports.

    Hope that makes sense.

    Pete

    Peter Gadsby
    Business Intelligence Consultant
    www.hgconsult.co.uk

  • November 7, 2007 at 8:24 am

    #749119

    Ramesh (11/6/2007)

    wackoyacky (11/5/2007)

    BTW, just a note, the limitation still exists in SQL 2005

    http://qa.sqlservercentral.com/articles/Development/2862/%5B/quote%5D

    As I said earlier, the limit only applies for fixed length columns....(i.e. the total length of fixed length columns)

    I am confused, like in SQL 2000 the mazimum on row length is 8K in 2005. You can create a table with dimenions in both and SQL 2000 will complain but still create the table. But in either case if you try to enter data into any combination of the columns and it exceeds 8k it will still reject the row. To my knowledged SQL 2005 will still reject the row but just doesn't complain about the lenght when using variable length datatypes.

    As for how I do these types of things

    1) Usually don't bother to denormalize unless I see an impact on perfomance.

    2) If I see and impact I may replicate the data to another database (could be on the same server) for reporting purposes. In most cases I use Transactional replication but in the case of not real time I may do an after-hours merge of snapshot.

    3) Only denormalize when I can see a particular query is being burdened by the joins. But honestly I have only had 2 cases and that was under SQL 7 that it was obvious, otherwise I found I could refactor the query a bit and correct the speed in most every case.

    The issue is how you impact your server and many times the best choice for non-realtime reports is another server to handle those request.

  • November 7, 2007 at 8:51 am

    #749133

    I agree, a reporting solution is not usually compatibile with transactional solution as the amount of data each are using is different. For example a large report may read 1 million rows, but a transactional system may only require an update to 1/2 records at a time. Also if a large running reporting query is reading a million rows from a table it will start to escalate it's read locks from row --. page --> table which will delay transactional reads/writes.

    Pete

    Peter Gadsby
    Business Intelligence Consultant
    www.hgconsult.co.uk

  • November 8, 2007 at 7:46 pm

    #749699

    Thanks pete you've explain clearly why I was doing the report tables.

    Thanks crazyeights for sharing your way of handling tables for reporting.

    To my knowledged SQL 2005 will still reject the row but just doesn't complain about the lenght when using variable length datatypes.

    I tried doing an experiment, I have 3 columns all of nvarchar(4000) type, I updated each fields on 3 separate statement and fill it up with data which is almost 4k, suprisingly the row was not rejected. Is it because I did the update on each field separately?

Viewing 15 posts - 1 through 15 (of 16 total)

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