John Ashmead on 16 Dec 2005 15:03:37 -0000

[Date Prev] [Date Next] [Thread Prev] [Thread Next] [Date Index] [Thread Index]

Re: [PhillyOnRails] Rails and Database Design Issues

Dear Tody,

I like your responses.  I whole-heartedly agree.

The key is to use the right tool for the right job at the right time.

Stored procedures make sense for large, industrial strength applications facing significant regulatory & other pressures. Ruby on Rails makes sense -- to me at least -- for getting something useful & nice up quickly.

The mapping issue, when you are using RoR as a frontend to an existing SQL backend is going to create problems. I don't think I've ever seen a case where a new GUI face was transplanted to an existing relational database body where intervention wasn't required to avoid rejection of the graft, at a minimum some additional supporting fields & often a table or two, even a small "helper" database to manage translation -- and suppress the inevitable immune response.

John (Metaphor Guy) Ashmead

On Dec 16, 2005, at 9:15 AM, Toby DiPasquale wrote:

On Fri, Dec 16, 2005 at 08:05:30AM -0500, John Ashmead wrote:
1) Security.  In the case where medical or financial data is kept,
all access to the data -- including reads as well as updates -- has
to be regulated or Mr. CEO goes to jail (Sarbanes-Oxley).  The only
way to do this easily is to consolidate all access in one place,
which you can then watch.  This means stored procedures, which can
not only do the data access, but log every access.  Because stored
procedures live inside the database  they are very easy to monitor.
You can run selects against the tables that store the stored
procedure code to see all stored procedures that access a specific
table or field.  This is a big win when verifying access rules are
being adhered to.  Also, stored procedures let you control
permissions in a very detailed way:  a bank clerk can be given access
to the "transfer_balance" stored procedure but not allowed access to
the underlying account tables.  And his/her transfer can be (& always
is) logged by the procedure itself.

I hadn't realized that they were used in this way. Very interesting.

2) Maintainability.  If all access to the data is consolidated, then
it is easier to change fields & tables, see where performance hits
are coming from, track data bugs, and so on.  I've had major
maintenance problems locating rogue SQL (there is such a thing!)
which was hiding in frontend code.  You haven't lived until you've
had to break a table into two or more tables and find every single
reference in the frontend code & fix each.  Worst case:  the SQL is
built in the front end on the fly.  References that don't exist until
execution time are especially hard to find/fix! :)

This is what Rails does, so you may have some problems in that same area
using Rails. Rails keeps it really simple w/r/t SQL generation, but if
you're trying to map Rails onto an older schema with pre-existing SQL
already in place you may run into some problems.

3) Speed.  The stored procedure is compiled once, then run many
times.  Individual SQL's have their query plans compiled every time
they are run, which can be a major performance hit.  Also, with
stored procedures the inputs travel over the wire to the database and
the outputs travel back, but all the rest of the work -- which can be
thousands of lines long -- happens on the server with no wire
charges.  Wire charge reduction can be a second significant
performance win.  Note large scale data accesses will almost always
be done faster by the database -- which was designed and built to do
them quickly -- than by anything else.  Small scale data access
happen so quickly nowadays they don't count.   One good model, when
applicable, is to have the stored procedure doing most of the grunt
work collecting data, throwing out and/or summarizing millions of
rows, then pass back tens or at most hundreds of rows for further
work on the frontend, letting the user apply graphic tools to them or
the like.

Yeah, I was aware of this purpose behind stored procedures and this is
what's most frequently cited in their defense. However, I've seen a very
large number of poorly designed and inflexible Web applications that lived
and died by the stored procedure (T-SQL) and could not be updated or
extended when necessary later without great cost.

In re speed, I'm not clear what is meant by "other levels of

Basically just "things that aren't stored procedures", such as eliminating
unnecessary queries, caching, etc. The things you would do if you didn't
have stored procedures in your RDBMS. (*cough* MySQL pre-5 *cough*)

Most of the performance problems I've seen have been
corrected by 1) fixing the join condition 2) adding indexes 3) fixing
the table design 4) switching to specialized data loading tools 5)
reducing network traffic.  Frontend tools have usually not created
performance problems, at least not in my experience.  This is because
the amount of data a single user can type in is minimal:  the worst
they can do is kick off some query that queries the entire database &
that problem can be checked for and trapped.

I would agree with this, but in my experience, there are a fair number
(maybe a lot) of guys who will _start out_ with stored procedures from the
get-go and I think that's a mistake. And, one that's much harder to
correct, I think, than some of the mistakes you have mentioned.

I have nothing against doing all database access from inside a single
tool, such as Ruby, but I think -- as I observed in an earlier email
-- that as an application gets larger, longer lived, more complex -- it can make sense to break parts of it out into tools specialized for
the specific tasks, i.e. database, data analysis, report generation,
and so on.

Absolutely. I concur wholeheartedly. All I was trying to point out to the
original posted was that perhaps he didn't want to try and shoehorn Rails
onto the database piece if it didn't really fit. Rails isn't good for
calling a bunch of stored procedures and probably won't be for quite some
time, if ever. Stored procedures and Rails are really at different ends of
the spectrum. Much to your point, stored procedures are for the later
stages of an app's lifecycle: once its been built and tested and proven,
you find out where it needs tuning in the database arena and you introduce
some stored procedures in places that make sense. Rails, on the other
hand, is for the early stage of the game: building and testing. Sure you
can maintain an app in Rails for a while, but my contention is that you
will eventually outgrow if your application becomes big/popular enough.

Basically, John, we agree, but we may not talking about the same things ;-)


John (person working with RDBMSs for long periods of time) Ashmead

Sure, and I knew you would be the one to respond. I know you have far more
experience with RDBMSs than I.

Toby DiPasquale
talk mailing list

John Ashmead Ashmead Software & Consulting, Inc. 139 Montrose Avenue Rosemont, PA 19010-1508 (610) 527 9560 fax (610) 527 4640

_______________________________________________ talk mailing list