Slow Queries
Slow running queries can be a big problem. Not only can data take a long time to display on the screen, but it can end up slowing down the whole client or session. This page covers checks and adjustments you can make when your project has a slow running query.
Identify the Slow Query​
The first step in dealing with slow queries is identifying which query is running slowly. For instance, a sign that a component has a slow query is if a window is opened and the component takes time before database data is displayed. However, it may also be a good idea to check the queries that are being run against that database connection in the Gateway. From the Gateway, navigate to Status > Connections > Databases to see a list of all database connections. Clicking Details to the right of the database connection will show all of the currently running queries, the most recent long running queries, as well as some basic metrics for that connection. This information can give a good idea of any queries that may be a little slower than the others.
Execute Against the Database Management Software​
The next step in identifying the type of slow query we are dealing with is to run the query directly within the database management software. By cutting out Ignition, we can determine if the query is actually running slowly, or if there is a problem within Ignition that is making the query run slow.
Fast Query in the Database Management Software​
If the query runs quickly in the database management software, you can complete the checks described below to try and identify an issue within Ignition.
Check the Database Connection​
First, check to make sure the database connection is valid, and there are no warnings associated with it. You may want to go into the connection settings for that database and take note of the value of the Max Active property, which determines the maximum number of active connections to the database, as well as the Max Wait property, which is the number of milliseconds to wait for a connection to come available. To get to the database connections settings, go to Config section of the Gateway and select Databases → Connections. Click the edit button for your database to view the database settings. Open Advanced Properties at the bottom of the screen, and you'll find the Max Active and Max Wait properties.
Check Currently Executing Queries​
In the Status section of the Gateway on the Database Connection page mentioned above, we can see a list of currently running queries, as well as how many of the active connections are being used. If the max number of connections is being used, it may be that there are so many queries running that each query needs to wait for an active connection to open up. If this is the case, you may want to increase the amount of active connections to the database or take steps to reduce the query load on the database.
Slow Query in the Database Management Software​
If the query also runs slow within the database management software, then the query is just a slow query. Unfortunately, nothing within Ignition can speed up the execution of that query, so you would want to instead take a look at what the query is doing. If the query is pulling in lots of data, you can try breaking the query down into smaller queries, or writing the query in a more efficient way.
For particularly large tables, it may also be helpful to add an index to one of the table's columns. Indexes are something that the user can't see, but help the database speed up data retrieval. However, adding an index to a table will increase the amount of time that an update to the table takes, because the index also needs to be updated. For this reason, it is recommended to only make indexes on columns that are frequently searched against.
To make an index, most database management software have built-in interfaces that allow you to customize the index on each table. An index can be made for a single column, or a combination of columns in the table. Talk to your Database Administrator about adding or updating table indexes.
Alternately, you may need to take a look at the database system as a whole. As the size of the database grows, you may need to update the hardware resources available to it. If the database is installed on a server with another system like Ignition, be aware that although the two systems are now sitting next to each other, they have to share the hardware resources available to them, which may cause issues for both systems. In many cases, it is often better to have the database run on a separate server, which gives it ample room to grow.