When it comes to working with the WordPress database – or any application that provides an API for data serialization – I try to always stick with the API unless it’s absolutely unavoidable.
For the most part, I tend to favor APIs that provides the necessary functions for reading and writing data, and I generally assume that they have everything built into them that they need in order to make data storage and retrieval as secure and as fast as possible (though I’ve been burned by this assumption before).
In some cases, this is true; in others, not so much.
Case in point: I’ve been working on a large intranet application for someone that’s built on top of WordPress. One component of the application requires the import of a relatively large set of data in CSV format that’s also piped through a third-party plugin.
Unfortunately, there was a bottleneck in the code that was causing timeouts on the remote server.
- No matter how high you set PHP’s timeout settings, a third-party script monitor would always kill it first. I’m actually in favor of having these types of monitors running.
- Long scripts create a terrible user experience so I wasn’t happy with the performance even when I was able to marginally improve it.
- Isolating bottlenecks can be a tedious process, but can seriously pay off if you spend the time to do it.
When I finally located the bottleneck, it was occurring in the third-party plugin.
Timing WordPress Functions (Or “When Data Imports Slowly”)
To give you some context as to how the application was performing under various sets of data, check out this information:
- Nine records took 1.9 seconds to import
- 98 records took 167 seconds (or almost 3 minutes) to import
- 428 records took 3992 seconds (or over an hour!) to import
Clearly, that makes for some serious performance issues. So – thanks to the nature of open source software – I was able to take a look at how the plugin was doing it’s work, as well as take a look at how information was being saved to the database.
When all was said and done, I was able to setup a set of database queries using the `wpdb` object available in WordPress and bypass the plugin’s APIs to insert the data. This improved performance significantly.
So much that importing close to 500 records happens in under four seconds.
Not bad, but that’s not the most important takeaway especially if you’re just getting into development or are someone who is trying to be mindful of performance.
Not All APIs Are Created Equal
Obviously, this goes to show that not all database APIs are as performant as others, even if they’re built on top of an application that has solid APIs.
Personally, I think WordPress’ native APIs for data storage, retrieval, and general querying work quite well. And for the times when you need to write straight SQL, it gives us the ability to parameterize our queries which, in turn, still makes sure that we have the ability to create secure queries (though more of the responsibility is in our hands at this point).
Unfortunately, the plugin in question wasn’t as performant as WordPress’ native APIs hence the need to locate the bottleneck, look through the code, and ultimately query around it.
How To Time Your Code
Finally, here’s a really simple way to time your code when working on WordPress (or even PHP-based) projects.
This isn’t anything new and there are tons of pages about this information available on the web. In fact, there are libraries dedicated to providing rich information on stuff like this. But if you’re looking for a really simple solution that will provide you with just enough information to get timings like I listed above, then you can do so with PHP’s `microtime` function.
Again, this is a very simple way to go about testing performance. There are other facilities and tools available to provide significantly more rich information, but if you find yourself in a similar situation, then this is one way to easily run some quick tests to see which performs better than other.