The Performance Guide/Database and Performance

We need more than knowledge how to create a SQL command to optimize the performance of database application. More informations over our database environment and software design are also helpful, like a good database administrator just beside you.

First we try to do some optimizing without changes in our application and database schema. Otherwise we also hope the design from database has include some advisement for performance.

Driver

The right driver can help us. We need to use the newest direct database driver. We can fast take the ODBC and this is often a nice way for prototyping. ODBC access works of some converting from database statement and the result of this. This can't be fast or furious. Attention - depending on programming language choice, we have some different drivers (for example JDBC knows four driver types).

Network link

Database are often using over a network. To handle incoming statements, these database need a high performant network link. If many users working with database, check for load sharing to use a database cluster.

Also you need to know how is the MTU [maximum transfer unit] from your network. Optimize your database statements and results to avoid fragmentation in your network. This is database and your network performance. This can be one of the last steps, if nothing other can help you.

Tablespace

Take the right size of tablespace and remember also the undo and temp tablespace. You need the right filesystem for your tablespace. We would not therefore recommend that you use journaling filesystems.

Partitions of tablespace

Partitions of tablespace can help you. Some database systems can faster returning results, if you know relative allocation of data in your database.

Keys and indexes

The change to select values over distinct keys is important for databases. For queries you can win more than 100% with the right keys. Also important is to create correct indexes. The space for these is not so big. Better not the time will tell if we have right keys and indexes. Let us using executing plans...

Execution plans

Execution plans are good changes to check your performance effort. These mostly visual displayed statistics let you see: would your index and key take for your SQL statement?

Sort data

Often we need sorted data and we can simple include these request in our SQL. But attention - our RAM can be too fast full and then the temp tablespace / hard disk drive is in using. At this time it is important how these tablespace is defined.

Result amount

Many times we don't need to return all results to the user. Think about internet search machines - no one send all big results at once. First, the user get from special rule selected pieces of result set. The small-piece principle combined with background processes or thread for next result pieces can be a subjective performance for our users. Think - how often has You all sites from internet search visiting.

Offline and online queries

We 'normal' user want our query result very quickly. But some applications, like data warehouse or statistics don't require results directly. When we need our result is a small but important information to check. Sometimes async offline queries are enough. We can stop needless pressure with clever later start for these query - sometime at night...

Where are the SQL command?

We can be faster with in database saved SQL queries - procedures. Before we use the query procedure our database knows the wanted information and can be optimizing itself.

Otherwise we can also use storage procedures. Here the application give our database the SQL statement before often use this. Also database can equally optimizing for us.

Connection count and reusing

Connection management is using many resources. Check how many connection to the database are required.

Reusing database connection is a good way named connection pooling. We can save many resources / overhead, as we always create a new connection.

Database internal

Order of SQL statements

It is a good idea, to know how our using database SQL statement exploited. But this is database specific an so we better place our statements to external resources.

Oracle

Oracle database interpret the where clause from right to left. Big constraints should be conducted first, if we join huge tables. So with oracle database the place the bigger constraints at the right site - for example:

select * from Kunde K, Bestellung B
 where Datum ‘1.1.2000’ and ’31.1.2000 23:59’ and Name= ‘Anton’
   and K.kdkey = B.kdkey

Interbase

Interbase interpret the where clause from left to right. Big constraints should be conducted first, if we join huge tables. So with oracle database the place the bigger constraints at the left site - for example:

select * from Kunde K, join Bestellung B on K.kdkey=B.kdkey
 where “Anton” 
   and Datum between “1.1.2000” and “31.1.2000 23:59”