Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

Re: [Semi-OT] Views, Stored Procedures, and Class::DBI

by mpeppler (Vicar)
on Oct 18, 2005 at 19:20 UTC ( [id://501094]=note: print w/replies, xml ) Need Help??


in reply to [Semi-OT] Views, Stored Procedures, and Class::DBI

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

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://501094]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others chanting in the Monastery: (6)
As of 2024-03-29 09:50 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found