You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Anurag Sethi <as...@yahoo.com> on 2009/01/05 04:01:11 UTC
Dynamic Query and Prepared Statement Caching
Hi,
I am using the Abator generated queryByExample sql/DAO classes. I am specifically using the Criteria class in there and have a bunch of criteria ored in the example that I send as the parameter.
So in general, I have the actual query with different number of criteria ored.
Does Ibatis cache the resulting Prepared statements too? If not, what is the best way to make sure that the statements are not prepared every time the queryByExample interface is called. Its a big performance hit.
I have also tried generating an sql map programatically as part of the bootstrap sequence and actually put in around 300 different static queries in there and call the one based on the number of criteria I would have had to or using the queryByExample dynamic query. This should at-least lead to caching of the prepared statements. Please correct me if I am wrong.
Thanks,
Anurag
Re: Dynamic Query and Prepared Statement Caching
Posted by Ian Butcher <ib...@real.com>.
Which driver are you using? I have found that the Oracle thin driver
(presumably all Oracle drivers act the same in this regard) works
vastly (50-100%) better if you turn on statement level caching a the
Connection level. This is using PreparedStatements and caching at a
higher level. You can set this using the callback interface with
C3P0. If you need more details let me know.
Ian.
On Jan 5, 2009, at 8:25 AM, Anurag Sethi wrote:
> Thanks. One thing I noticed though, that for both the cases, even
> when I have cachestatementenabled set to true in the sqlMapConfig,
> the log still comes out with the info that the statement is being
> prepared before its executed.
>
> Thanks,
> Anurag
>
> --- On Mon, 1/5/09, Jeff Butler <je...@gmail.com> wrote:
> From: Jeff Butler <je...@gmail.com>
> Subject: Re: Dynamic Query and Prepared Statement Caching
> To: user-java@ibatis.apache.org, asethi_im@yahoo.com
> Date: Monday, January 5, 2009, 3:46 PM
>
> iBATIS does cache prepared statements. The cache is based on the SQL
> string that is created after the dynamic query is resolved. So if you
> have 300 different combinations of criteria, then you
> will have 300
> cached prepared statements.
>
> Jeff Butler
>
>
> On Sun, Jan 4, 2009 at 9:01 PM, Anurag Sethi <as...@yahoo.com>
> wrote:
> > Hi,
> > I am using the Abator generated queryByExample sql/DAO classes. I am
> specifically using the Criteria class in there and have a bunch of
> criteria ored
> in the example that I send as the parameter.
> >
> > So in general, I have the actual query with different number of
> criteria
> ored.
> >
> > Does Ibatis cache the resulting Prepared statements too? If not,
> what is
> the best way to make sure that the statements are not prepared every
> time the
> queryByExample interface is called. Its a big performance hit.
> >
> > I have also tried generating an sql map programatically as part of
> the
> bootstrap sequence and actually put in around 300 different static
> queries in
> there and call the one based on the number of criteria I would have
> had to or
> using
> the queryByExample dynamic query. This should at-least lead to
> caching of
> the prepared statements. Please correct me if I am wrong.
> >
> > Thanks,
> > Anurag
> >
> >
> >
> >
> >
>
Re: Dynamic Query and Prepared Statement Caching
Posted by Anurag Sethi <as...@yahoo.com>.
Thanks. One thing I noticed though, that for both the cases, even when I have cachestatementenabled set to true in the sqlMapConfig, the log still comes out with the info� that the statement is being prepared before its executed.
Thanks,
Anurag
--- On Mon, 1/5/09, Jeff Butler <je...@gmail.com> wrote:
From: Jeff Butler <je...@gmail.com>
Subject: Re: Dynamic Query and Prepared Statement Caching
To: user-java@ibatis.apache.org, asethi_im@yahoo.com
Date: Monday, January 5, 2009, 3:46 PM
iBATIS does cache prepared statements. The cache is based on the SQL
string that is created after the dynamic query is resolved. So if you
have 300 different combinations of criteria, then you will have 300
cached prepared statements.
Jeff Butler
On Sun, Jan 4, 2009 at 9:01 PM, Anurag Sethi <as...@yahoo.com> wrote:
> Hi,
> I am using the Abator generated queryByExample sql/DAO classes. I am
specifically using the Criteria class in there and have a bunch of criteria ored
in the example that I send as the parameter.
>
> So in general, I have the actual query with different number of criteria
ored.
>
> Does Ibatis cache the resulting Prepared statements too? If not, what is
the best way to make sure that the statements are not prepared every time the
queryByExample interface is called. Its a big performance hit.
>
> I have also tried generating an sql map programatically as part of the
bootstrap sequence and actually put in around 300 different static queries in
there and call the one based on the number of criteria I would have had to or
using the queryByExample dynamic query. This should at-least lead to caching of
the prepared statements. Please correct me if I am wrong.
>
> Thanks,
> Anurag
>
>
>
>
>
Re: Dynamic Query and Prepared Statement Caching
Posted by Jeff Butler <je...@gmail.com>.
iBATIS does cache prepared statements. The cache is based on the SQL
string that is created after the dynamic query is resolved. So if you
have 300 different combinations of criteria, then you will have 300
cached prepared statements.
Jeff Butler
On Sun, Jan 4, 2009 at 9:01 PM, Anurag Sethi <as...@yahoo.com> wrote:
> Hi,
> I am using the Abator generated queryByExample sql/DAO classes. I am specifically using the Criteria class in there and have a bunch of criteria ored in the example that I send as the parameter.
>
> So in general, I have the actual query with different number of criteria ored.
>
> Does Ibatis cache the resulting Prepared statements too? If not, what is the best way to make sure that the statements are not prepared every time the queryByExample interface is called. Its a big performance hit.
>
> I have also tried generating an sql map programatically as part of the bootstrap sequence and actually put in around 300 different static queries in there and call the one based on the number of criteria I would have had to or using the queryByExample dynamic query. This should at-least lead to caching of the prepared statements. Please correct me if I am wrong.
>
> Thanks,
> Anurag
>
>
>
>
>