Horrible performance in CountOutputWalker query

[Yolo-plop]

Hello,

I am encountering major performance issues with counting result rows using the CountOutputWalker. It seems that my database system (IBM Informix) really doesn't like the Doctrine is writing the query. The original select query has some joins and an order by, the count ends up being something like this:

SELECT 
  COUNT(*) AS dctrn_count 
FROM 
  (
    SELECT 
      DISTINCT id 
    FROM 
      (
        SELECT 
          ...
        FROM 
          myTable
          LEFT JOIN files...
          LEFT JOIN translations...
        ORDER BY 
          translations.slug ASC
      ) dctrn_result
  ) dctrn_table;

I have of course removed a lot of the query details because it's not very important here, i'm just selecting a main entity, joining two tables and sorting using one of those two joined table.

This query takes about 150ms. It's crazy high, especially considering it's just the final count and there are lots of queries before that one.

Now, let's remove the order by part. It now takes 70ms. Way, way better. Then let's remove those left joins... it's now 15ms.

And here i am confused. I don't understand why we need those parts of the query here. Why do we do a sort when we are just counting the number of rows? Why do we do left joins as it's not going to limit the total number of the main entity that we are getting? Especially since we are doing a distinct id, so it doesn't matter if the query returns more rows with those joins.

The walk select function of the class contains this piece of code:

if ($this->platform instanceof SQLServer2012Platform || $this->platform instanceof SQLServerPlatform) {
            $AST->orderByClause = null;
        }

Why are we doing this only for sql server?

Am i missing something? Is there a reason for keeping those query parts or do we keep them simply because it's not going to create issues in most database systems?

Thanks

[derrabus]

Can you provide a PR with the changes you would suggest? But please note that we do not officially support Informix, so we cannot merge any code that is specific to that database engine.

[Yolo-plop]

Thanks for your feedback. I have created a merge request. I apologize in advance if i did something wrong since it's my first time contributing to an open-source repo.

I would however like to have your point of view on the matter, because there might be a very good reason why Doctrine does it this way that i'm not aware off.

In my pull request, i have for starters only removed the order by clause from the count statement since it's the most obvious. Why would we want to do an order by on a row count? On my Informix database, removing this clause drops the response time from over 300ms to +-35ms, almost ten time faster! I have no idea why Informix performs so poorly with this, are dba is going to look into it.

I also tested it with postgres as we have an exact replica (with the same indexes) of the database. Removing the orderBy drops the response time from +-14ms to +-8ms. Granted, it's not a huge difference but it's something, especially on big applications. I see no reason not to optimize it as much as possible if it has no downside.