10 ms·
Stack Overflow Makes Slow Pages 100x Faster By Simple SQL Tuning
- JoeAltmaier 15y agoThere 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 15y agoArguably, 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.
- ck2 15y agoWait, it took them how many years to think of doing a sql query analysis?
- gimpf 15y agoUntil it was a problem worth noticing. Opportunity costs.
- ck2 15y agoHow 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 15y agoI 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 15y agoMSSQL 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 15y agoThe 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.
- brown9-2 15y agoThis headline is incredibly misleading. The underlying/original blog post is all about tuning the queries behind a single page when the developer noticed some latency while looking at logs.
- swanson 15y agoThe linked article from Sam's blog: http://samsaffron.com/archive/2011/05/02/A+day+in+the+life+of+a+slow+page+at+Stack+Overflow# http://samsaffron.com/archive/2011/05/02/A+day+in+the+life+o... 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 15y agoThanks. I always find this articles more entertaining/educational.
- jarin 15y agoI 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 15y agoI 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 15y agoIf 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 15y agoThis 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.
- jasonkester 15y agoRemember 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.
- billybob 15y agoI haven't used LINQ, but I do use Rails and its ORM. Using Rails' ORM to generate SQL is a case of optimizing for development time rather than execution time; assuming I would write faster SQL by hand, which is questionable, it would still be premature optimization and would be harder to maintain. My take would be: use a great ORM to develop quickly and maintain easily, but be aware of common pitfalls, set up sensible indices, and have some performance monitoring in place. Tune by hand if absolutely necessary.
- MichaelGG 15y agoThey chose to write their query in C# instead of SQL. They could have kept LINQ-to-SQL and manually written the query (even made it a SP, for the slight gain that'd provide) and then used LINQ's mapping features to get objects out of it. (I believe the DataContext has a Translate feature that'll take any data reader and pop out objects.) I do this from time to time when the LINQ-to-SQL query has a bug or isn't doing exactly what I want, or when there's a bulk operation I can do much more efficiently. It's not exactly advanced LINQ-to-SQL...
- brunomlopes 15y agoActually, if you look at other posts from Sam mentioning dapper you'll find that they profiled the site and found some bottlenecks also on the translator from the datareader to objects on linq2sql. Since they're going to hand-write the sql then they're going to take advantage of that really fast microorm instead of sticking with l2q.
- smackfu 15y agoThis 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 15y agoSQL 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 15y agoIn 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 15y agoRealistically, 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 15y agoSerious 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.
- ltbarcly3 15y agoThey 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 15y ago"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 15y agomy 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
- n_are_q 15y agoIf 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 15y agoI 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.
- 15y ago
- jorisw 15y agoThey 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.
- baddox 15y agoThe 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 15y ago.NET programmer probably isn't aware SQLite exists.
- jrockway 15y agoThat's hard to justify these days, however. They even use SQLite on the Airbus A350 flight software!
- alnayyir 15y agoHow many .NET programmers work on Airbus A350 flight software?!
- darklajid 15y agoDon't want to ruin this bad joke here, but there's even a port of SQLite to to .Net: https://code.google.com/p/managed-sqlite/ https://code.google.com/p/managed-sqlite/
- biot 15y agoI read it as a clever portmanteau of NoSQL and SQLite. As SQLite is to full-blown SQL systems, NoSQLite would be to full-blown NoSQL systems.
- adw 15y agoBerkeley DB? (Or Tokyo Cabinet...)
- gary4gar 15y ago"__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.
- innes 15y agoImagine how great StackOverflow would be if all the experts critiquing it here had built it.
- deleted 15y ago[deleted]
- bobx11 15y agoThink 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 15y agoYes, but StackOverflow does have a DBA, and very good one at that: http://www.brentozar.com/ http://www.brentozar.com/ I guess he doesn't deal w/ this level of optimization, or it never hit his radar.
- jswinghammer 15y agoSometimes table scans happen even when you think you know what you're doing. If someone adds a column and doesn't think anyone will be filtered on that column then it can easily happen. I usually ask people: - how big is this table getting? - how are you querying on it? That usually fixes most problems before they happen. Not sure it's my job to specify a join type though. A nested loop join isn't a bad thing or even a sign of a bad thing happening necessarily.
- stcredzero 15y agoThe 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.
- chopsueyar 15y agoImagine 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.
- richardw 15y agoI 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-using-sql-s-missing-index-dmvs.aspx http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-usi... 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.
- nikcub 15y agoThis thread and the tips are great, but that page should be cached anyway Awesome that SE have come so far generating every pageview