0

Currently, I am experimenting with an EAV+Single Table Inheritance approach in Doctrine 2.

I have a Base-Class that has many Values and I have multiple Sub-Classes of the Base-Class. The Sub-Classes are related.

In the following CodeExample all Entities are SubClasses of the BaseClass except for Value.

In this example the hydration takes 3200 ms:

        $qb = $this->createQueryBuilder('p')
            ->select('p', 'rip', 'rs', 'c', 'pv', 'ripv', 'rsv', 'cv')
            ->leftJoin("p.revisionsInProject", "rip")
            ->leftJoin("rip.revisionState", "rs")
            ->leftJoin("rs.component", "c")
            ->leftJoin("p.values", "pv")
            ->leftJoin("c.values", "cv")
            ->leftJoin("rs.values", "rsv")
            ->leftJoin("rip.values", "ripv")
            ->where("p.id = '".$id."'");

        $q = $qb->getQuery();

        return $q->getSingleResult();

In this example the hydration only takes 302 ms. The only difference is that I removed the p.values and the pv from the query.

        $qb = $this->createQueryBuilder('p')
            ->select('p', 'rip', 'rs', 'c', 'ripv', 'rsv', 'cv')
            ->leftJoin("p.revisionsInProject", "rip")
            ->leftJoin("rip.revisionState", "rs")
            ->leftJoin("rs.component", "c")
            ->leftJoin("c.values", "cv")
            ->leftJoin("rs.values", "rsv")
            ->leftJoin("rip.values", "ripv")
            ->where("p.id = '".$id."'");

        $q = $qb->getQuery();

        return $q->getSingleResult();

The Problem does not solved when I use other hydrations modes like ARRAY or SCALAR. They are a bit faster but still ridiculously long.

Can anybody explain why this is happening? The other values (c.values, rs.values, rip.values) do not significantly change the performance.

The Code itself can viewed at Github

Robin
  • 3,512
  • 10
  • 39
  • 73
  • 1
    The MySQL result set potentially grows exponentially for every join. Doctrine needs to iterate over every row in the result set when hydrating. – Gerry Apr 02 '15 at 09:24
  • @Gerry That is obviously true, but the performance does not change that dramatically if I remove any of the other values. The `p.values` seems to have a significant impact on the querybuilder. For example if I remove the `rip.values` but keep the `p.values` the hydration takes `1000ms`. But without p.values but with rip.values the hydration is at `300ms`. – Robin Apr 02 '15 at 09:27
  • 1
    How many rows are there in the result set for both variants? – Gerry Apr 02 '15 at 09:28
  • @Gerry Using the full Query it is `11340 rows` and using the Query without the p.values its `1134`. That's interesting. So my issue is the query and not the hydration. – Robin Apr 02 '15 at 09:36
  • @Gerry Do you know if there is a better way to query for the values in this example? Lazy Loading everything is no solution for me, since I need the values. – Robin Apr 02 '15 at 09:40
  • 1
    Honestly, imho EAV is something that should be avoided. There are better solutions these days, like NoSQL databases (Mongo), or if you need a mix of relational and documents, PostgreSQL. – Gerry Apr 02 '15 at 09:41
  • @Gerry I tried Mongo with the same idea in, but Mongo was not suitable, since I have a lot of Relations. I should give PostgreSQL a chance, since I've never done anything with PostgreSQL. Thanks a lot Gerry. – Robin Apr 02 '15 at 09:44

0 Answers0