The Importance of Testing Application Performance For Database Tuning

March 10, 2023

One of the features I'm building for PI's and administrators in the Ag Data Portal includes the ability to download and share Ag Data such as raw Yield, raw AsApplied, and raw Protein records. There are several sets of select lists to choose from.  (See figure 1 below)

Criteria for Displaying and downloading Ag Data Types for Administrators:

1. Choose a research entity.
2. Choose a farm.
3. Choose a field.
4. Choose a year.

Features:  

Example - AsApplied:  For the select boxes, we only want to display Research Entity choice options where there exists data for that entity in AsApplied tables.   (It wouldn't make sense to force an end-user to browse through all of the entity options who didn't have data right?) 

Entity Select Box

The query for the Entity select box looks like this:   

SELECT DISTINCT E.ENTITYNAME, E.ID AS ID_ENTITY
FROM RAW_ASAPPLIED AS RA INNER JOIN
ENTITIES AS E INNER JOIN
FARM AS F ON E.ID = F.ID_ENTITY INNER JOIN
FARM_FIELD AS FF ON F.ID = FF.ID_FARM ON RA.ID_FIELD = FF.ID
ORDER BY E.ENTITYNAME 

You can see that we're performing inner joins to display only those entities where a farm field belonging to a farm, exists under the management of a research entity.  In this case, we have data in the database for

1) Washington State University

2) Montana State University.

Because the AsApplied table will potentially have millions of records, at this point we do not want to display AsApplied data in the report below because it would display all AsApplied data for all farms and all fields for a given entity consuming browser memory.   So we proceed as follows..

Farm Select Box:

SELECT DISTINCT F.FARMNAME, F.ID AS ID_FARM
FROM FARM F
INNER JOIN FARM_FIELD FF ON F.ID = FF.ID_FARM
INNER JOIN RAW_ASAPPLIED RA ON FF.ID = RA.ID_FIELD
WHERE F.ID_ENTITY=:P10_ENTITY
ORDER BY F.FARMNAME

:P10_ENTITY is required for a Cascading LOV (List of Values) which is dependent upon the choice made from the previous Entity Dropdown.  What will display here are only those farm fields belonging to the chosen entity, where id_field (unique id for a farm field) belongs to that specific farm an where data from that field exists in the AsApplied table..

We still do not want to automatically display the results of this query because a farm can have many years of AsApplied data potentially adding millions of records to the screen and consuming memory.

Field Select Box:     

SELECT DISTINCT FF.FIELDNAME, FF.ID AS ID_FIELD
FROM FARM_FIELD FF, FARM F, RAW_ASAPPLIED RA
WHERE FF.ID_FARM = F.ID
and RA.ID_FIELD=FF.ID
and FF.ID_FARM=:P10_FARM
ORDER BY FF.FIELDNAME ASC

:P10_Farm  is required for a Cascading LOV (List of Values) dependent upon the previous choice of farm from the farm select box.

Here in this example, were choosing only those fields where a field's unique id exists in a AsApplied record, and also, where the field belongs to the specific farm chosen by the end-user.

Displaying AsApplied Data for Inspection and Download:  

It is at this point we can start displaying the data below in the Interactive APEX Report for the user to review for downloading.  

AsApplied Interactive Report Region Display:

Below is the SQL Code that determines the AsApplied data to be displayed based on the users selections. A dynamic action is also used here to refresh the screen in order to display the data.

SELECT DISTINCT E.ENTITYNAME,
E.ID AS ID_ENTITIES,
F.ID AS ID_FARM,
FF.ID AS ID_FIELD,
RA.ID AS ID_ASAPPLIED,
P.ID AS ID_PRODUCTS,
F.FARMNAME,
FF.FIELDNAME,
P.PRODUCTNAME,
RA.YEAR,
RA.RATE_APPLIED,
RA.RATE_TARGET,
RA.WKT,
RA.X_LON,
RA.Y_LAT
FROM ENTITIES E
INNER JOIN FARM F ON E.ID = F.ID_ENTITY
INNER JOIN FARM_FIELD FF ON F.ID = FF.ID_FARM
INNER JOIN RAW_ASAPPLIED RA ON FF.ID = RA.ID_FIELD
INNER JOIN PRODUCTS P ON RA.ID_PRODUCT = P.ID
WHERE (:P10_FIELD IS NOT NULL AND :P10_YEAR IS NOT NULL AND RA.ID_FIELD=:P10_FIELD AND RA.YEAR=:P10_YEAR)
OR (:P10_FIELD IS NOT NULL AND :P10_YEAR IS NULL AND RA.ID_FIELD=:P10_FIELD)

Year Select box:

Examine the SQL query below used to display only those years for which there exists AsApplied data for the previous select box choices made.  

Here is a problem!  We select the year from the AsApplied data but the RA.ID_FIELD is pointing to FF.ID.   It should be referencing :P10_FIELD otherwise it will attempt to display duplicates of all records where there is a field id record in AsApplied and Farm_Fields table.   This is bad!  

SELECT DISTINCT RA.YEAR, RA.ID
FROM RAW_ASAPPLIED RA, FARM_FIELD FF
WHERE RA.ID_FIELD=FF.ID

Performance Tuning:  

The graphic below displays the performance of these queries when run against the database absent the appropriate indexes.

Observe the duration.   Performance is terrible!   However once we have build the indexes on the table columns and re-run the query we get this:

Much Better!