Frameworks have changed the way we develop web applications and many amazing products and services have been delivered as a result.
As developers we take these [frameworks] for granted, they help abstract a lot of the day to day leg work of our applications, and help us develop features more quickly.
This is great in the early days of an application’s lifetime when it’s helpful to be able to rapidly innovate and release new features, but as your user base grows and your application needs to scale, the speed and convenience of these frameworks can hold some hidden pitfalls that we should be aware of.
Recently we came across one such pitfall in the way Tactile CRM is built – we use a custom framework but are slowly migrating our code to the Zend Framework.
We found that one of the search queries used extensively by our application was taking more and more time to execute as new users came on board and our user base grew. The result was a rogue statement/comparison in the way our queries were built with in the application:
$sh->addConstraint(new Constraint($field), 'ILIKE', urldecode($this->_data['q']));
would produce something similar to:
SELECT * FROM table WHERE fieldname ILIKE 'abc%';
(Where the ILIKE comparison does a case insensitive search and the % means do a wildcard search i.e. abcd would match as would ABCD)
Now in general this query isn’t an issue – depending on the database server you use they will handle it in different way. In our case we had a perfect storm. Our development and test environment of our platform are pinned to database versions for testing and deployment reasons and the ILIKE operation on the current live database is very expensive (this has been fixed in a later version of the database server but when testing on our development version the issue wasn’t visible).
After hunting down the issue a change of the line to:
$sh->addConstraint(new Constraint('lower(' . $field . ')', 'LIKE', strtolower(urldecode($this->_data['q']))));
produced SQL such as:
SELECT * FROM table WHERE lower(fieldname) LIKE 'abc%';
and sped up the queries by over a 1000% in worst case scenarios (we lower cased the search string in our code too). Whilst we were at it we updated the framework to only use the LIKE comparison when doing wildcard searches. The new line of code has given us a speed improvement of over 1000% in certain cases for a few hours work.
Certain schools of thought will say that this should have been fixed before the application was released, however I’m a firm believer of the release often methodology of development and learning from one’s mistakes (and those around you). The offending query in itself wasn’t causing major issues, just in certain edge cases and we caught it before it became an issue. It’s given us a reasonable speed boost in the application for not a lot of work.
So the moral of the story is, check the queries your framework is building and how your database plans the query execution once in a while, you might be able to implement some low cost speed boosts for you application.