Mailing-List: contact user-help@cayenne.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cayenne.apache.org
Received-SPF: neutral (athena.apache.org: local policy)
MIME-Version: 1.0
In-Reply-To: 
 <CAM1yOjakWzs0ofvqXc-dMK1yyxBRGQa1fG4gzJkWSgTRveJUfw@mail.gmail.com>
References: 
 <CALnPV8S5AzmV-oZLBgnS0mphhhFuGjMYfExWWTzru49PESX06g@mail.gmail.com>
 <CAM1yOjbuSiSMLo=9MfwbsaKmMFSvGuzCoE5NKLHeEziNUB6jmA@mail.gmail.com>
 <CALnPV8QPxo62MW677nRnN3DTkz-nC6QLyp0hOxxbbEmzXp=g=g@mail.gmail.com>
 <CAA-upzrSrUkdOtjU2h7JQ4pDKDQU+kdke+fCCLSW+wXsecQVBg@mail.gmail.com>
 <CAM1yOjakWzs0ofvqXc-dMK1yyxBRGQa1fG4gzJkWSgTRveJUfw@mail.gmail.com>
From: Michael Gentry <mgentry@masslight.net>
Date: Thu, 15 Mar 2012 12:52:53 -0400
Message-ID: 
 <CAA-upzq4SHbAbob=4tey8d_NwNXQDUCsSrLNa7Bp4W7H9OGZ=Q@mail.gmail.com>
Subject: Re: Howto Include Time/Date Functions in Query Expressions
To: user@cayenne.apache.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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> wr=
ote:
> 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. =A0The best way to
>> improve performance is to have proper indexes (which a view could also
>> utilize). =A0We 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=F1eda <emecas@gmail.com>=
 wrote:
>>> Hi Mike
>>>
>>> My context: I am handling a table with 50 millions of rows and its size=
 is
>>> increasing up =A0every day some thousands. So, my question goes around
>>> performance of the view's solution that you propose.
>>>
>>> Do you think view performance =A0will 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=F1eda <emecas@gmail.com>:
>>>> > HI everyone
>>>> >
>>>> > I have a table with a timestamp field, so =A0I'm thinking about how =
to
>>>> write
>>>> > the next query without define a store procedure, but directly from m=
y
>>>> java
>>>> > code:
>>>> >
>>>> > select *
>>>> > from =A0table1
>>>> > where
>>>> > id =3D 1 and
>>>> > extract(day from timestampField)=3D'04' and
>>>> > extract(month from timestampField)=3D'04' and
>>>> > extract(year from timestampField)=3D'2009'
>>>> >
>>>> > I'd like to know if that is possible using cayenne, maybe some thing=
 like
>>>> > this?
>>>> >
>>>> >
>>>> > SelectQuery query =3D new SelectQuery(Table1.class);
>>>> > query.andQualifier(<??EXPRESSION???>)
>>>> >
>>>> > OR
>>>> >
>>>> > SelectQuery query =3D new SelectQuery(Table1.class);
>>>> > query.andQualifier( ExpressionFactory.likeIgnoreCaseExp(
>>>> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Table1.timestampprope=
rty, "%"));
>>>> >
>>>> >
>>>> > Thank you for your time
>>>> >
>>>> > EMERSON
>>>>