There's more than one way to do things | |
PerlMonks |
Re: [Semi-OT] Views, Stored Procedures, and Class::DBIby mpeppler (Vicar) |
on Oct 18, 2005 at 19:20 UTC ( [id://501094]=note: print w/replies, xml ) | Need Help?? |
My experience is with Sybase, and that may color what follows... Stored procs are great, and can be used to achieve a number of objectives. The first is speed - the proc is pre-compiled and pre-optimized, and the optimization of a complex multi-table join can take a significant amount of time. The second is access control - you give access to the proc, not to the underlying table, and so you control what DML a client app can execute. The third is modularity - you can hide some DDL changes within the proc architecture without having to affect the client apps (this is of course not a complete solution, but for some things it can work pretty well.) I haven't used views as much, but they can achieve similar results. I would advise against having dynamic SQL all over the place in your client code - this makes maintaining the database and the dependancies in the client code a nightmare. In addition the dynamic SQL is really quite difficult to optimize when you hit a bug in the DB where an optimization hint might be needed (such as index hints, abstract query plans, etc.). My experience here has been as a DBA for a team that develops with WebObjects, and the results are sometimes quite ugly. This is of course the view from the DBA, not so much the SQL/app developer, but may still be of use to you. Michael
In Section
Meditations
|
|