Each month my department compiles a 4M row 150 column data table for compliance with a federal agency. Before submitting, we check it against about 400 rules.

The existing system was simply 400 queries that ran in sequence, table-scanning 4M rows each time, taking upwards of 6 hours, which is a huge bottleneck, especially if you have to make changes and rerun. Plus the output was rather one-dimensional.

I built a proper normalized database and created a sort of rules engine, running all 400 rules in one table scan. Not only does it complete in 30 minutes, but the reports generate automatically, and the results can be filtered on several dimensions to aid with root-cause analysis.

Management was pleased.

Add Comment