Is The Database A Poor Step-Child?
Posted by Mike Brunt at 9:57 AM
21 comments - Categories:
Web Servers | .NET | DataBase | CloudComputing | ColdFusion | JRun-J2EE
Databases are so important to us, in many ways, they are the number one dependency we deal with in ColdFusion and yet, in many ways, we treat them as ignored step-children and I fear the advent of Hibernate ORM in CF 9 will make matters even worse. If client variables are in use, this becomes even more critical, tuning the database will always certainly yield more benefits than any amount of JVM tuning. By tuning I mean looking at indexing, affinities, CPU allocation, hard-drive layout, etc. All of theses things can yield immediate benefits or cause immediate pain if they are not right.
Typically, when we get new clients we try to persuade them to spend some time on our database review and report offering but most of the time they decline, wanting to focus all time/spend on the CF-web server, eventually we are typically able to persuade them to let us look at the database. They would genuinely benefit greatly if we started out at the database. Here are the main things we look at and report on...
- Table Indexing
- Back-up Redundancy Schema
- Security Settings - Ports etc
- Security Settings - User Accounts
- Use of Views
- Use of Stored Procedures
- CPU Affinity
- CPU Allocated to Database
- Service Pack Level
- Data File Location
- Log Files Location
- Compatibility Level (SQL Server Specific)
- Heavy/bad processes such as Auto_Shrink
- Data Type usage (looking for the most efficient data types)
- Unnecessary Services
- Long Running SQL
Bucky Schwarz wrote on 11/07/09 11:39 AM
I agree completely. Hell, even before you get to the tuning of the actual database, how much better would so many apps run if better SQL was written. It seems like instead of adopting the mindset that a good CF developer should write good CF as well as good SQL, we instead go in the opposite direction and say "don't worry about the SQL, ColdFusion 9 will handle that for you." I think the result is going to be the database becoming a sort of 'black box' where magic just kind of happens for you. What happens when the magic doesn't work? You end up digging around in the database and figuring how that works, THEN figuring out how to make the database work with Hibernate. What happens when you have to start writing complex queries with HQL? At that point, is it even worth it to use Hibernate?But back to your original point, I think the database is absolutely the red-headed stepchild, and I'm not really sure why. The whole point of web apps is managing data and presenting it in a useful and meaningful way. It's kind of what web developers do: manage data and give it to the layman in a way that makes sense, so he doesn't have to go digging around in a database. A lot of people just hate dealing with databases, which should make you happy since I guess it gives you more business opportunities ;)