sql

Hi,

I know drupal is heavily database run to pull a single page.

I'd like to know the average number of sql queries run to construct a tipical (front) page.

I ran devel to collect query info on one of my sites I work on, and it shows that 800+ queries ran! Is this normal? I thought it was too many for a single page. If this is more than average, could you suggest ways to reduce the the number of sql queries? Did I miss big things to check as a new comer?

Thanks.

Almer/Blank is an interactive media agency, specializing in high end Flex & Flash video application development looking for any students/interns who currently have working knowledge of Drupal and would like to learn how to integrate it with RIA’s.

We create amazing, award-winning work for clients including E! TV, Clear Channel, Apple, and Mattel. We work out of an awesome, bright, open warehouse, just off Abbot Kinney in Venice, complete with a 50-seat digital technology theater and a bar. When you work at Almer/Blank, you’ll get exposure to the highest-level of interactive application development practices, and have great extra learning opportunities, like our Intern Romps, user group meetings, and sitting in other special training sessions or courses at the Rich Media Institute.

In order to make a CRM in drupal that has a local and a remote version I had to try to synchronise the two.
I have tried to do that in three steps

Good to know is, that I made the synchronisation program in vb.net, my knowledge of PHP was to small to make it in PHP.
It will only work with mysql 5 because that has the table with meta-data (information_schema)
I don't know if mysql 4 has this but I have been told that it hasn't.

The program is dual layered. It has a data connection layer that handles the connection between the program and mysql.

So, I've had a few inquiries over the last few months about random node generation as I have implemented it successfully for a client. I have posted a full write up of it on my blog. Feel free to comment on it here or on my blog; I will post a patch when I have a chance and if there is interest.

Article link: http://earnestberry.com/node/13

I've released an in-development version of PressFlow Transaction for developers interested in convenient encapsulation of SQL transactions. The key features are intelligent use of scope for COMMITs and ROLLBACKs as well as safe, intelligent nesting of transactions to get exception-like semantics.

Usage details are on the project page. Requires PHP 5.

(I posted this to the High-Performance group because encapsulating updates in transactions can dramatically improve performance.)

So, you want to speed up your large site? Well, start by cleaning up your SQL if you're accessing large tables. We are running Drupal on top of MS-SQL for a client. We have one table that has 7.5 millinon rows in it. Now, I had mentioned what I am about to talk about in another post before, but I know have some hard numbers to base it off of.
So anyway, this table had 7.5+ million rows, and the page was loading mighty slow. Running it 10 times, Devel query showed where I was bleeding...more like gushing time:

<

ol>

According to my devel module logs and lots of watching the query lists, the query that costs us the most in all of Drupal core is this one:

SELECT DISTINCT b.* FROM blocks b LEFT JOIN blocks_roles r ON b.module = r.module AND b.delta = r.delta WHERE b.theme = S AND b.status = D

Let the brainstorming begin; what can we do to improve it? Are there enough indexes? Can we avoid the DISTINCT? Are there other options?