
Stack Overflow Makes Slow Pages 100x Faster By Simple SQL Tuning  - jpmc
http://highscalability.com/blog/2011/5/2/stack-overflow-makes-slow-pages-100x-faster-by-simple-sql-tu.html
======
jasonkester
Remember 10 years ago when this was a solved problem? There were only two
rules: "always use stored procedures" and "never build dynamic SQL". Doing
stuff on relational databases was fast.

Granted, you often had to actually write Stored Procedures by hand back then.
But then if you look at the LINQ in the article, you'll notice that it's
pretty much exactly the SQL you'd stick into your stored procedure, just
backwards. Had they not bothered introducing LINQ in the first place, they'd
have had their 100x performance boost from the get go.

Naturally, SP's are not some magic 100x'ing cure-all. They're just a non-
generated version of your SQL, which means that you're guaranteed never to
have your ORM go nuts and build some monstrosity like the one outlined in the
article.

You still need to tune your SQL by hand, but at least you can tune _the SQL_
rather than some not-particularly-helpful abstraction on top of it.

~~~
ssmoot
This is the old Microsoft Misinformation. Stored Procedures are not faster.

SQLBooksOnline actually has this information clear as day, but MS Evangelists
like Rob Howard (went on to create Teligent) would go around spouting the
"sprocs are faster!" line and no one would ever think to point out the
discrepancy. It just became religion for the masses of MS platform Developers.

ADO.NET (and whatever it's called these days) executes all _Parameterized_
queries through (see if I can get the casing right) the sp_execsql procedure.
This means that the Query Plan for _every query_ run on ADO.NET is cached.
IIRC the only additional step Stored Procedures avoided was Parsing. Execution
for either would come up with the same, cached plans, but sprocs wouldn't have
to parse each time.

Who's willing to bet that the SQL Parser, with decades of optimization work,
can parse 100% of your queries in a few hundred nano-seconds?

In practice then, that savings doesn't matter. In side-by-side tests I did a
few years ago (that anyone with a few minutes can replicate), there was
effectively _no_ performance difference between the two. So the parsing
savings were swallowed up as noise in the actual network-latency/query-
execution formula.

At the end of the day then, sprocs are not the solution. The solution is to
avoid dynamic SQL, and use _Parameterized_ queries. Generated queries that
aren't _Parameterized_ do not cache their query plans (last I knew, but it's
been years, so maybe that's changed) and won't perform as well as sprocs.

The problem isn't LINQ then. If you know how the gears underneath it all fit
together, you can easily avoid sprocs with no negative performance
repercussions.

~~~
nl
Ah.. but stored procs _are_ faster if you are executing business logic in
them, because you can avoid round trips.

I think it's a terrible idea, but I've seen multiple places where there is a
very blurred boundary between stored-proc-for-data-access and stored-proc-for-
data-access-after-a-bit-of-business-processing

------
swanson
The linked article from Sam's blog:
[http://samsaffron.com/archive/2011/05/02/A+day+in+the+life+o...](http://samsaffron.com/archive/2011/05/02/A+day+in+the+life+of+a+slow+page+at+Stack+Overflow#)

I found it to be much more interesting and a cool look at how he went about
actually finding the slowdown and the steps he took to fix it.

~~~
riledhel
Thanks. I always find this articles more entertaining/educational.

------
n_are_q
If you are building anything more complex than a blog site and expect to take
a decent amount of traffic, to the point that you may in fact care about
optimizing at all, going with an ORM that writes sql for you is a really
really bad idea. I really don't understand the fascination with ORMs today.
Some sort of sql-to-object translation layer is no doubt a great thing, but
any time you write "sql" in a non-sql language like python or ruby you are
letting go of any ability to optimize your queries. For reasonably complicated
and trafficked websites that's a disaster simply waiting to happen. This isn't
just blind speculation on my part, I've heard a great many stories where very
significant resources had to be dedicated to removing ORM from the
architecture, and the twitter example should familiar to most.

I would go so far as to say that sql writing ORMs are a deeply misguided
engineering idea in and of itself, not just badly implemented in its current
incarnations. You can't possibly write data access logic entirely in your
front end and expect some system to magically create and query a data store
for you in the best or even close to the best way.

I think the real reason people use ORMs is because they don't have someone at
the company that can actually competently operate a sql database, and at any
company of a decent size traffic-wise that's simply a fatal mistake. Unless
you are going 100% nosql, at which point this discussion is irrelevant.

~~~
nettdata
I disagree.

ORM's aren't a problem at all as long as you have the ability to override
problematic queries with named queries, etc.

ORM's can provide very real advantages when it comes to caching, development
time, etc., as long as you review what the ORM is doing and notice when it's
doing it wrong.

I've just spent 2 years architecting a high transaction global video game
system using an ORM, and it worked well. In our case, the ORM provided
acceptable SQL for about 85% of the queries, and we overrode the rest.

The ability to quickly and easily allow the developers to write their own SQL,
to be reviewed later by a DBA, was a life saver. Combine that with our stress
and load testing, it was easy to see where the hot spots were and deal with
them effectively.

The problem comes from people who rely on the ORM to do everything for them
without truly understanding how it works.

ORM's, like anything, are a tool, and there is a time and place for them.

~~~
n_are_q
Wrapping both caching logic and database access in an ORM like system is no
doubt the right thing to do. Letting front end developers write queries to be
converted by an orm and reviewed by a DBA later - in my opinion that's not the
most efficient method of development. I probably would have invested in an
extra DB person or two to help write the data access logic. But hey, I can't
argue with results - if it worked for you that's great. But as a general
statement I think that sort development methodology is highly conducive to
errors and systematic problems that would not become evident until later, and
at that point take a great deal of effort to fix.

~~~
nettdata
The two big systems I architected where I made the decision to go with ORM's
were the online EA Sports system (all EA Sports games on all platforms,
currently running in a 7 node Oracle cluster), and most recently, the Need For
Speed World Online system. We launched the EA Sports system with Madden, and
went from 50 to 11 million users hitting the DB in less than an hour. Then we
rolled out the other EA Sports games. Needless to say, both systems were
slightly bigger than a simple blogging site.

In both cases, we had a large number of smart developers who we empowered with
the use of an ORM; they understood the domain model, and they didn't have to
worry about waiting for a "DB type" to write stored procedures, or develop a
data model, etc. As a matter of fact, in both cases, I was the only DBA on the
project, and it was a predominately part-time role. We'd meet, ensure we were
all on the same page with the object/data model, and then they'd go and build
it. The developers were able to immediately build and run and test and
integrate something that was functional and operational, when they needed it.
This was HUGE, and something that most people don't properly appreciate.
Timelines were already insane enough as it was, the last thing we needed to do
was artificially constrain ourselves by waiting for other (db) devs before
work could go on. Especially when requirements had the potential to change
from one day to the next.

In both situations, we took advantage of very, very sophisticated testing
procedures that would happen nightly, both functional and stress/load, and it
pointed us at the bottlenecks of each nightly build that would require tuning
and investigation. We intentionally set up our testing to be able to monitor
and test the effectiveness of the ORM, and to point it out when it didn't work
efficiently. The devs would do the majority of the heavy lifting with the
initial data model, and the results would be tested, reviewed, and then
modified if required. The performance modifications were not a lot of effort
to fix, either. Usually it was a very slight data model change, or using a
named query to take advantage of a database-specific features. And CLOBS.
Every database seems to handle them differently, so we had to hack some
solutions.

Having done large scale database development for almost 25 years, using the
classic stored procedure approach and the ORM approach, I'll say again that
ORM's are a great solution for certain projects with the right staff, and
aren't a crutch or some lazy choice if used properly.

~~~
n_are_q
My experience is from writing a bunch of middle tier code at MySpace in the
06-07 time frame, the myspace hey days when they were pushing more traffic
than google (true story). Anyway, the user facing product might have sucked,
but we did scale (that's why friendster was friendster and we were myspace :).
In an environment with 450+ million users, we had extensive caching systems
and still had to use every sql trick in the book to get our systems to scale
well. I know because my job was working with the DBAs to bridge the sql and
front end worlds together. I can say with great certainty that front end
developers who did not know sql and were simply following a logical object
model would not have produced code that scaled in our environment, there were
way too many things that were done that were extremely non-obvious. Since
myspace i've been working at a python/postgres start up where we've been
applying the same principles pretty successfully, at a much different scale of
course. If nothing else, i think the no orm approach will at least give you
more bang for your buck.

Separating your data access code out of the application logic also allows you
to change it much more easily as data conditions change, including on the fly,
without an application deployment. That's often extremely useful.

MySpace scale may be at an extreme end of the spectrum, but we had formidable
hardware to throw at it too (although x86, so nothing TOO crazy). So I think
the ratio of hardware to scale at other sites is comparable, and so I think
the same lessons apply. I have no experience working with oracle, but would
you say that a 7 node oracle cluster is some pretty serious hardware? I myself
really don't know, but it is a question I have :).

EDIT: I'm not discounting your experience, i just want to point out that i've
experienced conditions where I think the orm approach would have broken down.
If others have had different experiences, the more data points the better, but
i think the scale/complexity/cost(hw) ratios play into the debate as well.

EDIT #2: Oh and I forgot to mention that the automated test suite you had is
an incredible asset, and no doubt made it easier to discover problems early
and deal with them effectively. But you do have to invest resources in
creating one, and something like that is no small cost at a start up.

~~~
nettdata
The point of my post was to say that if you take a serious look at the ORM you
want to use, fully understand the issues you may have with it, design/adapt
your development process to help mitigate the issues you may run into, there
are huge advantages to using it.

I was just pointing out that ORM's are indeed quite effective in online
systems that are more complex than a blogging site.

If you're going to say "no, don't use it", based on a development situation
that is very much an outlier (MySpace), and use that experience to discount it
for any but trivial use, then I'm not sure what to say.

They can and do offer real-world advantages with minimal downside if you treat
them like any other tool, and not use them blindly, in reasonably complex and
large systems, as I've tried to demonstrate.

As to your environment, the data requirements were quite different than ours.
Our systems were more like online banking systems; very much an even split of
fast writes and reads, transactionally bound to third party systems (in-game
payment, in-game "real time" use of consumables, etc), real-time analytics for
fraud detection, etc. We were very much high IO, and our caching
opportunitites were few and far between.

And in our environment, we HAD to have sophisticated testing. I ensured that
the stress and load testing was done so that we could directly simulate the
load of our expected user base, with realistic profiles, in order to better
engineer our databases and disk IO. It also allowed us to measure the impacts
of feature additions, etc. If it failed in Production, it made the news, and
we had millions of gamer-freaks bitching everywhere.

In my case, the middle-tier was not an issue... we enabled minimal caching on
a per-box basis, and other than that, they were stateless, and we could
add/remove them at will; the application WAS the database.

And you can still abstract various parts of the database while using an ORM.
We did write a few special stored procedures, and used some forced query
plans, views, etc., to tweak the performance.

And yes, Oracle can scale out quite well. Cache Fusion, high speed and low
latency interconnects, and shared block access provides incredible scaling
without having to do anything special in the middle tier.

~~~
n_are_q
It's interesting to hear that has worked well, obviously this wasn't a small
project. Your point about knowing how to use your tool definitely rings true.
Also interesting that you had a use case where data loss and integrity
actually mattered and in real time, unlike a social network or most start ups
operating today. Going with a heavy oracle system instead of trying to roll
your own creative distributed architecture definitely seems to make sense in
that scenario. Just out curiosity, was this Java/Hibernate?

~~~
nettdata
On one system we used Java/Oracle/Hibernate and went with the big single
cluster. The other system was a .NET stack, using NHibernate and a large
number of SQLServer instances. We also worked with Microsoft on integrating
their latest (at the time beta) caching servers. We did indeed have to roll
our own distributed architecture in that case, but it's not like we had to
drop ORM to do it.

------
baddox
The term "NoSQLite" shouldn't be used, or at the very least it should be
hyphenated "NoSQL-ite." The existence of a little database engine called
SQLite makes this term more than a little confusing.

~~~
alnayyir
.NET programmer probably isn't aware SQLite exists.

~~~
jrockway
That's hard to justify these days, however. They even use SQLite on the Airbus
A350 flight software!

~~~
alnayyir
How many .NET programmers work on Airbus A350 flight software?!

------
smackfu
This seems like a fairly common issue with frameworks that convert things to
SQL behind the scenes. If you aren't paying attention, it will run a query for
each object, instead of one for the whole page.

~~~
mike-cardwell
SQL is quick and easy to write. I never understood why people decided it would
be a good idea to add another layer of abstraction on top in various web
frameworks.

I don't think it saves dev time at all. It just makes it more difficult to
know what queries you're running by looking at the code.

~~~
MartinCron
In my experience, it saves a lot of dev time, using my current ORM (Linq with
Entity Framework 4.0) I can get probably-fast-enough CRUD against a new entity
in just a few minutes without writing any SQL at all.

When it's not fast enough (high volume, specific complex queries, whatever) I
can write specifically tuned SQL.

Performance tuning on anything that isn't an actual bottleneck is waste.

~~~
jasonkester
Realistically, you should never be writing your own CRUD, whether you use an
ORM or not.

CRUD stored procedures can be generated directly from the database schema,
wrapped in C# objects using the same code generator, and compiled into your
project whenever you make a schema change. That gives you all the advantages
of an ORM, without any magic runtime SQL generation.

~~~
MartinCron
Serious question: Why is having stored procedures generated at design time
better than having dynamic SQL generated at run time? It's not like there has
been a meaningful performance benefit for the last several years.

~~~
fendale
I'd also like to hear a reasonable answer to that question.

For me, the only advantages of stored Procs over straight SQL (generate or
hand coded) are:

* Security - you can revoke rights to directly access / change the tables from the app layer, making all access be through procs. However, in general you can probably create a similar security setup using views.

* Many SQL calls that need to be run as a set to give a single answer - this avoids the round trip latency of firing many SQL calls from the application.

I'd am genuinely interested to hear of more advantages.

------
bobx11
Think of it the other way around, their engineers didn't optimize any queries
and they don't have a dba watching for table scans or nested loop joins. To
the amateur crowd it looks like "wow optimization" but if you've been
developing seriously for your career this should look like "you have a lot to
learn".

~~~
pradocchia
Yes, but StackOverflow does have a DBA, and very good one at that:
<http://www.brentozar.com/>

I guess he doesn't deal w/ this level of optimization, or it never hit his
radar.

------
jarin
I don't think Stack Overflow's case is a case of "see, you can scale SQL
databases". I think it's more of a case of "they've forced themselves to HAVE
to scale SQL databases by tying themselves to .Net and MS SQL Server".

There's nothing wrong with relational databases, it's just that many of the
NoSQL databases are newer, encourage denormalization, and implement things
like intelligent sharding and mapreduce out of the box.

Which one you prefer seems to depend on whether you're more systems-oriented
or code-oriented (if you have someone dedicated to tuning your database, why
not just let them worry about it?), or whether you're just forced to use one
by virtue of the platform you're using.

~~~
beaumartinez
I think it's more a case of "boring but tried and tested" or "new but
potentially unreliable". Would you risk your business just to say you use
_latest tech buzzword_?

Things like NoSQL are neat to play with in your spare time, but I'd think long
and hard before I'd use them in actual production code. I seem to remember
hearing about Cassandra causing woes in the past, for example.

~~~
jarin
If you're putting your database on your feature page, you probably have other
problems than scaling.

It's not about "latest tech buzzword", it's about using the tool that is most
appropriate to the job. Cassandra worked up to a point, but it ultimately was
not the right tool for the job at _Twitter and Facebook scale_. Is that cause
to be dismissive of _all_ NoSQL databases?

I agree that you should give careful consideration to your technology stack
before putting it into production, but to me that means considering all of the
options available, not just the old stuff.

~~~
silverbax88
This is true, but it should be pointed out that Facebook scales very poorly.
Messages disappear/reappear, friends are suddenly missing and suddenly back,
your own posts suddenly vanish.

That points to poor caching and poor scalability.

~~~
glhaynes
I think Facebook does a pretty decent job considering that they have hundreds
of millions of users, with many of them hitting the site many times a day.
They've got an absolutely _tremendous_ amount of traffic.

------
jorisw
They turned on indexing on the table and went to a JOIN type query. Boy.
Trouble believing steps 1 - 5 were necessary to come to such a rudimentary
fix.

------
stcredzero
_The N+1 problem was fixed by changing the ViewModel to use a left join which
pulled all the records in one query._

This is a classic ORM problem. The usual solution: apply a Declarative Batch
Query. I've even written one of those things! The reason why you might want to
code a Declarative Batch Query option for your Object Relational framework?
You can then apply it to similar problem queries in a few minutes total and
one line of code.

The first operation I optimized went from 2500 SQL calls to just 40, which is
an over 50X speedup. And yes, the entire declarative mechanism was written in
about a week to satisfy a routine "Change Request," and yes, it ran in
production on a heavily used trading application at a major multinational.

------
richardw
I see 10x speedup was given by an index. A while back I was helping a customer
with some serious performance issues, and I pulled together a few procs that
look at e.g. indexes, most-locked-objects/whatever over the whole DB. I've
seen this below trick referenced many times but the source article seems to be
here:

[http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-
usi...](http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-
missing-index-dmvs.aspx)

It gives an indication of the highest-impact indexes that are missing.
Obviously there's room for experts to tweak, but for most it's an excellent
quick tool. Don't create every index it suggests, obviously, just use it as a
guide to look for hotspots. It's nice because often it'll surprise you with
areas you had no idea were an issue. And it takes a few seconds to run.

------
gary4gar
"__A NoSQLite might counter that this is what key-value database is for. All
that data could have been retrieved in one get, no tuning, problem solved. The
counter is then you lose all the benefits of a relational database and it can
be shown that the original was fast enough and could be made very fast through
a simple turning process, so there is no reason to go NoSQL.__"

Moral of Story: Relational database should be used in most cases. NoSQL is
overated & extra-hyped.

------
JoeAltmaier
There were a number of tools involved in the analysis, including code
examination.

Can this loop be closed and automated? Why do we all keep having to do this
manually?

~~~
jerf
Arguably, this particular failure case is what has been automated. It isn't
actually necessary to have an ORM that all-but-requires N+1 select problems. I
wrote one once that could join across an arbitrary number of tables in on
shot. Alas, it is now gone in an acquisition. But I know it's possible.

Part of the problem is that I think most ORM authors know OO but don't
necessarily grok SQL, so you get the same failure cases over and over again.
It's not easy to write an ORM that can do arbitrary joins and it's borderline
impossible to retrofit one that didn't have it built in from day one. The
other big failure that you get is a failure to support group queries or
arbitrary selects.

------
chopsueyar
Imagine an ORM causing a speed issue.

 _Performing a code review the code uses a LINQ-2-SQL multi join. LINQ-2-SQL
takes a high level ORM description and generates SQL code from it. The
generated code was slow and cost a 10x slowdown in production._

------
ck2
Wait, it took them how many years to think of doing a sql query analysis?

~~~
gimpf
Until it was a problem worth noticing. Opportunity costs.

~~~
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.

------
nikcub
This thread and the tips are great, but that page should be cached anyway

Awesome that SE have come so far generating every pageview

------
justin_vanw
They looked at SQL and added an index to their DB?

When I read this I actually cringed, because they changed their SQL query in
production without looking at the query plan first. Apparently it's the blind
leading the blind over at stack overflow, not that it seems to be hurting
them.

~~~
pbz
"In production this query was taking too long, so the next step was to look at
the query plan, which showed a table scan was being used instead of an index.
A new index was created which cut the page load time by a factor of 10."

I read it as: 1) noticed that's slow in production, 2) look at query plan
(prod or dev, not clear) 3) add index (not clear if they did that in dev first
and then prod)

~~~
sams99
my dev box runs a clone of production, I do all my tuning on dev then deploy
to staging, confirm it is still good there and finally ship it to production

------
innes
Imagine how great StackOverflow would be if all the experts critiquing it here
had built it.

