Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2012
    Posts
    2

    Unanswered: Normalization and Query Performance

    Hello Guys,
    Basically I am doin a basic course in Database Design Management System in my BS I have read about normalization but had a problem in it. I want U guys to help me I need some technical specification regarding this topic.

    My Query is:
    "Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency. but as it removes anomalies form data it effect query performance and why does this happen???"

    Take care

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK...WHO is teaching this course

    They are going to say that the normalized model will slow down data access do to the number of joins associated with retrieving the data

    That may be, but a denormalized structure INCREASES the risk of having data corruptions...so the speed you may incur (MAY) is offset by data corruption

    So, if the bozo's want to say that retrieving data from a single table than many is faster...fine..can't argue with that...well maybe you could...but at what COST?

    Bozo's

    And not for nothing...ALL of my sprocs are sub second...even in the thousands of seconds...I know.. because I log their execution

    Now...if Professor Bozo wants you to retreive 50,000 rows for a report, then there is something wrong with Professor bozo

    ALSO..it ONLY Affects performance if the tables are not properly Indexed
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    And retrieving data from a single table is not necessarily faster.
    A denormalized dataset means a wider table, which means fewer records per page, which means more time required to search for individual pages. It also mean filtering by string-based values rather than more efficient numeric surrogate keys.
    So, there are performance trade-offs either way, but the relational integrity of a normalized schema trumps all.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman View Post
    ... relational integrity of a normalized schema trumps all.
    agreed

    but a little disappointed you used a bridge metaphor instead of chess
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    mates all?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Rizz84 View Post
    Hello Guys,
    Basically I am doin a basic course in Database Design Management System in my BS I have read about normalization but had a problem in it. I want U guys to help me I need some technical specification regarding this topic.

    My Query is:
    "Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency. but as it removes anomalies form data it effect query performance and why does this happen???"

    Take care
    In general, as the data becomes more normallized performance improves. There are many factors that drive this performance increase, first of which is that normallized data takes less storage so more usable data can be held in faster storage than a non-normalized schema will allow. An important side effect is that queries against a normalized schema are statistically more likely to be logically correct so the user gets the answer on the first try instead of after multiple attempts and QA efforts.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You and I BOTH know that this is NOT The answer the Prof is looking for, however CORRECT it is

    It's a pervasive perverted assessment

    http://www.lcard****/~nail/sybase/perf/1088.htm

    The answer the Prof wants is

    "It impove pe-formANCE...AH YUP....bobby jean...get me my corn cob pipe"
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Normalization has nothing to do with performance because normalization defines only the logical features of the database (tables, attributes, keys, dependencies). Normalization says nothing about what the database looks like in physical storage or what optimizations can be applied to queries on it.

    If and when you find that some schema change due to normalization affects query performance then that's because of insufficient physical database independence in the DBMS concerned. In other words the DBMS made some unwanted physical change even when you only required a logical change to the schema.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by dportas View Post
    In other words the DBMS made some unwanted physical change even when you only required a logical change to the schema.

    Can I get a hit?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jan 2012
    Posts
    2
    Quote Originally Posted by Pat Phelan View Post
    In general, as the data becomes more normallized performance improves. There are many factors that drive this performance increase, first of which is that normallized data takes less storage so more usable data can be held in faster storage than a non-normalized schema will allow. An important side effect is that queries against a normalized schema are statistically more likely to be logically correct so the user gets the answer on the first try instead of after multiple attempts and QA efforts.

    -PatP
    This is what I am lookin for I think every one here is interpenetrating that it effect performance in a negative way but I personally think normalization increases performance Like only 1NF improves operations like SELECT and JOIN as search operations are reduced...I will really appreciate if some one will point me to the information It effect performance means It improves it as a byproduct and thats all...

    My english is terrible so don mind it

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    They are going to say that querying 1 table is faster the querying 10

    I'm not so sure that is case
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •