Query Performance

Overview

This page displays the query performance (if enabled) of each used server. It contains information about time (total, average, min, max, standard deviation), count, query type and SQL for each executed query / server. It helps developers to analyze if their application is generating problematic queries or if their database is not optimized for their application. poor query performance is the most common cause of poor application performance.

Query Performance settings can be found Servoy Admin interface: <host-name>/servoy-admin/database-performance.

View performance grouped by database

Statistics are grouped by database name:

Statistics grouped by Server Name

In order to see statistics, click on the desired server name.

View running queries

This shows the performance of ongoing queries (typically only problematic queries will be visible).

View past query statistics

Performance of "example_data"

After clicking on the server name, the standard performance statistics page will be shown, displaying a table with the following statistics:

• total time : Total Time is the time needed to execute the JDBC statement, the time to fetch the data from the database to the application server is also measured, this is included in the end-to-end time. This column's time might differ from one DBMS and JDBC driver to another.

• average time : Avg Time is the time needed to execute the JDBC statement on average, the time to fetch the data from the database to the application server is also measured, this is included in the end-to-end time. This column's time might differ from one DBMS and JDBC driver to another.

• count : how many times the query was executed

• type : query type

• action : SQL query itself

Average time and total count for each query are the most useful stats.

Advanced statistics

Performance of "example_data" - advanced statistics

When clicking on Show advanced Performance Statistics., the table shows extra statistics:

• Total (End-to-end) Time : total time, including the time taken by the read of the data

• Avg (End-to-end) Time : average time, including the time taken by the read of the data

• Min Time : minimum time that one execution of the query took

• Max Time : maximum time that one execution of the query took

• Standard Deviation : standard deviation of executions of this query; see standard deviation

• Standard Deviation/Average : standard deviation divided by average time

Clear statistics

Statistics can be cleared for each database, by clicking Clear Statistics..

Export

Statistics can be exported for each database, by clicking Download as CSV..

Control Number of entries

The user can control the number of Performance entries by selecting one of the options available on top the Database Performance page in Servoy Admin:

• Disabled

• Log only (goes straight to log, ideal for production if you still want this data)

• 500 max

• Unlimited:

Warning: Not ideal for production systems as it can have a big footprint on the server.

---

Log Only

Servoy Provides special loggers that will log the performance directly to a file, this doesn't only work in Log Only (also for the admin page logging if that i enabled) For this you need to enhance the log4j.xml file that you use in production (or developer/application server if you want to test this in developer)

First in the loggers section of the log4x.xml file you need to add a Logger with the INFO level

        <Logger name="com.servoy.performance.timing.sql" level="INFO" additivity="false">
            <AppenderRef ref="asyncperformanceoutput" />
        </Logger>

the name is the logger where Servoy will output this to. It is a good thing to redirect this directly to its own file (and use additivity="false" to make sure it really only goes to that file) The Appender part is then creating its own output and uses async so the writing of the log is done in a seperate thread:

        <RollingFile name="performanceoutput"
            fileName="${log4j:configParentLocation}/performanceoutput.txt"
            filePattern="${log4j:configParentLocation}/performanceoutput-%i.txt.zip"
            immediateFlush="true" append="true">
            <Policies>
                <SizeBasedTriggeringPolicy size="10MB" />
            </Policies>
            <PatternLayout
                pattern="%d %m%n" />
        </RollingFile>
        <Async name="asyncperformanceoutput">
            <AppenderRef ref="performanceoutput" />
        </Async>

You can also add the client timing directly to the same file if needed or output that also to another file:

        <Logger name="com.servoy.performance.timing.methods" level="INFO" additivity="false">
            <AppenderRef ref="asyncperformanceoutput" />
        </Logger>

NOTE ServoyCloud has far more advanced query and database monitoring features, including machine learning analyses and AI.