I’ve really been enjoying the database war stories from O’Reilly Radar these past few days. The discussion about getting Web 2.0 application architecture scaled up to meet rapidly growing demands has held my interest since I have been scrambling for the past few weeks to increase the query performance of Advertizer, the text advertisement aggregator/mobilizer that powers Skweezer ads. In summary, we were able to take a full-text SQL query that used to take nearly 10 seconds and drop it down to mere milliseconds. This takes Advertizer from the realm of proof-of-concept up to serious enterprise-ready money machine. I am thrilled and relieved now that it’s done and live.
Before it had a name, Advertizer was simply the mechanism that Skweezer itself used to gather text ads from multiple advertising partners in real-time, pick the best ones and put them on the bottom of the page. Eventually it became a stand-alone system general enough for other companies to use too. After all, mobile text ads = the next new hotness. As we grew, it became clear that real-time ad aggregation was too dang slow because in order to get the best ad from several different publishers, you need to wait for the slowest one. Advertizer then changed to a data-cached system where it would store the ads to show in a temporary database.
In order to match ads based on context and keywords, (okay, don’t laugh) I thought that using SQL Server’s full-text indexing was the way to go. It turns out I was wrong about that. Some of those queries were taking over 10 seconds to return, which is far too long to wait for ads. Also, having one single database is a performance brick wall. After hiring a DBA consultant ($$$ but worth it), that same multi-second query now returns fast enough to support 1.6 million queries per day, per server. Our projected hardware costs just plummeted, and that’s huge. Read more »