Too many SQL queries

I agree, and my hosting company says I must disable Quotes because it's massively overloading MySQL -- or pay for a dedicated server if I really want quotes. The problem is that I'm showing Random quotes, more than 12,000 of them.

I've used prior versions of Quotes for some time, same number of quote records, but the problem has been increasing as "features" have been added to the modules. I think that's the core problem -- all the extra goodies to dig into the quotation author, bio, categories, etc, it a big load.

The solution might be to offer two modes, admin-controllable. One does the most basic quote generation, like it used to, optimized for efficiency. The other mode has all the extra features, with the caution that it's costly to execute frequently.

For what it's worth, there's another way to architect this. Years ago I wrote a random quotes system for another Web platform -- entirely different from Drupal (not SQL, not PHP) but facing similar performance challenges because of the large number of quote records and high number of site hits. My solution was the following.

DAILY OR WEEKLY: A cron "get ready" task runs to prepare the database:
1. Once a day, a script ran that used all my "what to show"/config parameters to load a single field of each quote record with the entire text/HTML/CSS needed to show the quote in full. So only one field of one record was actually invoked each time a quote was displayed.
2. The script numbered all the records sequentially, then it stored the TOTAL records in a global variable (actually a little on-disk text file to assure it would always be there).

RANDOM DISPLAY: To show a random quote requires just this:
1. Using the stored TOTAL records as a max, generate a random number (rounded or truncated to an integer). Depending on the programming system this is usually quite efficient.
2. Go to that "record" number and output the one field that contains the entire formatted quote text/HTML/CSS. One query, one field retrieved, no further database manipulation.

The load on the server from random quotes dropped 99% -- it was an amazing difference.

That's it. The database system does the absolute minimum to show each random quote. The real load happens just once a day (or week or whatever is desired) to prepare the quotes table's records.

Which version did you have the best results with.

I'm still using drupal 5 on my production sites so do not have a good 6x reference site to work from. I've placed emphasis so far, when time allows, to 7x which is stalled at present due to time constraints. With 6x giving 20+ queries per quote, let's see where it broke and fix it first.

The cron options is a good idea, a txt file stored outside the root directory and included would definitely lower overhead while minimizing security threats. sure it could be set to read only but still prefer to keep it out of the public grasp.

The main question is which version of the quotes 6 series to start with.

I don't see how a single random quote can be 20 queries. There was an issue some time ago on what is the best way to code the query, but the differences in the methods was nearly negligible until one had a lot of quotes. You may have reached that level.

Are you sure that the random quotes block is responsible for 20 queries? Or could they be coming from elsewhere?

I didn't come up with the "20 queries" determination, I just know that it's quite a MySQL load, much more than once upon a time, perhaps Quotes in Drupal 5.

So, I don't know which Quotes 6 version might be a starting point, other than to suggest an architectural change to move most of the workload "offline'. My earlier system had the same evolution. Originally, all the quote selection, generation, formatting was done at run-time. But it just got too slow, and if affected many other aspects of both the database server and the web server. The 99% of the code was fine, it just needed to be split into two chunks.

Simplifying a tad (please correct where wrong), my sense is that the current module does this:

1. enter quotes (and related info) into the database -- once in a while

2. configure how quotes should be generated and displayed -- once in a while

3. on-demand, triggered by web hits, generate ONE desired complete quote, involving queries to get all the needed data, and the code to organize and structure it per the configuration, then select a random result and output it to the screen. (I'm not sure where in the current process the "total quote" HTML is built.) With this approach, virtually nothing happens until a web visitor hits a page that has "show a random quote" code, then 100% of the entire job runs -- every time such a request is made, which might be hundreds of times a minute (assuming no help from caching or other things that aren't especially compatible with random quotes).

My suggestion does all of this, but breaks up the work by splitting the one output step into two separate tasks.

1. Enter quotes (and related info) into the database -- once in a while

2. Configure how quotes should be generated and displayed -- once in a while

3. On-schedule, generate EVERY quote and store it. This does all the same things to create the complete quote, with whatever queries and code is necessary. But the result is then simply put into a "total quote" text field in the same record. Another field of that record gets a sequential pseudo record number (per a counter of all quote records in the table). Then, the total count of quote records is stored in a local text file. This step comprises 99.9% of the work, so schedule it once a week or maybe just when quotes are added/changed. Run it in the middle of the night or weekend or known slow time. Instead of running several times a minute triggered by web visitors, all this work is done periodically, "offline" with virtually no impact on the site.

4. On-demand, triggered by web page hits, get the locally stored count of total records, use it to generate a random "record" number (very fast), then a second line of code gets that ONE record and outputs the one "total quote" text field to the screen (very fast). Thus less than 1% of the total work happens at runtime. That's essentially 2 lines of code and just one database action, using minimal memory and minimal data access.

The point is, there is no simpler database action than to go to one known record and send the content of one text field directly to the output, so this is the maximum efficiency.

Since a random quotations (or other facts/trivia) system is destined to get large and then larger (more then 12,000 in my case), even small changes in efficiency have a large multiplier effect. With hundreds of simultaneous visitors triggering pages containing random quotes, it is a huge improvement to do the absolute minimum code+database activity each time a quote must be displayed.

It would be interesting to see how you are using this module. I certainly never envisioned anyone with that many quotes - my highest site is at 178.

On-schedule, generate EVERY quote and store it. -- This sounds like a CDN, and is not generally the "Drupal way." What do you do if the node is updated? How about dynamic links, votes/ratings, etc. that don't lend themselves to being stored?

Certainly the block code could be split out, as I have done on other modules. It just seems like over kill here (it becomes a second module). I could be wrong. I wanted to split the add/edit code out, but the core developers weren't any help in figuring out how to do that (menu complications).

The admin functions are already split out into a separate file.

get the locally stored count of total records, use it to generate a random "record" number -- Keep in mind that quotes are nodes, just like "story" or "page" or "event" or any number of other content types. They are intermingled in the node table. Certainly counting how many quotes there are is easy (there are two places where that is already done). But how does that help figure out which "nid" it is that you want randomly? Let's say that I have four quotes; they have node ids (nid's) of 27, 583, 1351, and 2349. My count tells me I have 4 quotes, I generate a random number between 1 and 4 - and let's say I get 3. As a human, I know quickly that means I want node 1351, but how do I code that decision? If I do a query that gives me every nid, then I have kind of defeated the purpose, not to mention potentially used up a lot of memory. I know the random query is not the most ideal for large numbers of quotes, but the fix would have hurt the rest of us who have small numbers.

I am certainly open to performance improvements, but they need to be quantifiable improvements. They also must translate to D7 because that conversion is well underway. Do you have any concrete suggestions?

I certainly don't mean to be saying there's anything wrong with the current module, just recognizing how it works in my situation, which apparently is different from the design target.

It seems to presume a smallish number of quotes, and perhaps not very many pages calling them randomly. So generating the complex output on the fly is OK.

It's just that my experience is to have many, many quotes (a typical book of quotations has thousands of entries, for instance). So I came up with a different way that puts the workload offline, scheduled as needed, and very efficiently stores and serves up the live quotes comprised of "dead" text. This approach was necessary for a site that had many frequent calls to the random quotes system.

To clarify, each quote gets a PSEUDO record number, separate from the real NID. This is done by processing only the quote type records, incrementing a counter from 1 to the final record, storing the pseudo record number in its own field. The final record number/max count is then stored somewhere else (I use a local .txt file). Non quote-type records are ignored.

Then, to display a quote, the max number is used to generate a random integer in the range 1 to max. That is used to retrieve the matching pseudo record number -- a SELECT of a single field/column of the record WHERE pseudo-record-number = random-integer. It's virtually instantaneous. The rest of the records in the node table, and the true NIDs, don't matter for this action.

Regarding generating and storing the entire quote, this is where the notion of forking the "quotes" module arises, depending on the desired use.

In my situation, we just display random quotes, nothing live about it, no embedded links, no votes, just some "dead" text to display. It might be quotes or trivia or whatever. (A variation is "this date in history" which randomly selects on a date field rather than a pseudo record number.) Assuming the quotes are called from thousands of nodes (my case), at any given moment the quotes system might be getting hundreds of hits. This requires a simple, super-efficient system.

The other possible use is the direction this module has taken, essentially being a "famous people and interesting things they said" database with several interesting features. Obviously perfectly fine, it's just a different approach for a different purpose.

Maybe the "perfect" quotes system would be a front end to enter the quotes and names and bios, etc, and manage this data. A config system would determine what to do with these elements. The output system would really be two systems, one to spit out simple random pre-generated text (if this mode is specified in the config). The other more complex system would generate output on-demand, providing all the extras, links, etc. I don't know if this should be separate modules or just conditional modes of a single module.

Only the simple system is a candidate for scheduled generation and storage of the full desired text of each record in a single field. It could be an additional field in each quote node record, OR it could be in a separate table (which needs just two fields: pseudo-record-number, and body-text). Either way, this approach can handle very large collections of quotes efficiently.

I built the same thing several years ago in IBM Lotus Domino, which is non-SQL and very inefficient compared with MySQL. Yet this approach lets Domino very quickly serve random quotes from a total of 12 thousand to be embedded on more than 10 thousand content pages that are dynamically generated. It's still in use.

If I'm still not clear, please ask again.

I can see where a static records would be faster than querying a database in certain instances. I have used this approach in the past for random ads before I started using drupal. In a situation where thousands of quotes are randomly displayed without the need for a permissions check this could be ideal to lower server resource demand.

The hind sight to this is that you could not use many of the drupal modules with it. Would not be really necessary though if your only generating a random quote in a block, for display only, and maybe a title or author link to click on. At that point the quotes module could take back over if required.

The admin area could have an option for this feature and the static record could be updated by cron. On a large database it might be better to rewrite the static record rather than updating it and would have to be done on non-peak operation hours. The only thing I see is at what point would it be better to go back to a single database call to fetch the record for performance reasons and also php memory allocations.

You are correct. The design was that there would be generally only one block generating a random quote. I have trouble fathoming a site that is otherwise.

It was also designed that there would be (optionally) a single page listing all the quotes.

There could then also be additional blocks of the other types.

I believe I understand what you're after. Drupal is designed for dynamic formatting of nodes, not pre-formatted. The only time this is generally otherwise is with caching, where the idea is that the content is not changing in any manner. What you are describing is more like what is called a content delivery network (CDN).

It might be possible to use a special case cache table for this, but it would be more expensive for users with smaller loads. It could get pretty complicated with taxonomy terms and by-user content look ups, both of which are possible in the blocks.

I need to set up a test site (my old PC died) and see if I can figure out where all those queries are coming from and if any of them can be bypassed.

If you don't need the random blocks re-generated on every page request, you can set them to have a lifetime so that they don't refresh every time. I have one site that has a "quote of the day" where the choice is random, but is only updated once a day.

I didn't realize there could be other ideas about showing random quotes, and I guess neither did you. The two ideas seem to be, eeither simple random quotes to liven up web pages, or a famous quotations library with a variety of helpful features for that purpose.

For instance, I don't want to ever show a list of quotes, because it's not about the quotes, it's just random interesting little nuggets of text on various web pages. Such quotes have nothing to do with users or taxonomy or roles or access control. They are just text that gets displayed wherever the block appears (display of the block itself can be controlled, if desired).

To see how I implemented it years ago in IBM Lotus Domino, here's a link to a page of the site. The random quote is near the bottom of the page. Refresh the page and another quote randomly appears. http://my.advisor.com/pub/FileMakerMaxAdvisor

There are about 12,000 quotes on my.advisor.com. The site has approx 10,000 pages of articles. Each such page has a random quote on it. The site at times gets lots and lots of hits. Yet even though Lotus Domino is not efficient, the quotes system works quite well.

As I explained, the quotes are static text, each quote a separate record in a Domino table that is updated only as necessary (when quotes are added or edited). The only dynamic aspect is that the quote text elements are wrapped in CSS. (The random quotes on my Drupal site www.advisor.com look almost exactly the same, but they are disabled right now due to the performance hit.)

I finally got a site set up to test this.

On my front page, after clearing the cache, I see 239 queries. Refreshing the page, I see 155. So comparing apples to oranges must be avoided when reporting performance issues.

I see five (and only 5) queries related to the block that I have any control over:

The first one is to get information about how you have the block configured. I don't see how this can be avoided.

The second one is to select the actual quote to display.

The third one is actually done by the node module to get the node.

The fourth one gets the author information and is actually an API interface from the node_load.

Te fifth one is because I have comments enabled for quotes.

With some convoluted JOINs and code, it might be possible to combine the 2nd, 3rd, and 4th queries. Possibly hawkdrupal's suggestion might come into play here. However, on my site, that would save less than a millisecond, at the expense of some other site missing potential API calls, for example the comments.

And further, hawkdrupal, what would happen if someone changed something about the way the quote was displayed, such as enabling or disabling comments. Are you asking the module to somehow know that these changes were made and recycle through all the nodes to get the new formatting?

Your usage is no different from what I have on my site. What is different is the number of quotes to select from (I only have 173). In another module, someone questioned the performance of MySql doing a random selection. The issue was never resolved, but that's exactly where I see your problem being. If you can come up with a solution that doesn't break the majority of sites with only a few hundred quotes, I would be happy to test it out.

BTW, I tried creating a similar block with Views and it increased the query count by 24.

All kinds of things are going to contribute, Path (any URL alias) is one of them. Just having a block defined will also contribute. I have no control over those queries and I didn't count them.

closed, no response for over 1 year. Feel free to re-open if this is still an issue