Using Netezza’s in-database analytics package FPGROWTH, database administrators can identify the most commonly used combination of tables and the performance of the queries that reference those sets of tables.

First, let’s see the most commonly used combination of tables. Today, FPGROWTH requires that you specify a unique ID within which associations are discovered. With query history data, the unique identifier is a composite key made up of NPSID, NPSINSTANCEID & OPID. We have to create a single key that we can feed into the procedure, which can be done as shown below:

Now, what is happening with this data is that there is one query that references 6 tables — but with each possible combination of 5 showed up several times. This is why you see the same averages for 5 different rows.

Not that I’m aware of; this solution is really a combination of two very different features within Netezza: advanced query history which tracks access all the way down to the column-level for historical reporting and the advanced analytic suite which allows us to use fpgrowth to identify frequent pattern sets within that query data.