Complex Data Structure / Major Performance Issue

Hi everyone: I need some help with a major performance issue within my application.

The application has a "matrix" report which calculates and displays a vast amount of data. This data (in this format) is critical for the core application. It works, but, it's really, really slow. Let me explain.

Core Tables:

Laws
Business Units
Departments
Requirements (type A)
Requirements (type B)

Essentially, the application helps businesses understand how certain laws, regulations, or contractual compliance obligations apply to their business and what they need to do in order to comply. For each law or obligation in the system, a Business Unit categorizes the Law into a Type. Let's call them Type A, Type B, and Type C. From there, a Business Unit sets up dynamic Departments. In the active database, I have 20 dynamic Departments. There are about 1,000 "Laws" in the current database. Each Law has 1 type. For Law Type A, there are a set of Requirements for each Department. For Law Type B, a separate set of requirements apply to each Department

Imagine a really massive report:

---------------------------------------------------------------------------------------------------
| Law Name | Law Type | Department {1} | Department {2} | Department {3}| ...... | Department {28}  |
---------------------------------------------------------------------------------------------------
| Law 1 Title  |  B              | (computed value) | computed value | computed value | computed value|
| .........		|  A              | (computed value) | computed value | computed value | computed value|
| .........		|  B              | (computed value) | computed value | computed value | computed value|
| Law 999	|  B              | (computed value) | computed value | computed value | computed value|
----------------------------------------------------------------------------------------------------

"Matrix Report" For the report depicted above, I'm first querying a list of Department. For each department, I dynamically create a DataTables column. Then, a query the list of "Laws". If Law Type = A, I then query that Law in Requirements Table A where Department ID = {x}. If Law Type = B, I query that Law in Requirements Table B where Department ID = {x}. That computation typically displays a single value representing a status for that specific Department for the specific Law on the row.

That means that there are thousands of database queries and objects.

I've realized that I could have potentially created a polymorphic Requirements table instead of two different ones. However, I don't think that will help with my fundamental performance issue.

The Problem: -The report I've built is currently using Yajra Datatables. It takes about 30 seconds to generate this complex report. I've used Laravel Debugbar to help understand the queries. Since each of query is dynamic, I'm not certain how to add efficiencies into the query.

Any thoughts?

How do I restructure this? How do I streamline the report and queries?

kalemdzievski

6 years ago

Level 6

Not sure if I will able to help you, since i don't know the DB structure, but from personal experience a lot of heavy reporting can easily be done with queries.

Here is an idea for an approach. Start with generating a query that replicate one row from your report. For example For "Law 1", try to get all computed values for the departments you need. First start with hard-coded columns for few departments, doesn't need to be all, 1,2,3 is enough.

Try to write a query to pull the info you need. For the computed values i would recommend to go with sub queries. Not sure how is your DB structure, so joins might get u in trouble in some cases. This will look something like this (please don't mind if the query not correct just trying to give an idea):

SELECT
    law.name,
    (SELECT SUM(department.column_X) FROM department WHERE department.id = 1) AS "Department 1",
    (SELECT SUM(department.column_X) FROM department WHERE department.id = 2) AS "Department 2",
    (SELECT SUM(department.column_X) FROM department WHERE department.id = 3) AS "Department 3"
FROM law

If you can replicate a simple query like this to pull data like in your report, you are alomst done.

Next you just need to dynamically create the final query. First you will need to create a query builder, so you can build your query dynamically.

And you can simply do this by, first, getting all the departments with a separate query, like from your model.

$departments = Department::all();

Or apply any filters if you need. Easy

Then, you will foreach the departments and just add selects with the sub-query that you've build at the very begging.

$departments = Department::all();
$query = DB::table('law')->select('law.name');

foreach ($departments as $department) {
    $query->addSelect("department_{$department->id}", function ($query) use ($department) {
       $query->selectRaw('SUM(department.column_x)')
          ->from('department')
          ->where('department.id, '=', $department->id);
    });
}

$query->get():

You will end up with just 2 queries. Hope this helps.

Please or to participate in this conversation.