Vectors and 3D Models
Local Search - Computers
Weather Information
Get the App for Smartphones and Tablets

Go Back


WhmSoft Free Articles Directory
Free Articles for Reprint
Free Articles to Publish
Free Articles for Newsletters
Videos to Watch


Page Generation Date and Time:
09/20/2017 15:01:41

 
Free the Animation VR / AR
Play to reveal 3D images and 3D models!
   
 
vlrPhone / vlrFilter
Project of very low consumption, radiation and bitrate softphones, with the support of the spatial audio, of the frequency shifts and of the ultrasonic communications / Multifunction Audio Filter with Remote Control!



 
 
Alexa Data
 

Go To Articles Directory Home Page

To get the current article, - See Below (at the bottom of the page) -.
For top news titles, see below.
Web sites and videos listed in this page are frequently updated.
If you find that this page is useful (quality of web sites, images and videos, ...), you can add it to your favorites.
Bookmark Page !

Tell a Friend:



With your mobile phone (WAP / I-Mode / iPhone / PDA), for free:
The Top News - http://www.whmsoft.net/services/wap/news.php
The Daily Files - http://www.whmsoft.net/services/wap/get.php
All the Directory Files - http://www.whmsoft.net/services/wap/choose.php

Web version of feeds:
Podcast Music - http://www.whmsoft.net/services/web/wpodcast.php
Daily Files - http://www.whmsoft.net/services/web/wget.php

You can play the Guitar Drum Revolution game (flash game) by following the link below:
Play Guitar Drum Revolution Game


You can play free online games (flash games) by following the link below:
Free Online Games

Play the samples below:
01171 Flashman00329 Conquesia1303 Lance La HacheBlast EmFarm Frenzy

You can view the people (celebrities) news and the front page news (with videos, images and constant updates) by following the link below:
View Recent News
or by visiting the WhmSoft Service blog:
News Photos Slideshows


Article Keyword Videos to Watch
Computers
Click on the image to start the video.



Related Topics
Images - Links - Articles

Berlin


Related Images



Article Category Videos to Watch
Computers
Go to the Videos Pages


How to speed up a database which has gotten slow

Preface While the actual database design usually is able to handle the amount of stored data without major performance problems newly added clients and their data volume changes that. Once the size of a database grows close to 1 GB, the number of concurrent user exceeds 20 or the number of new a-records goes beyond 200 an hour, the need for speed improvement is raised. This document is not in search for reasons why a database is designed the way it is. In general there are two types of improvements involved: Direct and indirect. * Direct improvement by means of applying a database design. * Indirect by changing the way the client applications request data from the database. It has to be understood that direct changes always force changes in the way client applications access the data. This document only considers direct changes. Note: The best way to get performance is to pay up-front for tuning. It is not a hidden cost if included in the initial design effort and is exponentially cheaper than performing tuning as an add-on feature. Put simply, tuning involves three basic activities: 1. Eliminating unnecessary processing. 2. Eliminating redundant processing. 3. Using more efficient processing in exchange for less efficient processing. Usually you start of with the existing diagram of your database. Oops - you do not have one? What about reverse engineering? Most case tools offer that option. Unfortunately too often reversed-engineered diagrams of existing databases are an unreadable mess of 60 pages or more. This easily proves that databases grow into far too complex systems that do not allow the easy use of a case tool anymore. It is recommended to take the proper steps to not only simplify the database but to also force the existence of an easily readable and up-to-date diagram. "Performance Design Review" should be part of both the "Database Design Review" and "Application Design Review" processes. These are experiences while working with many different databases sized 2 GB up to 100 GB. Some of the information in this document can be found on the Internet as others faced the same challenges. To simplify things blob fields and large varchar field (> 254 characters, but mainly > 2048 characters) will be named blobs and seen as synonym. There are two major times when the behavior of databases changes: Once the database hits the 2GB size and then once again when the DB grows over 10GB. Once a table hits more then 10 million rows there will be changes in the behavior too, but they are less compared to the 2 GB size. And usually they appear around the same time, especially in non normalized databases. Some of the speed problems appear earlier, but they are usually covered by the use of expensive hardware or spending lots of time to fine tune the used hardware and/or software. Once a database exceeds the 2GB there will be a few others - name them natural - issues: * Backing up and Restoring a database becomes a difficult task, especially when the DB is supposed to be running in a 24/7 environment. * When containing blobs the DB will grow exponentially faster with the growing size of the DB. * When containing blobs databases tend to crash more often and get corrupted faster. Redundancy of data Some of the described speed improvements will lead to more redundancy of data. While this is an unwanted side effect the improved speed makes up for it. Possible Optimizations There are quite a few major areas of improvement, which can gain more speed with less work: - hardware Some RAID controllers can cause database corruption. It is not clear under which circumstances and what vendor. Reports about this behavior can be found on the internet. Please avoid the mistake of throwing good hardware at bad design. - OS Generally the use of Unix based systems will speed up things. - database parameters Experience shows that changing the database parameters is a time intensive game which does not necessarily leads to consistent results. What works with one DB might fail with another. Having multiple databases on the same server makes a perfect set of settings for a specific database almost impossible. It will be very hard to measure the gain or estimate the time needed for finding the best settings. - Blob fields Blob fields should be moved out of the main database. Especially since different database vendors handle blobs differently. On top of that some databases need more temporary space for sorting or retrieving data when one of the involved tables contains a blob field. Queries have to be optimized way more to force the RDBMs to use the proper indices and minimize disk IO. Most times blobs shouldn't be stored in the database at all. Access restriction can be handled via the OS. In case blobs will be stored in the database there should be only one blob table in the whole system. The access to the proper data will be done via the unique PK. See primary key discussion below. As described earlier huge databases containing many tables and blob fields tend to corrupt faster. - Indices The main condition for proper key design is the understanding that two unique keys have to be considered: -- Logical keys A logical key is combined of attributes. It is used to avoid duplicates in the database. -- Primary keys The primary key is nothing else then a physical unique identifier for every single row. Most RDBMs have this key implemented for internal use, but not available for the programmers use. The primary key must to be stable. With other words it can NEVER be changed. The primary key must never be the same as the logical key, because the logical key is made up of entities whose values can change. Way too often databases contain many tables where an additional field was added to force the uniqueness of the logical key: e.g. A_ID got a SEQ_NUM added for that sole purpose. Meaning keys represent a hierarchy among the data. This is inflexible and limits the number of levels. And it is plain wrong. If a logical key is used as primary key and this key is changed all foreign key references to this table have to be updated. Migration of logical keys used as primary keys will have terrible implications on database flexibility. A primary key independent of all attributes or relationships allows changes to the logical key without cascading changes. It allows maintaining data easily and sufficiently. Another major advantage is the minimal impact when adding attributes to the logical key. Since entities are defined by relationships programmers are tempted to use foreign key columns in the primary key. As stated above this is bad practice because changing the primary key will lead to major changes in all client applications and stored procedures. When using a real primary key there would be only the change to the logical key in the involved tables and any place where inserts and updates happen. All references to primary keys stay the way they are. -- Primary keys have to be * unique * not null * stable, meaning unchanged over their lifetime * different from the logical key * internally assigned, else they belong in the logical key * logical meaningless, to avoid the use of them Do all tables need to have primary keys? Yes. Just do it! When a table contains rows which have to be referenced from other tables in the database then that table absolutely needs a primary key. In all other cases it depends on the way programmers use the data. In cases it will make sense to add the primary key to simplify the physical view of the data. From past experience we know that systems and their uses change. To be future-compatible always adding the primary key is best practice. Primary keys should be integers filled from one generator per DB. -- Benefits of primary keys * Improved speed * Improved flexibility * Simplification of the database schema * Easier transition to databases from other vendors because foreign key constraints are simplified There is only one major disadvantage: It takes a lot more time to implement the use of primary keys into an existing system then starting with primary keys right from the beginning. And it has to be done with a lot of discipline. - table design / table contents Most systems are designed in a way, which allows the easy retrieve of data in specific predefined ways. Adding new ways is complicated and time intensive. More often than not tables need a re-design to better reflect the data stored in them. A perfect example would be a table containing one varchar field to store numeric, alphanumeric and date values. This table should be split into more then one table to reflect the different types. It should be one logical table with one primary key but containing multiple physical tables, at least one for each different data type stored. - normalization --First NF All columns only contain atoms --Second NF All columns not containing the primary key are dependent on the primary key -- Third NF All columns which do neither contain the primary nor the logical key are independent from each other -- Fourth, Fifth NF and further normalization Has been proven as not relevant to actual RDBMs and will be therefore ignored. Normalizing the tables will lead to speed improvements and a more simplified ERWin diagram. - query optimization Query optimization depends heftily on the used database and will be discussed in another white paper. (C) Frank D. Kanu All rights reserved.


About the Author:

Since two decades author and leadership consultant Frank Kanu helps top managers and executives to improve success ratios and productivity.




Recommended Web Site(s):

Easy Articles Home Page - Articles Directory

Recommended WhmSoft Web Sites, Feeds and WAP Address:

WhmSoft Software Home Page - Software
WhmSoft Services Login Page - Music and Images
WhmSoft Moblog Home Page - Blog - Photo Gallery
WhmSoft Free Online Games Home Page - Flash Games
WhmSoft Services RSS Feed - Daily Music, Image and 3D Flash Animation
Classical Music with Drum RSS Feed - MIDI and MP3 Files
Classical Music with Drum Podcast Feed - MP3 and MP3 Files
WAP / I-Mode / PDAs - Daily Music, Image and Flash Animation

Home Pages:

WhmSoft Free Articles for Reprint Home Page
WhmSoft Services Home Page - Music and Images
Copyright (C) 2006-2017 WhmSoft - All Rights Reserved.