Database Performance Tips

Database performance is an important thing to keep in mind when designing your database.  As your database grows over time, queries will inevitably start to slow down as there is more and more data to search through.  Whether you have found yourself in a situation in which your queries are running unusually slow or you are just starting the design and setup of you database, below are a few suggestions that can help fine tune the performance of your database.

Properly index columns

Creating an index on a column tells the database that you're going to reference it often in order to locate data. A well placed index can dramatically affect query speed, sometimes taking queries down from minutes to sub-second. However, creating too many indexes can have a negative result so only create indexes that are relevant to your queries on that table. Also, creating multi-column indexes can be effective, but more often than not is less helpful than expected. Any column that is often used to look up or narrow down data is a good candidate for an index. For example, almost every query on a historical table would reference the timestamp column, so indexing that column would be a wise idea. Tables that are created by the SQLTags Historian already have indexes created by default, so unless you notice some very long running queries on the Historian queries you shouldn't have to worry about these tables.

Give your machine plenty of RAM, and use it

Having a good amount of RAM on your database machine lets it hold more information in memory, reducing slow disk access. Given the low cost, it can be one of the most economical hardware based improvements, as well. Be aware, however, that you may need to configure the database to take advantage of it- many databases have settings that limit the amount of memory the system can use. Documentation on the specific database engine you are using will have information on how to adjust the memory usage settings.

Select your "engine" wisely

Some database systems, such as MySQL, support multiple "data engines", or methods of storing data. The different engines each have their strengths and weaknesses, and by being informed and choosing wisely you can significantly improve system performance. For example, the InnoDB engine has good transactional support. However, in many cases this is not crucial, and the performance boost offered by the MyISAM engine is more important. For historical data, the Archive engine can drastically reduce the required disk space; at the trade off of some query speed (indexes aren't supported, though querying an archive table still tends to be faster than a non-index InnoDB or MyISAM table).

Exclude data directory from virus scanner

Many virus scanners include "realtime" components that constantly check changing files for viruses. Since the database interacts frequently with the disk, it can trigger the scanner to execute frequently, killing performance. It is highly advisable to exclude the database's data directory from the list of folders to monitor. Some virus scanners will even have special exclusions that you can setup specifically for databases so make sure you familiarize yourself with whatever virus scanner product you are using so you can learn how to exclude your database from whatever live scan features it may have.

Check your data types

Having columns set to use data type that are larger than the actual data being stored can result in wasted disk space. By selecting the correct data types, you can save space and improve read/write performance.

Avoid sub-queries when JOINs will do

Utilizing JOINs in your SQL Queries allows the database to optimize much more than with sub queries. This results in quicker queries with less memory usage.

Check your SQL Server "auto grow" and "auto shrink" settings

Some database systems, such as Microsoft SQL Server, allow the database to dynamically grow and shrink as data is inserted and deleted. By default, the "auto grow" setting for Microsoft SQL Server is set to grow by 1MB at a time. When data is inserted at a fast rate, the database constantly has to increase the size, leading to disk fragmentation. A general rule of thumb to you can use for testing is to set your "auto grow" setting to about one-eight of the size the table will get. Also, turn off any "auto shrink" settings to prevent that database from constantly growing and shrinking, again leading to disk fragmentation.

Periodically defragment your hard drive

Over time as data is inserted and deleted from the database the disk can get fragmented causing your queries to take longer. Periodically check and defragment your hard disk to avoid this issue.

Check for periodically executing tasks

Some database systems, such as Microsoft SQL Server, can execute tasks, such as stored procedures and back ups, on a schedule. These tasks will run automatically and can affect the performance of the database if executed at the wrong times or if the tasks are not optimized. Periodically check these tasks to find out when they are running and how long they take to execute.

Use database profilers and query analyzers

Many database systems come with profilers or query analyzers that help you see how the database is performing. Profilers are graphical user interfaces that monitor all database events in what's known as a trace file. You can then analyze or use the trace file to troubleshoot logic or performance problems. You can also use the utility to do a stress analysis, fine tune indexes, auditing and reviewing security activity, etc. Query analyzers can be used to recommend indexes for specific tables, find out exactly how the database system will execute a given query, and statistics after the query is executed. This tool can help better optimize slower performing queries.

Be aware of database activity

Most database software allow you to set up different "schemas" which many users tend to refer to as "databases"; MS SQL server also allows you to run different instances each with their own multiple schemas. This functionality is great for allowing you design separate databases for use by different applications who all maintain their own connections to the database. An often overlooked side-effect of this functionality is that a user will not actually be aware of the database load due to all of these different connections.  If you find yourself experiencing slow running queries when you feel that the load you are putting on the system seems to be rather insignificant keep in mind that there may be many other applications accessing the database. In the situation where there are a lot of different applications putting load on the database there may not be too much you can do to increase performance.  Your best bet will be to try increase the memory allotted to the database.  If that doesn't work then moving to a dedicated database for use with Ignition may be a better option.