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: 
 <CALnPV8QPxo62MW677nRnN3DTkz-nC6QLyp0hOxxbbEmzXp=g=g@mail.gmail.com>
References: 
 <CALnPV8S5AzmV-oZLBgnS0mphhhFuGjMYfExWWTzru49PESX06g@mail.gmail.com>
 <CAM1yOjbuSiSMLo=9MfwbsaKmMFSvGuzCoE5NKLHeEziNUB6jmA@mail.gmail.com>
 <CALnPV8QPxo62MW677nRnN3DTkz-nC6QLyp0hOxxbbEmzXp=g=g@mail.gmail.com>
From: Michael Gentry <mgentry@masslight.net>
Date: Thu, 15 Mar 2012 12:35:00 -0400
Message-ID: 
 <CAA-upzrSrUkdOtjU2h7JQ4pDKDQU+kdke+fCCLSW+wXsecQVBg@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

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=F1eda <emecas@gmail.com> wr=
ote:
> Hi Mike
>
> My context: I am handling a table with 50 millions of rows and its size i=
s
> 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>wr=
ote:
>
>> 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 my
>> 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 l=
ike
>> > 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.timestamppropert=
y, "%"));
>> >
>> >
>> > Thank you for your time
>> >
>> > EMERSON
>>