Refactoring SQL Applications 159
stoolpigeon writes "My past as a DBA probably makes me a bit biased, but the reaction I've seen the most when a database application isn't performing as well as would be liked seems to focus on the database side of things. The search for a solution usually seems to center around tuning db parameters, the building (or removal) of indexes and, if the budget allows, throwing hardware at the problem. In their new work, Refactoring SQL Applications, Faroult and L'Hermite bring a much wider range of options to the table. There is a lot in this little book for the developer charged with fixing an existing application and I think a lot of good information that could save one from making a number of headache-inducing mistakes on a new application." Keep reading for the rest of JR's review.
The book is divided into eight chapters; the first two deal with how to approach a problematic application in general. In the preface the authors say, "This book tries to take a realistic and honest view of the improvement of applications with a strong SQL component, and to define a rational framework for tactical maneuvers." I found this to be true throughout the entire book and was impressed by how well the examples, suggestions and problems echoed my real-life experience. This book is first and foremost practical. There is really almost nothing in the book that does not come immediately to bear upon the problem at hand. I've seen others do a lot less with many more pages. | Refactoring SQL Applications | |
| author | Stephane Faroult with Pascal L'Hermite |
| pages | 293 |
| publisher | O'Reilly Media, Inc. |
| rating | 9/10 |
| reviewer | JR Peck |
| ISBN | 978-0-596-51497-6 |
| summary | Good for a developer charged with fixing an existing application. |
The examples and benchmarks are compared across three different popular relational database management systems. They are MySQL, Oracle RDBMS and Microsoft SQL Server. I thought that this brought up a couple interesting issues that are not directly addressed in the book. First is that the authors are talking about how to improve performance, not comparing platforms, but the numbers are there and may be of some interest to people who would like to compare them. Secondly, I've met a number of people over the years who get quite animated about insisting that a good DBA does not need to know any certain solution, but rather just the fundamentals. I think Faroult and L'Hermite put this idea to rest, though unintentionally. In order to discuss how to best understand what exactly is happening and how best remedy issues, they show that it is necessary to have an understanding of platform specific issues and tools. This is true on two levels. The first is that the location of use of the built in tools for each platform are different. The second is that what works for one platform does not necessarily work for another.
For example, Chapter Two "Sanity Checks" contains a section on parsing and bind variables. The authors compare performance when queries are hard coded, with new prepared statements on each iteration (firm coded) and with one prepared statement and changing the parameter value on each iteration in a loop (soft coded). On Oracle and SQL Server the performance was poorest with hard coded, better with firm coded and best with soft coded. MySQL did best with soft coded as well but actually took a performance hit moving from hard coded to firm coded. This had to do with differences in how MySQL server caches statements. The authors took the time to rewrite their code from java to C in order to ensure that the issue was not related to language or driver issues. This is not to say that one can ignore RDBMS and SQL fundamentals, but rather that to get top performance requires knowledge of platform specific issues. This also comes out again when dealing with optimizers.
With that in mind, the authors recommend that readers have a solid understanding of SQL and some programming language. Most examples are SQL and code is given in Java and PHP. There are also examples that illustrate SQL extensions showing procedures, functions, etc. written for all three RDBMS products covered. The authors stick primarily to standard SQL but do make note and at times show examples of how things will look in each of the other databases. This information is current and reflects the most recent versions of the each product.
The fourth chapter, "Testing Framework" is incredibly useful. The authors cover generating test data and then checking correctness of outcomes through comparison. This is really useful information for anyone working to improve an application, or writing one for the first time. I think it also a large part of why this book could really appeal to new and experienced developers as well as the developer working on existing or brand new applications. I think there is a good chance that only the most extremely experienced developer would find nothing new here, or at least some new way to approach a problem. New developers can learn quite a bit and avoid some bad habits and assumptions without having to gain that information the hard way. And then the tools for generating random data, large amounts of data and comparing results will provide excellent opportunities for learning and real world application.
The next three chapters cover dealing with specific types of issues and how to improve performance. The last chapter then quickly describes a scenario of just how the authors step into real world situations and start to attack a problem. This is followed with two appendices. The first is scripts and samples, the second tools that are available to help in finding issues and resolving them. Some of the authors tools use SQLite, which is discussed briefly in the chapter on creating test data as some of the tools depend upon it.
I think that it has been a while since I've read a book that could have such a rapid return on investment. There are many suggestions and insights that should enable anyone to squeeze better performance out of just about any database application. While the focus is on the application side, there is plenty that requires understanding and work on the database side as well. There is discussion of the parameters and hardware I mentioned at the start of this review. But rather than the only options, they are one part in a much larger and systematic approach.
The authors relate that often refactoring for this type of application comes into play when something that used to work does not work any more. This can often lead to an environment of high pressure and emotion. The desire for a rapid resolution can lead to casting about in the dark for a quick fix or a feeling that cost is no longer as significant since a fix must be had now. The authors argue, and I agree, that this is exactly when a rational, disciplined process of tracking down and fixing issues is the most valuable. I agree. The issue is of course that someone in a position to do something must have the ability to take that approach. This book will get one well on the way to being in that place. Of course it can't take a brand new developer or DBA an expert. Much like a degree it can give them some fundamental tools that will allow them to take full advantage of experience as it comes rather than just crashing and burning.
If I could I'd have any developer on a database centric application read this, and DBAs as well. There is a lot here for both sides to learn about just how much they depend upon and impact one another. This may be an idealistic dream, especially for larger shops where often the relationship between those two groups is adversarial, but I think that such an approach could only make life much better for everyone involved. For anyone looking to enter this world on either side of the DBA or developer equation, this may make a nice addition to their education. For that individual wearing both hats this could be a life saver. In this small book they will learn many things to look out for as well as gain exposure to some of the similarities and differences in what are arguably the top three relational database management systems right now.
You can purchase Refactoring SQL Applications from amazon.com. Slashdot welcomes readers' book reviews -- to see your own review here, read the book review guidelines, then visit the submission page.
Should have included PostgreSQL and DB2 (Score:5, Insightful)
This study would have carried more weight if it had included PostgreSQL and IBM's DB2. These two databases do more serious work than MySQL although many believe MySQL is more widely deployed.
Problem is not the SQL writers..... (Score:5, Insightful)
But with management.
when I spent a few years as a DBA it was common to be told to not work on that project any more as soon as it produced usable data. That means as soon as you have a working prototype you are required to drop it and start the next project. Many times after you get a working prototype you then go back and refine it so that it's faster and uses less resources.
Management is the blame. Unrealistic deadlines for DBA's and if you are honest with them and give a report that you have data they think it's good to go. I actually got wrote up once for taking one of the old procedures we had and rewriting it so that it worked much faster and the resource hog it was was reduced to the point that others could use the DB while it ran. I was told I was wasting time.
Performance Tuning is Not Refactoring (Score:5, Insightful)
I have the misfortune of working with a database that is primarily a couple of tables with key-value pairs (not a traditional database model).
There is only one column that can be indexed, and it has to be done with a full text index.
Every once in a while, there is a discussion about moving this mess to something more traditional. I was excited to read the review on this book, but as I read through the review, it seemed like this was more of a "performance tuning guide".
Re-factoring a database is a lot more involved - changing tables, stored procedures, maybe even the underlying database.
The term Database Application is fuzzy and poorly defined. Is it the front end? The stored procedures? The database tables? I would consider a database application to be any part of the code that stores, retrieves, removes or modifies data stored in a database, and the entities that have been defined to store that data.
Using that definition, this book is about tuning, not refactoring.
Server performance is important, but... (Score:4, Insightful)
I've found that the biggest issues with SQL applications (writing rich clients) is not in performance turning of server/sql but in dealing with ORM issues, where to draw the line between how much work the client does vs. how much the server does, reconciling changes made in memory with data in tables, concurrency, database architecture designed to cope in advance with poorly thought-out requirements you're given, etc. I'd hope that book on refactoring SQL *applications* would touch on these issues.
Use views (Score:3, Insightful)
I think it's usually best to have views (whether with rows that are the result of code, or with a pure 'select' definition, or materialized ones) define what your application 'sees', so that you can always change the underlying datastructure. That way refactoring becomes a bit more easy.
Re:Should have included PostgreSQL and DB2 (Score:4, Insightful)
Exactly, MySQL is nothing but a toy database.
This is the problem with most slashdotters. Most of them put up unsupported comments. What I would like you to do is to support your claims by pointing us to websites that have made the "mistake" of first running MySQL and later discovering the "light" in adopting PostgreSQL or otherwise.
Alternatively, you could websites that use MySQL; which websites can be branded as "toy websites" by extension.
Re:Should have included PostgreSQL and DB2 (Score:4, Insightful)
As an aside, some of the toy websites that use mySQL include Flickr, Facebook, Wikipedia, Google, Nokia and YouTube.
Re:Should have included PostgreSQL and DB2 (Score:4, Insightful)
You forgot the other often repeated/never researched traditional slashdot claims seen in every mysql comment section, such as mysql doesn't do transactions and doesn't do replication, both of which are necessary for each and every database install ever done past present or future... (Of course it has those features for about half a decade, maybe more, certainly since around the 4.0 range, but that never slows down the folks that repeat those claims)
Then there are the re-occurring claims that mysql is useless because it doesn't have some bizarre feature that you might personally think is useful, therefore any database without it is useless for everyone doing anything, like perhaps direct file importation of COBOL ISAM punch cards, or an internal database representation for complex four dimensional vectors. You know, the stuff everyone uses.
Then there are the posts explaining how a failing hard drive on an old gateway 2000 vaporized the filesystem and/or bad ram caused endless kernel lockups, and the mysql software was running on that bad hardware, and correlation always implies causation, so mysql must be bad too.
Finally I expect several posts about how they found an obscure bug in the beta 3.23 version back around eight years therefore they'll never use it again because that is the only software that has ever had a bug.
Re:Shoot the developers (Score:2, Insightful)
How do you debug, log, source control, deploy?
I found triggers to be a pain in the ass on the problems above
(Unless you just have one big customer and nothing else)
Re:Problem is not the SQL writers..... (Score:4, Insightful)
Re:compare SQL to Code (Score:3, Insightful)
That's possibly a VERY bad idea. Even with small queries it's possible to create huge intermediate result tables and loading all that data into your application will make it crash. And if that doesn't happen, breaking a complex SQL statement into separate parts robs the SQL query optimizer of useful information. Your code limits the choices for an optimum evaluation plan, but how close is your code to the optimum plan that can be achieved?
Having said that, the optimizers can't work magic. I sometimes split up SQL statements to force a certain strategy, when the result of the optimized plan is garbage.
Re:Should have included PostgreSQL and DB2 (Score:5, Insightful)
It isn't quite that simple, but I suppose one of my earlier (and later abandoned) projects qualifies.
I set up HERMES (a CRM suite written in PHP4) originally on MySQL and eventually discovered that the lack of transactions, etc. were a serious problem (this was back in 1999). I tried to move it over to PostgreSQL and discovered that PostgreSQL was really hard to administer (this was back in 1999). I ended up doing all my prototyping on MySQL, then converting the schemas to PostgreSQL using mysql2pgsql.pl because this was the only way I could get the data protections I needed (back in 1999).
Now, both MySQL and PostgreSQL have come a long way in the nearly-a-decade since then. MySQL has added transactions (for some table types not installed by default), foreign keys (for some table types not installed by default), strict mode (which can be circumvented on the app level), and a the planner has gotten much better. On the other hand, nearly every one of my issues with PostgreSQL has been resolved too. 8.3 has some really impressive new features from a developer perspective, and 8.4 will have even more. I haven't had to do prototyping on MySQL since PostgreSQL 7.3 came out.
I still stand by the statement that "compared to PostgreSQL, MySQL is a toy," and I would expect the gap between them to continue to widen. However what was limited to light content management db's in 1999 (MySQL), has become better able to handle a wider range of single-app dbs. MySQL is still no reasonable choice for an enterprise-wide database management solution especially where critical data is involved, but there are an increasing number of special cases where it is an option, in particular when compared to Firebird's embedded version, SQLite, and stuff like Sybase's SQL Anywhere. Comparing MySQL to MS SQL though only comes out favorably for MySQL where MS SQL is quite a bit more than is needed. PostgreSQL OTOH can in most cases compare favorably to Oracle, DB2, and MS SQL.
So the other half of the statement needs to be "but there are some cool things you can do with a toy db...."
Re:Should have included PostgreSQL and DB2 (Score:5, Insightful)
What I would like you to do is to support your claims by pointing us to websites that have made the "mistake" of first running MySQL and later discovering the "light" in adopting PostgreSQL or otherwise.
It's a toy database because when things aren't set up properly, they don't fail. Instead, they succeed silently and corrupt data (see using the wrong file format for your tablespace). Also, the developers are a treat - "we don't need transactions, do integrity checks in the app", followed by "we now have transactions, aren't we cool". Do they have triggers yet? Meanwhile, I have postgres, which works just fine.
Re:Problem is not the SQL writers..... (Score:1, Insightful)
You were wasting time. You only have so many hours in your life; why spend them working for those losers?
Confusing volume with data integrity (Score:5, Insightful)
The typical argument goes something like: 'MySQL suxorz - nobody uses it for serios work' followed by: 'Yeah? well explain that to =HIGH VOLUME SITE=!'
Such responses show a misunderstanding of what serious work is being discussed.
MySQL does a fabulous job with simple, high-volume transactions, exactly the type seen by Yahoogle/Flicker/Blogsites. They need to sore simple data (EG text) and be able to retrieve it quickly, and for these uses, MySQL is probably a better bet than Postgres or DB2.
But 'serios work' means thing like strong, ACID compliant transactions, row-level locking, strong integrity of field types, and a query scheduler that holds its own when you combine inner, outer, nested, subqueries mashing together a dozen or more tables with millions/billions of records/combinations.
Postgres will do this, MySQL won't. MySQL isn't bad because of this, it's just a tool not well suited to this specific job. I use MySQL for website CMS, I use Postgres for financial applications.
Does your dishwasher suck because it does a piss-poor job cleaning your socks? Use the right tool for the job.
Re:Confusing volume with data integrity (Score:3, Insightful)
PS: Your company is pissing away tens of thousands of dollars on Oracle, when you could use PostgreSQL for free!
And no, I haven't read your requirements, but I'd be intrigued to find out what needs Oracle answers that PostgreSQL can't!