Long, dark night of “the loop”…

<stir type=”pot”>How exactly would stored procedures help the majority of WP users who are running it off MySQL versions significantly below 5.0?</stir>

Well, in wordpress’ case, it wouldn’t; moreover, MySQL 3 compatibility is so central to WP that were WP to go for a PGSQL version, I’d guess the devs would continue using $wpdb. i was just answering lambic’s question. ??

Returning result sets using stored procedures will not be faster than a tuned piece of SQL on any decent RDBMS. The time it takes to parse a piece of SQL is insignificant compared to fetch times.

There are all sorts of things that make an RDBMS like Oracle faster than MySQL, but stored procedures isn’t one of them.

Yes, but beyond the parse time, your RDBMS (of which the R is a fallacy in MySQL’s case) will then do a sequence of actions in order to find the result set, and different strategies lead to shorter or faster queries. On the one side, you do a query, and optimize the code accordingly. On the other, you do a sp, and optimize the code in a like manner. The difference is what happens immediately after: Whereas the select statement will be processed exactly the same way next time you run it, the optimizer will inspect where it could have gained time with the sp so as to run it faster the next time. The same kind of gen algs run your email spam filter: You’ll get the most of it by training them.

I’m not sure which RDBMS you’re talking about. My expertise is only in Oracle, and I can tell you that what you’re saying is completely incorrect in that case. The Oracle optimizer treats SQL the same, whether it came from a stored procedure or an external call. In both cases it will “learn” to be better over time.

I toy with the MS gadget most of the time. ??

Folks, take a step back for a second to consider the problem rather than engaging in a my-db-is-better-than-your-db argument.

There are 2 possible causes for the slowdown on hooopla’s site:

1) Poor scaling in the posts loop due to query ineffeciency, badly structured db schema or poor db engine performance.

2) Poor scaling in other queries executed by WordPress.

While there may be some cause to examine the first, the likely culprit is the second one. Does the site list links for categories or archives? Those queries are more complicated than you think and will degrade in performance as the database grows in size. How many plugins are in use on the site? Many plugins run inefficient queries and add unnecessary processing time.

If you really want to know what’s going on, try turning on SAVEQUERIES and dump the value of $wpdb->queries at the bottom of the page. You’ll see what WordPress is trying to do and how long each query takes to execute.

Is there any mileage in someone creating some sort of diagnostic plugin for this execution stuff ?

Podz, I don’t think you can set SAVEQUERIES from a plugin early enough to catch *all* queries. I can look into making a quick plugin.

For what it’s worth, if you want to do diagnostics, add the following to your index.php (before the wp-blog-header.php include):
define('SAVEQUERIES', 1);

And add this code somewhere in your site’s footer:
echo "
<div class=\"stats\">
{$wpdb->num_queries} queries, ";
timer_stop(1);
echo " seconds.
</div>
";

if (SAVEQUERIES)
{
echo "<!--";
print_r($wpdb->queries);
echo "-->";
}

Hoopla,
I personally think your problem is outside of the core WordPress code and might be some inconsistencies in your MySql database rather than the inability of WordPress itself or the RDBMS.
If you prefer and if you cannot find any good answer soon, please feel free to contact me directly at mark at wltc dot net and I can try to find a solution for you.

I know that I would LOVE to see a “diagnostics” plugin for WordPress…I have no idea what’s available in the code for diagnosing problems, and being able to get access to this would be just phenomenal. Bonus points for making the output use a JavaScript popup window so it doesn’t ruin my blog layout. <grin>

Here’s a quick plugin to do the above: jeromes-query-diagnostics

Note that the plugin can’t set the SAVEQUERIES constant itself, you have to do it manually (see plugin file for details). No JS required.

Thank you, darkcanuck and LaughingLizard. I appreciate your help very much.

I’m going to set up some tests tonight and tomorrow to see if one of the plugins I use is causing this. I’ll also add that snippet to my front page and see where the queries are taking so much time. Several people now have suggested that this isn’t typical behaviour for WP core, and my host runs such a finely-tuned server that it’s frightening… ??

PS: I’m with podz and mickelsn1 re: a plugin. I’m sure there are lots of people like me with a need to identify performance problems but without the skills to figure them out at the command prompt.

Here’s a quick plugin to do the above: jeromes-query-diagnostics

Jeez, what took you so long! ??

“If you want to see the WordPress community’s own kind of FUD at work, have a look at the answers to this WP support topic”

https://lightpress.org/post/wordpress-is-fast-mysql-is-slow-fud-at-work