SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Database Queries and Scalability

By Steve Jones,

I ran across this post with a very provocative title:  (thanks Brent Ozar Unlimited). It's an interesting read from a developer, talking about how so many website designs aren't built for scalable because they rely too heavily on a set of database queries place on every page. If you expect a database query to retrieve or store a user id, or other data on every page, then you are limited to the scalability of your database back end. There's no doubt there, but the author has ideas that can help you overcome these issues.

One of the problems is that systems read and write account information (or registrations) to the database directly. That's definitely a scale issue, and the author suggests generating the client information on the web server and storing it in a more robust and scalable backend. That makes sense, but it's with the caveat that " As long as you eventually write the data to the db later on, you’re good to go." Good point, but now you need a replication process of some sort (or messaging) that gets the data to your database. This ignores some of the synchronization issues across multiple servers as well, but it's not a bad idea. 

The idea of avoiding joins, by using a cached set of data to iterate through and pull data from other tables may work in some cases, but I'm not sure this is the most efficient way of grabbing data from a database, ensuring it's the latest data, and not impacting concurrency. This technique seems to invalidate the power of an RDBMS by not using joins to effectively, and efficiently, retrieve data. I suspect replicated copies of slowly changing data is a better way of scaling your system than avoiding joins.

Overall I don't love the article, and depending on which platforms and technologies you're using, it might be better, or worse, advice. I would say that building better systems involves considering alternative techniques, allowing for failures, and using new techniques like messaging, caching, asynchronous processes and more. As we try to improve the quality of our software, we should be talking about, writing about, and trying out new techniques, learning what can work better in our environments.

Total article views: 263 | Views in the last 30 days: 1
Related Articles

Performance and Scalability Testing

Performance and Scalability Testing environment



The following is a question I received over the weekend.   “If you had to grow your database capab...


Database Adminstration Scalability

database administration scalabilityI am working on a project that is very successful in terms of its...


A Technique for Determining the I/O Hog in your Database

Performance Tuning can be as much an art as a science when working with SQL Server, but there are ma...


scalable hardware solution for 10TB now to 100TB in 3 years

scalable storage and query performance