"Thank you for the SQL course! It was the breakthrough I needed to let me solve a tough problem our team was dealing with. You method of thinking like a developer really works! "

- R DaCosta

The problem:

I was working on a feature to let event managers onboard their customers and sell them booth space at trade shows. Part of the onboarding included a requirement that the customer fill out a five step application. The steps include completing a profile, uploading photos, answering a customized questionnaire, signing legal agreements, and uploading required documents and licenses. Each step was tailored to the customers specific category and tags, resulting in various combinations of requirements for each individual customer.

The event manager required a screen to quickly see who had completed the required steps of the application process. They wanted a YES or NO for each step on each customer in order to eyeball the applications. Some managers had thousands of customers, each with unique and customized requirements, and I endeavoured to build a query that could pull up this information quickly. The query was often taking up to 10 seconds to run, while my goal was a page that loaded in under 1.5 seconds.

With so many variations of the requirements, the database was making too many calculations every time the page ran. It didn't matter if I wrote it using correlated subqueries, joins, or a combination thereof. I tried a dozen ways to write the query. No luck. And of course, the necessary indexes were all in place. Still the page was too slow.

The solution:

I decided to preprocess the application results and store the YES or NO in a simple table. Preprocessing the results, even for thousands of applications, too on average 7 seconds. When this process ran, it populated a table whose sole purpose was to hold the customer's ID and a YES or NO, indicating the status of the application's completion. We put this on a timer, executing the process automatically on the server every 20 minutes. We also gave the customer the option to manually run the preprocessing as needed. The page ran in under a second, using a simple join between the main tables in the report and the application result table, and the customers have never looked back.