1 / 16

Physical Database Design Data Migration/Conversion

Physical Database Design Data Migration/Conversion. What is physical DB design?. The organization of the internal data model to strike a balance between performance and usability. a.k.a “When to De-Normalize”. Factors which Impact Physical Design. Structure – table and relationship count

swelk
Download Presentation

Physical Database Design Data Migration/Conversion

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Physical Database DesignData Migration/Conversion

  2. What is physical DB design? The organization of the internal data model to strike a balance between performance and usability. a.k.a “When to De-Normalize”

  3. Factors which Impact Physical Design • Structure – table and relationship count • Volume – quantity of data • Volatility – rate of change of data • Input Mode – how does data gets in • Storage Format – data type selection • Retrieval – optimizing data retrieval

  4. Structure

  5. Volume • Volume determines DBMS selection. Right-size your DBMS.

  6. Volatility • The more volatile your data, the more hardware resources should be dedicated to it. • DBMS Selection should also play a factor.

  7. Input Mode • High input modes need simplified table designs and more hardware resources.

  8. Storage Format • Right-sized column data types. • Int vs. varchar • Date vs. varchar • Accuracy vs. performance – (Defaults, check constraints )

  9. Retrieval • Index – a thread over columns in a table which speed up searches over it. • Cluster – physically grouping rows of data in the same physical block on disk. • Improving search at the expense of insert/update

  10. Indexing Rule of Thumb Use Indexes Avoid Indexes There are relatively few rows in the table The column is not used for searching The majority of the queries that retrieve more than 2% - 4% of the table’s rows There is high insert or update transaction volatility • The table contains over 100,000 rows • The searchable field (indexed column) has a wide range of values • The searchable field has a large number of null values • The searchable field is queried frequently • Queries retrieve less than 2% - 4% of the table’s rows CREATE [UNIQUE] [NONCLUSTERED] INDEX index_name ON table_name (column_name [ASC|DESC][,…n])

  11. Data Migration / Conversion

  12. What is Data Migration / Conversion? • Data Migration – the act of moving data from one system to another • Data Conversion – the act of moving data from one format to another. Typically as part of upgrading systems you need to do both.

  13. Common Types • In Place – all activities done on the one DMBS • Eg. Upgrade wordpress from 3.0 to 3.0.1 • Parallel – Both system running at the same time, with final cut-over. • Eg. Upgrade from Blackboard 8 to 9 • Phased – parts of the system are migrated in phases. (Accompanies in-place or parallel)

  14. The Process

  15. SQL Example • Case study example using this:

More Related