cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Gentry <mgen...@masslight.net>
Subject Re: Howto Include Time/Date Functions in Query Expressions
Date Thu, 15 Mar 2012 16:52:53 GMT
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 <mkienenb@gmail.com> 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 <mgentry@masslight.net> 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 <emecas@gmail.com> 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 <mkienenb@gmail.com>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 <emecas@gmail.com>:
>>>> > 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
>>>>

Mime
View raw message