# 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:

<div align="left"><figure><img src="https://3933488479-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjpWd52BKwABWxF2lScUK%2Fuploads%2Fgit-blob-2b8740ae914119fbd645d7d6b915e64d527600ea%2Fquery-performance-server-name.jpg?alt=media" alt=""><figcaption><p>Statistics grouped by Server Name</p></figcaption></figure></div>

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

<div align="left"><figure><img src="https://3933488479-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjpWd52BKwABWxF2lScUK%2Fuploads%2Fgit-blob-dc525481b2a5a3d17dcb330f8d57bd527c79c0ff%2Fquery-performance-example-data.jpg?alt=media" alt=""><figcaption><p>Performance of "example_data"</p></figcaption></figure></div>

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

{% hint style="info" %}
Average time and total count for each query are the most useful stats.
{% endhint %}

## Advanced statistics

<div align="left"><figure><img src="https://3933488479-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjpWd52BKwABWxF2lScUK%2Fuploads%2Fgit-blob-2829ddf1cf186fd2b9b9894e0acf749a0a90da9d%2Fquery-performance-example-data-adv.jpg?alt=media" alt=""><figcaption><p>Performance of "example_data" - advanced statistics</p></figcaption></figure></div>

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](https://en.wikipedia.org/wiki/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](#log-only) (goes straight to log, ideal for production if you still want this data)
* 500 max
* Unlimited:

{% hint style="warning" %}

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

{% endhint %}

***

## 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>
```

{% hint style="info" %}
NOTE\
[ServoyCloud](https://docs.servoy.com/reference/servoy-cloud#servoy-cloud) has far more advanced query and database monitoring features, including machine learning analyses and AI.
{% endhint %}