How i was able to optimize page loading time from 30 seconds to less than 1 second !!

When it comes to web based applications, loading time is essential, and you don’t want any of your pages to take more than 3–5 seconds. I had one of the main paginated grids taking 30 seconds to load.

I needed to do something about this.

I went into this grid with profiler* enabled and started checking the loading time of every query happening to the database. The problem wasn’t in fetching the records, it was the count query
The library* i was using for grid pagination takes an ORM Adapter which by default uses the OutputWalkers to get the count of the records.

Which looks something like this:

SELECT 
    COUNT(*) AS dctrn_count 
FROM 
  (
    SELECT 
        DISTINCT id_0 
    FROM 
        (
        SELECT 
            m0_.id AS id_0, 
        ...
        FROM 
            table_name m0_ 
        ) dctrn_result
  ) dctrn_table

And when the number of records increase this query’s complexity increases exponentially.
Obviously if you need to count the number of records you could use a much simpler query to get the count.

I looked it up and I found out that to initiate an instance of this library you pass an Adapter interface ( Doctrine ORM Adapter ) and when you’re creating a new instance of this Adapter, you can specify whether you want to use output walkers or not and I tried it and boom, the query went down to milliseconds and as a result the page now loads in less than a second.

The query now looks like your usual count query:

SELECT count(DISTINCT id) FROM table_name WHERE condition = value

Of course when changing such thing I needed to make sure that this change will not affect anything in the grid or ruin the count, and after research and investigation it turns out that this Output walker is used to make sure the count query will always work even when having a group by.
And in my case it was a simple select for one table without any group by so it was a perfectly working solution for me.

Conclusion: Whenever something is taking more time than it should, know that there’s something wrong and take an action.