[ck2]

How is query analysis hard to do on any significant website?

All queries are typically passed through a standard function.

Part of the function logs the queries and times them.

Append them to the bottom of a page or in hidden html comments when special cookie is present.

[dagheti]

I believe they are using SQL server, which keeps statistics on the most longest total runing, and longest per-run queries. It's a good idea to monitor this list and take a very close look at every execution plan that comes up. Many times you end up doing RID lookups due to non-clustered indexes that don't cover the requested data.

[farout]

MSSQL is easier to optimize at last for me than say Sybase and Oracle, which I also supported.

People can bash MS as much as they want but their products are dirt simple to use and for the most part efficient. MSSQL comes with some query plan analysis so you can look at hit and miss ratios. From this info you can make some decisions as to add additional clustered or non-clustered indexes or use stored procedure which are saved (preprocessed) for faster execution. I forgot which of the several books I used to do this. But I was pleasantly surprised at the performance increase with my occasional tweaking.

Performance was not a priority for us that is why it was done occasionally. Not my call. My main job was make sure replications happened effectively, maintain good restores and security, and create stored procedures and triggers.

I hated the ORM in Rails. I do not want magic when I know how to do it more efficiently. For example there are certain times it is better to use DISTINCT versus GROUP BY for unique values.

I miss MSSQL. I use MySQL and it is ok but not like my sweetie and, thank you, not like the ugly gorilla Oracle.

In smaller apps I use sqlite or flat files.

[brown9-2]

The article shows the SO team doing all of these things. It's about the opportunity costs of which semi-slow queries you tackle in which order. As new features are added, performance shifts around in different areas of the site.