Site performance (Complex front page layout + Views)

Hi all,

I am currently working on a project to convert a static HTML newspaper site (with about 1700 articles in English and Spanish) to Drupal 6. Most of the data is imported and the site is pretty much assembled, but I am running into some major performance issues that I need some help with.

The basic content type used throughout the site is "Article", which has about a dozen CCK fields.

Some background about how the site is set up. I have used Views to build most of the site as follows. The newspaper has several core Departments. For example, "Music", "Politics", "Society", "International", etc.

For each Department, I created a View. Then I created two displays: "Page" and "Block". The Page display is used to display lists of content (with exposed filters and a pager to limit the lists to 20 items). The Block is used for front page display of just the titles of the three most recent articles in that department. I have a Department Taxonomy to file each article, then I have another Taxonomy (with the same terms as Department) to pick a "Primary Term" - as of now this is used to pick where the article is displayed in lists of content on the front page (i.e. since the same article is often filed in multiple Departments, I don't want the same posted in multiple blocks on the front page), but it will probably be useful in other circumstances too.

Then I have several Views associated with Node Queues to display the three most recent articles entered with title, author, date, description; a View of a Node Queue to display the lead story with Photo, and a couple other Views also tied to Node Queues to display various featured items, etc.

So here are my questions:

1. What is the best strategy to reduce the number of queries for the front page? I have read that the maximum limit of queries one should aim for on the front page is 1000. Running the devel module last night, I accessed the front page with the following result:

Executed 4879 queries [!!] in 4284.22 milliseconds. Queries taking longer than 5 ms and queries executed more than once, are highlighted. Page execution time was 16505.7 ms

That is not good. From there I navigated to an single article node with the result:

Executed 326 queries in 4264.23 milliseconds. Queries taking longer than 5 ms and queries executed more than once, are highlighted. Page execution time was 5483.45 ms

I navigated back to the Front page which should have been cached by now:

Executed 481 queries in 3319.64 milliseconds. Queries taking longer than 5 ms and queries executed more than once, are highlighted. Page execution time was 9122.86 ms

And, looking at a listing of content (i.e. departments/society) yields:

Executed 344 queries in 2112.89 milliseconds. Queries taking longer than 5 ms and queries executed more than once, are highlighted. Page execution time was 5470.19 ms

Now clearly this is not very good. I can only imagine what the picture will look like when there are hundreds of logged in users and thousands of page views each day...!

Right now I am more concerned about the number of queries than the page execution time at this point as I'm fairly certain the latter is impacted to a pretty high degree by an incorrectly configured VPS (which our tech person is going to sort out).

So how can I reduce the number of queries? Am I taking the right approach in creating a View for each Department? Or does it make more sense to create a View called "Department Blocks" and add displays for each Department taxonomy term, and likewise, a View called "Department Pages" and add displays for each term?

What are my options for front page displays? I do not want to use Panels as it seems too buggy and prone to crashing (from my own experience with it). I don't feel comfortable using it on a production site.

2. What is the best practice for using Taxonomies with custom content types?

For example, I have this option checked: "Save values additionally to the core taxonomy system (into the 'term_node' table).
If this option is set, saving of terms is additionally handled by the taxonomy module. So saved terms from Content Taxonomy fields will appear as any other terms saved by the core taxonomy module. Set this option if you are using any other taxonomy application, like tagadelic. Otherwise terms are only saved in the cck tables and can only be accessed via the node or a view."

Now I am not using Tagadelic, though I am using the Related Content module to automatically generate related content blocks when the user is viewing an article. Is it a better practice performance-wise to not have these terms saved to the term_node table, or does it not make much of a difference? If it makes a big difference I could change that and also ditch the Related Content module.

3. Views and Depth filters

The Department "International" uses depth filters to display all terms that are children of the parent term, International. I do this also for regions. For example I have created a display called "Africa" for the International View which uses a Taxonomy depth filter to include all countries that are in Africa. Does this slow things significantly? For the Africa display example, would it be a better practice to use the Taxonomy filter without depth and simply select all the taxonomy terms I want displayed, i.e. "Africa", "Sudan", "Egypt", "Nigeria", etc.

4. URL aliases

Part of this project entailed mapping the old article URL aliases to the new site. Thus each one of the 1700 articles has a URL alias that copies the old site's URL and ends with ".htm". This is consistently one of the red flags from Devel's page load output (usually about 72 ms or so). Anything that can be done about this? Is there another, faster method?

5. Any other suggestions?

I am not really looking for server optimization advice so much as tips for fine tuning my Drupal site. I want to get those queries down, then deal with server optimization. Any advice or pointers would be extremely welcome.

Thanks in advance to your responses and help!