|
[Date Prev] [Date Next] [Thread Prev] [Thread Next] [Date Index] [Thread Index]
|
Re: [PhillyOnRails] Rails and Database Design Issues
|
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
|
|