Optimizing Joined Reports Performance

When we have users submit support tickets related to slow performance in their account, some of the biggest reasons are related to the Joined Report. Because a Joined Report is bringing two or more tables together, it can get quite large. Sometimes large report is unavoidable, but can also be caused by unnecessary information that is included in the joined report or if it is joined improperly. Here are a few tips to consider if you're joining two or more report and are either currently experiencing performance issues or are concerned about performance and want to make sure you avoid any unnecessary slowness.

Don't include columns you don't need.

In most situations when joining two reports together all selected columns from both reports will be included. Usually you won't need all columns from both reports so it doesn't make sense to include them all.

Take for example a situation where you have a lot of user data on one report and you need one column about the users monthly sales on another report. Instead of including all the columns from both reports and getting many unnecessary columns, you can choose to include just the one(s) you need.

To do this, use the Column Cleanup transform on both reports you'll be joining before you create the joined report. That way you'll only be joining on the columns you've chosen to include in the Column Cleanup step. Doing this can significantly decrease the size of your joined report and should allow Grow to process it quicker.

Use cross joins sparingly.

"Cross" join combines the two reports so that all of the data from one report will be added to each row of the second report. An example of this is if you had a table that had the color options for T-shirts and the second table had the size, a cross join would create a row for each combination of colors and sizes. Basically, it multiples the number of rows because it is joining all of the rows from one report to each row of the second.

As you can imagine based on the multiplication nature of this join type, the output of the cross join can get out of hand very quickly and cause tons of performance problems. There are certainly situations, like the T-shirt example above, where the cross join is necessary but make sure it is the correct join type for your situation before using it.

There are a few resources that explain the kind of joins for PostgreSQL. Here's one that we've found to be helpful: https://www.w3resource.com/PostgreSQL/postgresql-join.php

Was this article helpful?