Embedded Files
Tomcat Server Performance
How you have your server configured can also affect your overall performance. For example, the amount
of memory allocated to your server can have quite an effect on how the WOW application performs. For
more information on Tomcat configuration, see the Optimizing Tomcat reference.
SQL Fragments
Overview:
[PRO] WOW Enterprise Edition 7.0 and above include a feature that can significantly enhance performance
of SQL. Consider a table that holds tax payer information and includes first name, last name, and social
security number. If an optional search is created allowing entry of any of the three columns:
SELECT * FROM x.y WHERE firstname = COALESCE( ?, firstname)
AND lastname = COALESCE( ?, lastname)
AND SSN = COALESCE( ?, SSN)
If “Jones” is entered for lastname, the database processor must still process selection on firstname and SSN
which can affect performance. SQL Fragment will translate this query to:
SELECT * FROM x.y WHERE lastname = ‘Jones’
Using SQL fragments, the WOW operation would be coded as: (This assumes lastname is required on search)
SELECT * FROM x.y WHERE lastname = ? [[ AND firstname = ? ]] [[ AND SSN = ? ]]
Which will perform much faster.
This document describes how to work with SQL fragments in WOW. A SQL fragment is a portion of an SQL
statement which can be dynamically removed or included in the statement at runtime. Whether or not a
fragment is included is based on which of the statement’s parameters the user has entered values into.
Fragments are removed from the SQL only when it is sent to the database; fragments are never removed
when generating the onscreen prompts for a SQL statement.
Defining a fragment
The sample SQL statement below selects fields from table A; the user can search on fields from table A
or table B:
SELECT A1, A2, A3, A4 FROM LIB.A INNER JOIN LIB.B ON A1 = B1
WHERE (A1 = ? OR CAST(??1 AS INTEGER) IS NULL) AND
(A2 = ? OR CAST(??3 AS CHAR(10)) IS NULL) AND
(B2 = ? OR CAST(??5 AS CHAR(10)) IS NULL) AND
(B3 = ? OR CAST(??7 AS CHAR(10)) IS NULL)
ORDER BY A1
To define a fragment of code, enclose the fragment between two left brackets and two right brackets:
SELECT A1, A2, A3, A4 FROM LIB.A INNER JOIN LIB.B ON A1 = B1
WHERE (A1 = ? OR CAST(??1 AS INTEGER) IS NULL) AND
(A2 = ? OR CAST(??3 AS CHAR(10)) IS NULL) [[ AND
(B2 = ? OR CAST(??5 AS CHAR(10)) IS NULL) AND
(B3 = ? OR CAST(??7 AS CHAR(10)) IS NULL) ]]
ORDER BY A1
This causes WOW to create a SQL fragment which contains the following code:
AND
(B2 = ? OR CAST(??5 AS CHAR(10)) IS NULL) AND
(B3 = ? OR CAST(??7 AS CHAR(10)) IS NULL)
By default, WOW will automatically remove any fragments which contain display parameters when none of
those parameters have values. So if the user ran that SQL statement and left both the B2 and B3 prompts
blank, then the SQL which WOW would actually run would be:
SELECT A1, A2, A3, A4 FROM LIB.A INNER JOIN LIB.B ON A1 = B1
WHERE (A1 = ? OR CAST(??1 AS INTEGER) IS NULL)
AND (A2 = ? OR CAST(??3 AS CHAR(10)) IS NULL)
ORDER BY A1
Fragment groups
SQL fragments can be associated together in a fragment group. Putting multiple fragments together in the
same group tells WOW what it should do with fragments which do not contain display parameters (fragments
with display parameters are removed when all of those parameters do not have values). Using the above
example, we could define two fragments which belong to the same group.
SELECT A1, A2, A3, A4 FROM LIB.A [[{group1} INNER JOIN LIB.B ON A1 = B1 ]]
WHERE (A1 = ? OR CAST(??1 AS INTEGER) IS NULL) AND
(A2 = ? OR CAST(??3 AS CHAR(10)) IS NULL) [[{group1} AND
(B2 = ? OR CAST(??5 AS CHAR(10)) IS NULL) AND
(B3 = ? OR CAST(??7 AS CHAR(10)) IS NULL) ]]
ORDER BY A1
Adding a group name surrounded by curly braces at the beginning of the fragments tells WOW that both
fragments belong to the same group; in this case the group is named “group1”. Any name could be used to
identify the group; as long as both fragments use the same name they will be in the same group. In this case
the first fragment does not contain any parameters (a “non-parameter fragment”), so it will be removed when
the other fragment in its group (the “parameter fragment”) is removed. As noted before, parameter fragments
are automatically removed by WOW if none of the display parameters contain values. By using a parameter
group, WOW can totally remove the join from the query when the user is not searching on any of the fields in
the second table.
In the next example the SQL contains multiple fragment groups:
SELECT A1, A2, A3, A4 FROM LIB.A [[{groupB} INNER JOIN LIB.B ON A1 = B1 ]]
[[{groupC} INNER JOIN LIB.C ON A1 = C1 ]]
WHERE (A1 = ? OR CAST(??1 AS INTEGER) IS NULL) AND
(A2 = ? OR CAST(??3 AS CHAR(10)) IS NULL) [[{groupB} AND
(B2 = ? OR CAST(??5 AS CHAR(10)) IS NULL) AND
(B3 = ? OR CAST(??7 AS CHAR(10)) IS NULL) ]] [[{groupC} AND
C2 = ?]]
ORDER BY A1
The fragments in groupB are only included when either B2 or B3 is given a value. The fragments in groupC are
only included when the C2 parameter has a value.
Page updated
Report abuse