Database optimisation
Every business application has got a database, in which information and status are stored. Database systems (DBMS) are generalised systems, which always present a compromise in a basic configuration.
The only party benefiting from investing in faster hardware is the manufacturer. You do not gain anything! Increasing CPU capacity fivefold or the central memory tenfold will make your costs explode. Unfortunately these are the dimensions you would have to increase your systems with in order to get rid of the database that is creating the bottle neck.
Micromata succeeded in improving SQL running times within reputable German companies by 140. Reaching the same result through the increase of hardware that would take up to seven years according to the law of Moores. Save your time…
The critical factor of SQL statements is their time/load behaviour in relation to the size of the table on which they operate. During the development of the application a minimal amount of stored data is assumed. At the beginning the application can cope, however, with time, the operation worsens due to increased data. Should the application still function despite increased data, you will have exactly the case you want to avoid: the application becomes unusable in just that moment when you need it to make money.
Bad timing
If a multiple full table scan results from the SQL statement (e.g. nested selected stipulation) a polynomial time response is to be expected – a fatal efficiency trap.
Linear Time Response
Linear time response is always then expected when the database cannot optimise. In that case the database searches the desired data internally. This is called a full table or range scan.
Constant Time Response
Constant time response is what you need. No matter how many data records your database contains. Constant SQL statements do not need more time if there are more data records in the database. You also have to remember that an application with more data will be used more often. Be glad if these statements are equally weighted.
