If the view is backing to 50m records, then it would still have to do a full table scan without indexes.
On Thu, Mar 15, 2012 at 12:42 PM, Mike Kienenberger <[email protected]> wrote: > In this particular case, since the view is just doing data conversion, > I don't think you'd even need indexes, would you? > > On Thu, Mar 15, 2012 at 12:35 PM, Michael Gentry <[email protected]> > wrote: >> Hi Emerson, >> >> I'm pretty sure a view will make no difference. The best way to >> improve performance is to have proper indexes (which a view could also >> utilize). We have a similar-sized database and query times went from >> over 1 minute to a few milliseconds once we added an appropriate >> index. >> >> mrg >> >> >> On Thu, Mar 15, 2012 at 11:29 AM, Emerson Castañeda <[email protected]> wrote: >>> 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 >>>>
