I am not an expert in this area. I suspect it depends on your database and environment.
My guess is that a view that only added those three columns would have very little impact on performance. The primary difference is that you've moved the conversion of those items from the application to the database side. But converting a timestamp to a day, time, year is a pretty basic database function. You'll be transferring 3 additional columns over the network on queries, but again it's pretty negligible. So, yes, it might have a minor performance impact, or it might have none. An Oracle View would likely have no impact. Can't speak for views on other databases. The real question is whether your code maintenance is going to increase by having to use SQLTemplates instead of native Cayenne objects. 2012/3/15 Emerson Castañeda <[email protected]>: > Hi Mike > > My context: I am handling a table with 50 millions of rows and its size is > increasing up every day some thousands. So, my question goes around > performance of the view's solution that you propose. > > Do you think view performance will be better than using SQLTemplate > queries? > > Thanks > > EMERSON > > On Thu, Mar 15, 2012 at 3:07 PM, Mike Kienenberger <[email protected]>wrote: > >> Depending on your database and usage, another option is to set up a >> view and let the database create derived columns for these, then you >> can treat them as regular Cayenne data object fields. >> >> 2012/3/14 Emerson Castañeda <[email protected]>: >> > HI everyone >> > >> > I have a table with a timestamp field, so I'm thinking about how to >> write >> > the next query without define a store procedure, but directly from my >> java >> > code: >> > >> > select * >> > from table1 >> > where >> > id = 1 and >> > extract(day from timestampField)='04' and >> > extract(month from timestampField)='04' and >> > extract(year from timestampField)='2009' >> > >> > I'd like to know if that is possible using cayenne, maybe some thing like >> > this? >> > >> > >> > SelectQuery query = new SelectQuery(Table1.class); >> > query.andQualifier(<??EXPRESSION???>) >> > >> > OR >> > >> > SelectQuery query = new SelectQuery(Table1.class); >> > query.andQualifier( ExpressionFactory.likeIgnoreCaseExp( >> > Table1.timestampproperty, "%")); >> > >> > >> > Thank you for your time >> > >> > EMERSON >>
