For most of my career I have developed software that talked to and/or ran machines. Now I find myself doing more and more database work.
While I understand the basics, I want to learn best practices. As one “for instance”: when is it better to use a view or stored procedure?
I know *how *to use a view or stored procedure, but I want to learn more about when to use them.
Please recommend some good books.
I applaud your desires. (Probably because they mirror mine.) I’ve been highly disappointed not to find satisfactory answers to most of my “which is best” questions. It turns out that most of the things I ask seem to have “it depends” answers, or competing “best” solutions depending on who you ask.
On the topic of stored procedures, my personal preference (at present; I reserve the right to change my mind) is to write my own queries in DAOs and avoid store procedures. Much of this is due to the environment I work in where the database engine is liable to be changed on me, or I have to switch back and forth between a few. Stored procedures don’t shift well, but raw SQL queries work fine so I tend to stay with the lowest common denominator. That likely comes with some performance cost.
I also have been resistant to the useof ORMs. I dislike “magic boxes” and prefer to fully know/understand what is going on. That said I’ve been using Hibernate on a recent project and I have enjoyed (most) of it.
A lot of that will depend on the particular application and database system. Sorry, I don’t have a book to recommend.
A view is a set of data created at run-time based on a query. The query is usually pre-compiled and optimized. A stored procedure is a function with capabilities that vary widely across database systems. When analogous to a view, a stored procedure would be used to perform processing that wouldn’t be available or difficult to accomplish using a standard query. It would also be used where the result wouldn’t be a table of information.
There should be on-line resources where these topics are discussed in detail. The database provider will usually provide information specific to optimal performance for their product.
In a lot of cases, these really are the right answers.