Today, I want to take you out left field a bit. I want to take you a little away from the everyday, potentially clichéd, advice you likely read when it comes to improving Zend Framework 2 application performance.
There’s likely nothing wrong with it; but I’d say you’ve already read it many times. Instead, I’ll show you 3 strategies you may not have thought of – specifically focused around the database. That way, when combined with the standard advice, you’ll be better able to improve performance of your Zend Framework application.
1. Improve Your Database Skills
From experience, database skills are all to often overlooked. I’ve met and worked with a number of developers over the years, and taken over a number of code bases. Whilst very capable, with years of experience, commonly the database appeared to be an afterthought. Sometimes it was just a data dump.
Whilst PHP applications have been almost wedded to the hip of MySQL, since the language’s inception, developers don’t seem to have the database knowledge they should – whether that’s with DDL (Schemas) or DML (Queries).
Note: I simplified my definition of DDL & DML intentionally.
I’ve seen applications created which (re)implemented so much functionality already existing in the supporting database – functionality developed by a team of developers, with accompanying tests and support. Why? I’ve seen applications which do so much processing in PHP, which would be executed much faster in the SQL query or a stored procedure.
I’m not sure if the developers in question weren’t aware of the functionality, or perhaps were unmotivated enough to learn. Either way, they made a lot of work for themselves. So firstly, please stop reimplementing existing functionality. And don’t perform unecessary processing in PHP.
Not sure whether you can or not? Does that function exist? Spend time learning about your database with the vendor supplied documentation. The time will be well spent and you’ll do your career a favour by up-skilling. Links to the major database vendors are available at the end.
What If My Database Vendor Changes?
Unless you’re Facebook, Google, or a startup, this is likely a non-argument, or an excuse for laziness.
Note: If it’s your manager saying it, email me and I’ll help you break down the resistance. There’s a few good ways to show the benefits.
It’s more than likely, per/project at least, you’ll only use one database vendor. From experience, talk of migration to another vendor is likely smoke and mirrors; at least when the true cost of that process is understood.
So get in and start learning all about your vendor. Learn to do ever more, especially the advanced stuff. Draw out every last bit of functionality you can, cut down re-implementing functionality, and watch your app fly.
For some simple examples, try these:
- Understand the difference between a Group By and Having clause
- Learn more about Group By and Order By Optimization
- Learn how to master MySQL indexing
2. Learn Key Database Features
Can you answer the following questions?
- What’s the difference between a normal and materialized view?
- What’s an Explain Plan?
- What’s an Index?
- What’s a Tablespace?
- What’s the difference between an Enum and a Check Constraint?
- What’s the difference between auto increment and a sequence?
- What’s the difference between a Subselect and a Join?
- Whats a Self-Join?
You don’t need to know all of them, but it’s handy if you do. They’re all features which can reduce your codebase, time and cost investment. Depending on your application, these will be more or less effective.
Note: The links above are taken from across the majority of the major database vendors. If it’s not your vendor of choice, the respective documentation will be not too far away.
3. Move Logic to the Database Layer
This really goes hand in hand with points 1 & 2. The closer the logic is to the underlying data store, the faster it will be. Are you familiar with triggers and stored procedures? If not the Microsoft definition.aspx) is:
A stored procedure is a group of Transact-SQL statements compiled into a single execution plan.
These are written and stored within the database itself, often accounting for large performance increases. Why? One of the biggest bottlenecks is removed, network overhead.
Whilst networks have grown in speed exponentially over the decades, network overhead’s still one of the slowest areas. If you can eliminate it, it would be worth your while.
However, as MySQL’s own documentation points out:
this does increase the load on the database server because more of the work is done on the server side and less is done on the client (application) side
But it can be worth your while to invest time in approaching performance optimization this way. With stored procedures, queries don’t need to be sent to the server and processed before result sets are returned, as they’re stored, analysed and precompiled on the server. So you’ve removed some PHP code analysis and compilation, some network overhead and SQL analysis and compilation on the server.
Some people feel uncomfortable with stored procedures however. And some of the drawbacks are covered in this recent article on SitePoint - as well as a good introduction to them as well.
Two of the key criticisms I’ve heard, are a lack of knowledge about how to develop them and versioning them. To combat a lack of knowledge, there is abundant, excellent, documentation both online and off.
For versioning, there are two excellent tools, Doctrine Migrations and DBDeploy. Both of these allow for managing queries in a versionable manner. I’m an avid user of Doctrine Migrations and recommend it highly. I don’t advocate stored procedures in every case, but they’re worth considering if your needs benefit from them.
I’m concerned I may have veered into a rant today. I mean neither to do that, nor to belittle. My intent is to encourage you in learning more about databases and to show you how, with a bit more knowledge, you don’t have to write a sceric more code – in fact you’ll likely write less.
What’s Your Database Advice?
What’s your database of choice, and what your your performance tips and tricks? Share in the comments.
If you want to know more about your database vendor of choice, here’s the links to the latest documentation. Is yours missing?