If the option is available, is it preferable consolidate as much data as possible into a database view instead of doing this through view view objects? It seems the answer would be yes, but I would like to hear the pros and cons related to performance, etc.
While I do not mind a detailed discussion, practical "rule-of-thumb" advice is what I am after; I am a newbie that needs general guidelines - not theories.
Well, it depends
- From sql query performance point of view this probably doesn't matter.
- From maintenance point of view, maybe is faster to replace db view than to redeploy app(but applications can be versioned, so you can have old and new application running at same time and with db view you can't do that - except if you use "Edition Based Redefinition" feature available from oracle db 11gR2+)
- But, if you need to do DML operations on these db views there is big difference(for some db views you will probably need to write "instead of" triggers, figure out how to do record locking, etc.)
Thank you for the information. Performance is the main driver behind the question because I am wondering if it is faster to send a single large record set across a network or several small ones and "assemble" them at the client level. The fastest is what I am after. You're last point is important to me because the view does need to be updatable. As far as creating an updatable database view, I know there are minor tricks that are required to make that happen from a strictly SQL standpoint. But, I am curious the best way to go in JDeveloper.
Right now, maintenance is not my biggest concern, so I probably would go for creating the best performing database view that I can while handling the business rules in the application.
As I gain expertise, I know that I will rethink my development approaches.
Performance is the main driver behind the question because I am wondering if it is faster to send a single large record set across a network or several small ones and "assemble" them at the client level.
Probably is better to send one large record, but you will need to take in account time required to create this one large record in db(maybe oracle object types, or arrays of oracle object types).
Check this for some VO performance advices: Advanced View Object Techniques (especially property: "In Batches Of" which defines number of roundtrips between app server and db)
As far as creating an updatable database view, I know there are minor tricks that are required to make that happen from a strictly SQL standpoint. But, I am curious the best way to go in JDeveloper.
number and use of data also should be taken into consideration. Appears to me that with your one large record, you bring down all data at once no matter of it is use or not. View Objects in ADF BC can fetch data on demand, which means that e.g. initially 10 rows are fetched and then, as the user continues, more rows are read. This appears to be of much better performance than querying all at once. Note that a View Objects issues a query (select statement). So unless your data is queried from many different tables or aggregated using data you don't want to query to the middletier, I don't yet see why a database view would be faster in your case.
What about doing a little performance test? Just build the same query twice. First time using a database view and second time doing the same from a VO.
Ps.: Btw, if users are allowed to filter or sort queried data, then having a View Object based on an entity object comes with the benefit of the ADF BC entity cache
Your performance test suggestion is good. Is there an EXPLAIN PLAN equivalent in JDeveloper or do you have some other recommendation?
The final point that you made about filtering data is key. In our case, each user will query several hundred records so they can be approved in a custom application, and there will be hundred or more users working in the application concurrently. The underlying table receives numerous foreign keys from other tables although is not likely that actual foreign key constraints are in place (which I still need to confirm), and this data must be filterable but it is not necessary to update these other tables. So, that is why I am wondering whether if it is best to join the data in JDeveloper or join it in a database view.
By the way, I have been perusing the web as I learn JDeveloper and ran across some nice content that you authored. Thanks for that!