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. Here, we take a look at some of the things that you can do 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 actually running slowly. It is typically obvious when a window is opened and a component takes time before database data is displayed. Chances are, the query is on that component somewhere. 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 webpage, navigate to Status → Connections → Databases to see a list of all database connections. Clicking on the Details button 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. Here, you can get 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, then there are a few things we can take a look at that may help out.
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 Configure page of the Gateway webpage and select Databases → Connections, locate your database, and hit the edit button. Here, you can check the database settings. Open Advanced Properties at the bottom of the screen, and you'll find the Max Active property as well as the Max Wait property.
Check Currently Executing Queries​
Back in the Status section of the Gateway Webpage 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 now 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.