Application Development from a Database-Freak’s Point of View
A colleague of mine recently posted an excellent article about the general duties of an application developer: http://scn.sap.com/community/abap/blog/2014/02/18/some-recommended-points-everybody-should-remember-when-developing-abap
One of Florian’s points dealt with performance-testing. I’d like to expand a bit on this point, from the perspective of an impassioned database developer.
We’ve all been there.
Most of us are there everyday. Development System with a few rows of application data. Our task is to write an application to work with this data. It also needs to work in a production environment someday.
If you’re an ABAP developer, performance problems ought to show their ugly faces when you move your application to the QAS system, provided the system has a relatively recent copy from the production system.
In other development environments, the same sort of concepts should apply.
Often times, however, problems don’t show themselves till years down the road. Ineffecient code just keeps on running and running, until one day it just stops.
What can we do about it?
Well, the USUAL starting point is to blame the guy who wrote the application, but doesn’t work here anymore. Or, worst case scenario: he does still work here and you work for him. 😯
As professional developers, though, we need to take it upon ourselves to neither blame nor be the one to blame.
(Now don’t get me wrong, all of us have written some pretty bad code, the kind of which we could kick our own butt for. And kicking yourself is a good sign. It means you’re better than you were before.)
A USEFUL starting point would be to understand what the code is doing to the database.
Be good to your database and your database will be good to you.
The modern approach to application development sees the database as just a place to persist your data for future use in the application. ORM (Object-relational mapping) frameworks are commonplace in practically every development platform. With code-first, a developer is able to design data objects in a familiar environment, without having to worry about how to create the database objects. Even with database-first design (database already exists), a developer doesn’t need to concern himself with querying/updating application data using native SQL.
This isn’t something I’m going to call good or bad. There are definite advantages to using ORM Frameworks:
Most are well designed and when properly used, they treat a database better than most developers.
Most are cross-DBMS compatible. The application can run on MSSQL, Oracle or many other Database Management Systems.
The flip-side to that coin, however, is ORM Frameworks make it too easy to not think about how an application’s data are being retrieved and persisted. It’s so easy to call a function that gets some data. If you need the data again somewhere else, call the function again. Just don’t be surprised when your application starts doing funny things a few years and a few hundred thousand rows of data later.
Being good to your Database.
Best Practices can be found all over the Internet:
For every development platform.
For every DBMS.
For every ORM Framework.
Here, I’d like to share some tips I’ve collected over the years:
- Learn and understand at least Basic SQL. Enough that you can write the queries you need without frameworks.
- Familiarize yourself with the above-mentioned best practices, suited to your environment.
- Trace your application. What is being queried, when, where and why? Remove redundancies and Overkill. If need be refactor your code.
- While planning a database or a piece of a database for your application, consider normalization. Don’t overdo it, but remember redundant data comes at a huge price. It may be easier to query, but it costs a lot more to maintain.
- Plan your database objects defensively. Know what to expect over the lifetime of your DB objects. How much data is probably going to be stored(Allocation)? How will a table most likely be queried “WHERE CLAUSES”, “JOINS” (secondary indexes, key fields)?
In general, know what’s going on behind the scenes of your application, including the database.
That’s all for now.
This article touched on a few points in general. I plan to expand on many of these points in later installments.
Being that I’m in the middle of SAP TAW12 and supposed to be studying for my certification-exam, though, it may take a couple of weeks. 😀
P.S. Does anybody know how to just turn off the spellchecker here?