Orm: Low performance when executing setParameter() on a query that has a big amount of parameters

[nivpenso]

Bug Report

When use AbstractQuery::setParameter() on a query with large number of parameters the preformance are getting really bad.

| Q | A
|------------ | ------
| BC Break | no
| Version | 2.6

Summary

I have a query that I need to build, this query contains about 8,000 parameters. I use AbstractQuery::setParameter() method to add each one of these parameters.
Building this query takes long time.

Current behavior

The buggy behaivour happens because each time setParameter() is called, the method checks whether the parameter is already exists. Checking that it is already exists is done by array_filter function which basically means that call to setParameter() the code runs over all the parameters array.

How to reproduce

Very is easy. Create a ORM/Query that has 8,000 parameters (each parameter should have a different key)

Expected behavior

After a while you should see an expodential increase of the time it takes to execute setParameter()

[Ocramius]

Please check against 2.6: 2.2 is ancient and unsupported

[nivpenso]

Apologize for the old version test. This also happens in 2.6. should I open a new post?

[Ocramius]

Nope, re-opening here 👍

[Amegatron]

+1 here. Can someone please explain, why getParameter/set are working such way? Why not just reference parameter by key from ArrayCollection? Why do we need to walk through ALL parameters, searching for matching name? Is it on purpose or it is just a flaw? I myself could not understand that :)

We also have complex queries with about 1,000 parameters, and parameter handling (our settting and later Doctrine's getting) takes about 93% of time.

Also, why was Query class made final? To workaround this performance issue (using direct parameter referencing described above) we had to (besides makeing custom EntityManager and QueryBuilder, which can simple extend base doctrine's classes):
1) make our custom Query (cause Query is final), which was directly copied from Doctrine's Query with changes described above
2) make our custom Parser (also copied from Doctrine's Parser), cause the latter is hard referencing Query: we could not just pass our own Query there, cause it is not a subclass of Doctrine's Query.

I would suggest at least make the Query class not final.

Thanks.

[lcobucci]

Also, why was Query class made final? To workaround this performance issue (using direct parameter referencing described above) we had to (besides makeing custom EntityManager and QueryBuilder, which can simple extend base doctrine's classes):

1. make our custom Query (cause Query is final), which was directly copied from Doctrine's Query with changes described above
2. make our custom Parser (also copied from Doctrine's Parser), cause the latter is hard referencing > Query: we could not just pass our own Query there, cause it is not a subclass of Doctrine's Query.

I would suggest at least make the Query class not final.

@Amegatron Query is not an extension point of the library, hence the final modifier. It won't be made an extension point in 2.6.x. Could you submit a PR addressing the reported issue?

[Amegatron]

@lcobucci OK, I'll try to make it some of these days.