|
[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
optimization".
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 ;-)
HTH,
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
talk@phillyonrails.org
http://lists.phillyonrails.org/mailman/listinfo/talk
John Ashmead
Ashmead Software & Consulting, Inc.
139 Montrose Avenue
Rosemont, PA 19010-1508
(610) 527 9560 fax (610) 527 4640
john.ashmead@ashmeadsoftware.com
http://www.ashmeadsoftware.com/
_______________________________________________
talk mailing list
talk@phillyonrails.org
http://lists.phillyonrails.org/mailman/listinfo/talk
|
|