You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@cocoon.apache.org by Leszek Gawron <ou...@vip.net.pl> on 2003/03/04 16:59:11 UTC
Re: big ESQL performance problem - this one's weird
On wto, mar 04, 2003 at 04:25:09 +0100, Christian Haul wrote:
> On 04.Mar.2003 -- 04:08 PM, Leszek Gawron wrote:
> > 2. Same query run by cocoon:
> > INFO (2003-03-04) 16:03.13:573 [access]
> > (/romes/data-old/contractors-offline-bug) Thread-6/CocoonServlet:
> > 'romes/data-old/contractors-offline-bug' Processed by Apache Cocoon 2.1-dev in
> > 9.93095 minutes
> >
> > (this query is much bigger then the previous one I tested it on)
> >
> > My sitemap entries are simple: match, generate type="serverpages", serialize
> > to xml. nothing more.
> >
> > News from just a while ago: I've been monitoring CPU usage. It's not cocoon
> > which consumes most of CPU while fetching these data but pervasive. So I think
> > you should consider that esql performs too much database interaction (from
> > what I saw it does not cache metadata information and fetches it with every
> > row. Is there a way to omit esql use metadata ?
>
> Off my head esql does not care much about metadata. However,
> retrieving strings might be a problem since we try to have it
> one-size-fits-all and auto-magically do clobs and stuff as well. Would
> be interesting to know if e.g. <esql:get-object/> does a better job.
while trying to test the esql:get-object I have run just this:
<esql:execute-query>
<esql:query>
SELECT * from kontrah</esql:query>
<esql:results>
<esql:row-results><a></a></esql:row-results>
</esql:results>
</esql:execute-query>
The execution time does not differ much from first case even though I do not do any esql-getXXX.
ouzo
--
__
| / \ | Leszek Gawron // \\
\_\\ //_/ ouzo@vip.net.pl _\\()//_
.'/()\'. Phone: +48(600)341118 / // \\ \
\\ // recursive: adj; see recursive | \__/ |
Re: big ESQL performance problem - this one's weird
Posted by Leszek Gawron <ou...@vip.net.pl>.
On wto, mar 04, 2003 at 05:39:01 +0100, Torsten Curdt wrote:
> >>Not weird at all!
> >>You are still looping through the ResultSet (row-results)
> >
> >Yes but some people in this discussion blamed the amount of SAX events to
> >handle for bad performance. So now it is clear that it's the rowset
> >traversal
> >that consumes so much CPU, but still why?
>
> Well, because the traversal means that the JDBC driver transfers all the
> data of the ResultSet over the wire to the application. This happens no
> matter if you use the esql:get-xxx tags or not. (Well, it's all up to
> the driver's implementation. But...)
>
> I bet that's your bottleneck.
>
> > I'm not skilled in JDBC. The only
> >difference I can tell from Squirell SQL client is that it does not use
> >prepared
> >statements.
>
> Well, to be really sure you better create a JDBC testcase.
<xsp:logic>
try {
Connection conn = DriverManager.getConnection("jdbc:pervasive://localhost:1583/fppcsouzo");
Statement stmt = conn.createStatement();
stmt.execute( "select * from kontrah" );
ResultSet rs = stmt.getResultSet();
while ( rs.next() ) {
<ok/>
}
} catch ( SQLException e ) {
<error><xsp:expr>e.toString()</xsp:expr>!</error>
}
</xsp:logic>
result is :
....
Instead of 9 minutes (of course that one contained sax events also but it's irrevelant)
....
Thread-10/CocoonServlet: 'romes/data-old/contractors-offline-bug' Processed by
Apache Cocoon 2.1-dev in 7.17 seconds.
!!!
The question now is: why doesn't Pervasive like standard JdbcEsqlQuery and how
to write PervasiveJdbcQuery that works fast? Could anybody help ?
ouzo
--
__
| / \ | Leszek Gawron // \\
\_\\ //_/ ouzo@vip.net.pl _\\()//_
.'/()\'. Phone: +48(600)341118 / // \\ \
\\ // recursive: adj; see recursive | \__/ |
Re: big ESQL performance problem - this one's weird
Posted by Torsten Curdt <tc...@dff.st>.
>>Not weird at all!
>>You are still looping through the ResultSet (row-results)
>
> Yes but some people in this discussion blamed the amount of SAX events to
> handle for bad performance. So now it is clear that it's the rowset traversal
> that consumes so much CPU, but still why?
Well, because the traversal means that the JDBC driver transfers all the
data of the ResultSet over the wire to the application. This happens no
matter if you use the esql:get-xxx tags or not. (Well, it's all up to
the driver's implementation. But...)
I bet that's your bottleneck.
> I'm not skilled in JDBC. The only
> difference I can tell from Squirell SQL client is that it does not use prepared
> statements.
Well, to be really sure you better create a JDBC testcase.
--
Torsten
Re: big ESQL performance problem - this one's weird
Posted by Torsten Curdt <tc...@dff.st>.
>><snip/>
>>
>>>>Anyway - I'll add the pervasive support tonight after work, ok?
>>>
>>>Fantastic, Thanks a lot!
>>
>>Please try...
>
> Works fantastic, great thanks !
cool :)
--
Torsten
Re: big ESQL performance problem - this one's weird
Posted by Leszek Gawron <ou...@vip.net.pl>.
On śro, mar 05, 2003 at 06:14:39 +0100, Torsten Curdt wrote:
> <snip/>
> >>Anyway - I'll add the pervasive support tonight after work, ok?
> >
> >Fantastic, Thanks a lot!
>
> Please try...
Works fantastic, great thanks !
ouzo
--
__
| / \ | Leszek Gawron // \\
\_\\ //_/ ouzo@vip.net.pl _\\()//_
.'/()\'. Phone: +48(600)341118 / // \\ \
\\ // recursive: adj; see recursive | \__/ |
Re: big ESQL performance problem - this one's weird
Posted by Torsten Curdt <tc...@dff.st>.
<snip/>
>>Anyway - I'll add the pervasive support tonight after work, ok?
>
> Fantastic, Thanks a lot!
Please try...
--
Torsten
Re: big ESQL performance problem - this one's weird
Posted by Leszek Gawron <ou...@vip.net.pl>.
On śro, mar 05, 2003 at 01:04:41 +0100, Torsten Curdt wrote:
> Leszek Gawron wrote:
> >On śro, mar 05, 2003 at 12:45:53 +0100, Torsten Curdt wrote:
> >
> >>...then we can just easily add support for pervasive and you should be
> >>fine. So we need to create the PervasiveEsqlQuery class and register it
> >>in the EsqlConnection class. Does pervasive support any kind of
> >>ResultSet limiting (like LIMIT or TOP)?
> >
> >no it does not, it also has problems with retrieving @@identity value -
> >select
> >max(id) should be used instead
>
> Ok - so then we only have *really* bad max row / skip row support for
> pervasive :-/
>
> Anyway - I'll add the pervasive support tonight after work, ok?
Fantastic, Thanks a lot!
ouzo
--
__
| / \ | Leszek Gawron // \\
\_\\ //_/ ouzo@vip.net.pl _\\()//_
.'/()\'. Phone: +48(600)341118 / // \\ \
\\ // recursive: adj; see recursive | \__/ |
Re: big ESQL performance problem - this one's weird
Posted by Torsten Curdt <tc...@dff.st>.
Leszek Gawron wrote:
> On śro, mar 05, 2003 at 12:45:53 +0100, Torsten Curdt wrote:
>
>>...then we can just easily add support for pervasive and you should be
>>fine. So we need to create the PervasiveEsqlQuery class and register it
>>in the EsqlConnection class. Does pervasive support any kind of
>>ResultSet limiting (like LIMIT or TOP)?
>
> no it does not, it also has problems with retrieving @@identity value - select
> max(id) should be used instead
Ok - so then we only have *really* bad max row / skip row support for
pervasive :-/
Anyway - I'll add the pervasive support tonight after work, ok?
--
Torsten
Re: big ESQL performance problem - this one's weird
Posted by Peter Royal <pr...@apache.org>.
On Wednesday, March 5, 2003, at 06:52 AM, Leszek Gawron wrote:
> On śro, mar 05, 2003 at 12:45:53 +0100, Torsten Curdt wrote:
>> ...then we can just easily add support for pervasive and you should be
>> fine. So we need to create the PervasiveEsqlQuery class and register
>> it
>> in the EsqlConnection class. Does pervasive support any kind of
>> ResultSet limiting (like LIMIT or TOP)?
> no it does not, it also has problems with retrieving @@identity value
> - select
> max(id) should be used instead
And be sure to set your transaction isolation level to serialized (the
only other one pervasive supports is read-committed) otherwise you may
end up reading the wrong max value if another transaction commits
between your insert and select max call.
and also contact pervasive to get the fix for incident A30241825. by
default, when a table is involved in a transaction it is locked and
parallel threads trying to do a transaction on that table get a
sqlexception thrown since the table is locked. the above incident has a
patch that switches that to a blocking behavior.
-pete
Re: big ESQL performance problem - this one's weird
Posted by Leszek Gawron <ou...@vip.net.pl>.
On śro, mar 05, 2003 at 12:45:53 +0100, Torsten Curdt wrote:
> ...then we can just easily add support for pervasive and you should be
> fine. So we need to create the PervasiveEsqlQuery class and register it
> in the EsqlConnection class. Does pervasive support any kind of
> ResultSet limiting (like LIMIT or TOP)?
no it does not, it also has problems with retrieving @@identity value - select
max(id) should be used instead
ouzo
--
__
| / \ | Leszek Gawron // \\
\_\\ //_/ ouzo@vip.net.pl _\\()//_
.'/()\'. Phone: +48(600)341118 / // \\ \
\\ // recursive: adj; see recursive | \__/ |
Re: big ESQL performance problem - this one's weird
Posted by Torsten Curdt <tc...@dff.st>.
Leszek Gawron wrote:
> On śro, mar 05, 2003 at 11:52:42 +0100, Torsten Curdt wrote:
>
>>>got tired of waiting to finish so it will take at least 5 minutes (last
>>>time it took 9.9 minutes)
>
> cannot really tell how much it took - poor wget timed out
>
>>Could you try the PreparedStatment testcase without
>>TYPE_SCROLL_INSENSITIVE, CONCUR_READ_ONLY and the Statement testcase
>
> PreparedStatement WITHOUT flags works just as fast as the Statement (results
> are similar : 4.7 - 5 seconds in each case)
Hurray :)
...then we can just easily add support for pervasive and you should be
fine. So we need to create the PervasiveEsqlQuery class and register it
in the EsqlConnection class. Does pervasive support any kind of
ResultSet limiting (like LIMIT or TOP)?
> Statement WITH flags works slow as hell.
>
> So it's strictly connected with ResultSet flags
yepp
--
Torsten
Re: big ESQL performance problem - this one's weird
Posted by Leszek Gawron <ou...@vip.net.pl>.
On śro, mar 05, 2003 at 11:52:42 +0100, Torsten Curdt wrote:
> >got tired of waiting to finish so it will take at least 5 minutes (last
> >time it took 9.9 minutes)
cannot really tell how much it took - poor wget timed out
>
> Could you try the PreparedStatment testcase without
> TYPE_SCROLL_INSENSITIVE, CONCUR_READ_ONLY and the Statement testcase
PreparedStatement WITHOUT flags works just as fast as the Statement (results
are similar : 4.7 - 5 seconds in each case)
Statement WITH flags works slow as hell.
So it's strictly connected with ResultSet flags
ouzo
--
__
| / \ | Leszek Gawron // \\
\_\\ //_/ ouzo@vip.net.pl _\\()//_
.'/()\'. Phone: +48(600)341118 / // \\ \
\\ // recursive: adj; see recursive | \__/ |
Re: big ESQL performance problem - this one's weird
Posted by Christian Haul <ha...@dvs1.informatik.tu-darmstadt.de>.
On 05.Mar.2003 -- 11:46 AM, Torsten Curdt wrote:
> >Doing it automatically depending on the DBMS is a problem,
> >though, because we would have to escape and convert parameters.
>
> It's not that easy. "0 parameters" does not necessarily mean "use
> Statement over PreparedStatement" - that's the point. So either we make
> it configurable (adding a esql tag or attribute) or other people might
> complain.
Right. But since the current issue seems to be solved, we can well
postpone this.
Re-using the prepared statement is a good idea, though.
Chris.
--
C h r i s t i a n H a u l
haul@informatik.tu-darmstadt.de
fingerprint: 99B0 1D9D 7919 644A 4837 7D73 FEF9 6856 335A 9E08
Re: big ESQL performance problem - this one's weird
Posted by Torsten Curdt <tc...@dff.st>.
<snip/>
> You're joking. At least in book, full featured would entail providing
> a SQL parser and talking to the server native protocol. Certainly not
> as part of Cocoon.
Well, I did not mean it *that* full featured way ;)
Though it might be worth to have a look into all that is available...
I thought more in direction of a capabilities "repository" (sorry, I
just don't have good term for it yet) that hides some of those details
than you usually wanna think about. We could make it a component that
can be accessed from the database modules as well as from esql.
<snip/>
> IOW, yes, it is a cool idea and would help many people a great deal if
> done correctly. But it's too big to be a part of Cocoon.
aggree
> Adding back plain Statements and loading the decision on the developer
> is good.
hm... I would not call it "good"
> Doing it automatically depending on the DBMS is a problem,
> though, because we would have to escape and convert parameters.
It's not that easy. "0 parameters" does not necessarily mean "use
Statement over PreparedStatement" - that's the point. So either we make
it configurable (adding a esql tag or attribute) or other people might
complain.
--
Torsten
Re: big ESQL performance problem - this one's weird
Posted by Christian Haul <ha...@dvs1.informatik.tu-darmstadt.de>.
On 05.Mar.2003 -- 09:43 AM, Torsten Curdt wrote:
> Leszek Gawron wrote:
> >On ??ro, mar 05, 2003 at 01:16:26 +0100, Torsten Curdt wrote:
> >
> >>So you think cocoon should fix all the bugs that software vendor X
> >>refuses to fix? come on!
> >
> >I have the "pervasive case" in my mind and was reffering to that. I think
> >it's
> >not even a bug (that awful performance) but the way they have designed the
> >SQL
> >layer on Btrieve DB - you have to use it in a specific way to get your
> >results
> >quite fast or you won't get them at all. It's a matter of cocoon if it will
> >support the database or not.
>
> ...well, so you ask for a per database behaviour of esql.
> Doable but not done yet.
>
> Hm... Chris, what do you think about creating a full featured database
> abstraction layer?
You're joking. At least in book, full featured would entail providing
a SQL parser and talking to the server native protocol. Certainly not
as part of Cocoon.
There's Avalon DB for example
http://avalon.apache.org/apps/apps/db/index.html (no idea about the
status, no builds seem available)
There's Jakarta Commons SQL
http://jakarta.apache.org/commons/sandbox/sql/index.html (not quite
the same)
Certainly, OJB needs to take care of such things as well (while it's
not their main point) http://db.apache.org/ojb/
Obviously, Torque has that as well http://db.apache.org/torque/
IOW, yes, it is a cool idea and would help many people a great deal if
done correctly. But it's too big to be a part of Cocoon.
Adding back plain Statements and loading the decision on the developer
is good. Doing it automatically depending on the DBMS is a problem,
though, because we would have to escape and convert parameters.
Chris.
--
C h r i s t i a n H a u l
haul@informatik.tu-darmstadt.de
fingerprint: 99B0 1D9D 7919 644A 4837 7D73 FEF9 6856 335A 9E08
Re: big ESQL performance problem - this one's weird
Posted by Torsten Curdt <tc...@dff.st>.
Leszek Gawron wrote:
> On śro, mar 05, 2003 at 01:16:26 +0100, Torsten Curdt wrote:
>
>>So you think cocoon should fix all the bugs that software vendor X
>>refuses to fix? come on!
>
> I have the "pervasive case" in my mind and was reffering to that. I think it's
> not even a bug (that awful performance) but the way they have designed the SQL
> layer on Btrieve DB - you have to use it in a specific way to get your results
> quite fast or you won't get them at all. It's a matter of cocoon if it will
> support the database or not.
...well, so you ask for a per database behaviour of esql.
Doable but not done yet.
Hm... Chris, what do you think about creating a full featured database
abstraction layer?
--
Torsten
Re: big ESQL performance problem - this one's weird
Posted by Leszek Gawron <ou...@vip.net.pl>.
On śro, mar 05, 2003 at 01:16:26 +0100, Torsten Curdt wrote:
> So you think cocoon should fix all the bugs that software vendor X
> refuses to fix? come on!
I have the "pervasive case" in my mind and was reffering to that. I think it's
not even a bug (that awful performance) but the way they have designed the SQL
layer on Btrieve DB - you have to use it in a specific way to get your results
quite fast or you won't get them at all. It's a matter of cocoon if it will
support the database or not.
> > 3. you have to power to make the invalid driver usable and the user
> > won't even notice (sometimes developers cannot choose a database/driver just
> > like me) - why not use it ?:)
>
> Sorry - didn't get that one... you mean you are not in charge to decide?
Decide which database to use? No. I'm using cocoon as a layer between mobile
device and an accounting system. We want to prepare specialized versions of
applications for leading accounting systems for small business. And what can I
do if nearly all of them are MS SQL Server and Pervasive/Btrieve or at best
Sybase? The main advantage of intruducing our application in the company is
that they do not have to change the tools they use so database is also no
matter of discussion.
ouzo
--
__
| / \ | Leszek Gawron // \\
\_\\ //_/ ouzo@vip.net.pl _\\()//_
.'/()\'. Phone: +48(600)341118 / // \\ \
\\ // recursive: adj; see recursive | \__/ |
Re: big ESQL performance problem - this one's weird
Posted by Torsten Curdt <tc...@dff.st>.
> got tired of waiting to finish so it will take at least 5 minutes (last time it took 9.9 minutes)
Could you try the PreparedStatment testcase without
TYPE_SCROLL_INSENSITIVE, CONCUR_READ_ONLY and the Statement testcase
with, please?
Thanks
--
Torsten
Re: big ESQL performance problem - this one's weird
Posted by Leszek Gawron <ou...@vip.net.pl>.
On śro, mar 05, 2003 at 01:16:26 +0100, Torsten Curdt wrote:
> > > Anyway, Leszek, could you please check with the same ResultSet type
> > > (ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY)
> > > as in the JdbcEsqlQuery?
> > Sure no problem, gonna make this my first thing to do in the morning
Let me give you my test cases:
p:logic>
try {
Connection conn = DriverManager.getConnection("jdbc:pervasive://localhost:1583/fppcsouzo");
Statement stmt = conn.createStatement();
stmt.execute( "select * from kontrah" );
ResultSet rs = stmt.getResultSet();
while ( rs.next() ) {
<first><xsp:expr>rs.getString(1)</xsp:expr></first>
}
} catch ( SQLException e ) {
<error><xsp:expr>e.toString()</xsp:expr>!</error>
}
</xsp:logic>
this runs about 5 seconds for table with 5.5k rows
while this one:
<xsp:logic>
try {
Connection conn = DriverManager.getConnection("jdbc:pervasive://localhost:1583/fppcsouzo");
PreparedStatement prepstmt = conn.prepareStatement(
"select * from kontrah",
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY );
prepstmt.execute();
ResultSet rs = prepstmt.getResultSet();
while ( rs.next() ) {
<first><xsp:expr>rs.getString(1)</xsp:expr></first>
}
} catch ( SQLException e ) {
<error><xsp:expr>e.toString()</xsp:expr>!</error>
}
</xsp:logic>
got tired of waiting to finish so it will take at least 5 minutes (last time it took 9.9 minutes)
ouzo
--
__
| / \ | Leszek Gawron // \\
\_\\ //_/ ouzo@vip.net.pl _\\()//_
.'/()\'. Phone: +48(600)341118 / // \\ \
\\ // recursive: adj; see recursive | \__/ |
Re: big ESQL performance problem - this one's weird
Posted by Torsten Curdt <tc...@dff.st>.
> > it's faster. So whom shall we kick in the but? Database A user or
> > database B user? Of course we could make it... configurable *ring* *ring*
> maybe just esql:query and esql:prepared query?
It's not the logicsheet.but the helper classes that create the
"problem". Of course it is doable - the question is if it's desirable.
We need to decide per database what type of statement to create if 0
parameters or n parameters are given.
> > IMO it is not cocoon's job to use the best method for each individual
> > database only because the database engine and/or the driver does not do
> > "this" or "that" internally. (Why not file a bug against the driver/db?)
> because:
> 1. if you ask M$ to fix a bug they will send you a nice "Thank you for your
> cooperation" and nothing else will happen
So you think cocoon should fix all the bugs that software vendor X
refuses to fix? come on!
> 2. even if it's something more than that you never know how much you will have
> to wait for that
Well, or you have to pay for it. That's how it works in the commercial
world ;)
> 3. you have to power to make the invalid driver usable and the user
> won't even notice (sometimes developers cannot choose a database/driver just
> like me) - why not use it ?:)
Sorry - didn't get that one... you mean you are not in charge to decide?
> 4. wouldn't it be nice that cocoon is always there for you when you need it
> with no additional work?
Sure it would be cool if cocoon could bake bread - sorry;)
> 5. see sources - it is already done already (fixes for Informix, Oracle)
Don't get me wrong if we could fix it by just adding a new type of query
to work around - no problem. This can be done in 10 minutes. But maybe
we need to change some of the interfaces of the helper classes. I
already have something in mind... But I need to check the logicsheet
tomorrow.
Just waiting for your results...
<snip/>
> Cocoon's functionality is fantastic but it's not nice to spend more time
> setting up database <-> cocoon connection that actualy use it.
Well, as always this depends on how many people used this database with
cocoon before :)
<snip/>
> If you need an additional person to solve the
> case - take me :)
Be sure I'll get back to you ;)
> > Anyway, Leszek, could you please check with the same ResultSet type
> > (ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY)
> > as in the JdbcEsqlQuery?
> Sure no problem, gonna make this my first thing to do in the morning
Excellent!
> > I removed the Statements - so I'll try to help you get this fixed -
> > anyhow :)
> thanks a lot
No problem :)
--
Torsten
Re: big ESQL performance problem - this one's weird
Posted by Leszek Gawron <ou...@vip.net.pl>.
On wto, mar 04, 2003 at 07:27:28 +0100, Torsten Curdt wrote:
> >Ooops. Torsten, I believe this changed with your refactoring, right?
> >(2.0.5 still uses plain statements in this case) Any idea why you have
> >changed this? (OK, PreparedStatements do have advantages, but)
>
> Yes, I did. IIRC it lead to a much easier handling inside the classes.
> And due to the reported SQL injection I wanted to have as least "simple"
> Statements inside the codebase as possible.
>
> The problem is: the PreparedStatment vs Statement issue is very
> controversial. Especially if you do not reuse the same PreparedStatment
> object. Speed advantage or disadvantage seem to vary from database to
> database.
>
> In general you often get the advice [1] to always use Prepared
> Statements. But some people are reporting it to be slower - others that
> it's faster. So whom shall we kick in the but? Database A user or
> database B user? Of course we could make it... configurable *ring* *ring*
maybe just esql:query and esql:prepared query?
> IMO it is not cocoon's job to use the best method for each individual
> database only because the database engine and/or the driver does not do
> "this" or "that" internally. (Why not file a bug against the driver/db?)
because:
1. if you ask M$ to fix a bug they will send you a nice "Thank you for your
cooperation" and nothing else will happen
2. even if it's something more than that you never know how much you will have
to wait for that
3. you have to power to make the invalid driver usable and the user
won't even notice (sometimes developers cannot choose a database/driver just
like me) - why not use it ?:)
4. wouldn't it be nice that cocoon is always there for you when you need it
with no additional work?
5. see sources - it is already done already (fixes for Informix, Oracle)
> I mean we *could* think about some sort of database capabilites
> database... (weird name;) some sort of an abstraction thing. Chris, you
> remember? I was talking about merging the database abstraction from esql
> and the database modules. That could be the first step ...but I don't
> really know if we really should follow that path.
Cocoon's functionality is fantastic but it's not nice to spend more time
setting up database <-> cocoon connection that actualy use it. If it involves
fixing someone else's problems for our own purposes so be it. Maybe the bug
authors will never fix it but some of us still have to use the product.
For me cocoon is most fantastic as database abstraction layer. This is also
the part of cocoon I most understand. I have little experience in cocoon core,
medium in Java but a lot of in C++/OOP and I'm willing to contribute but I do
not know where to start from. If you need an additional person to solve the
case - take me :)
>
> Anyway, Leszek, could you please check with the same ResultSet type
> (ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY)
> as in the JdbcEsqlQuery?
Sure no problem, gonna make this my first thing to do in the morning
>
> I removed the Statements - so I'll try to help you get this fixed -
> anyhow :)
thanks a lot
ouzo
--
__
| / \ | Leszek Gawron // \\
\_\\ //_/ ouzo@vip.net.pl _\\()//_
.'/()\'. Phone: +48(600)341118 / // \\ \
\\ // recursive: adj; see recursive | \__/ |
Re: big ESQL performance problem - this one's weird
Posted by Torsten Curdt <tc...@dff.st>.
> Ooops. Torsten, I believe this changed with your refactoring, right?
> (2.0.5 still uses plain statements in this case) Any idea why you have
> changed this? (OK, PreparedStatements do have advantages, but)
Yes, I did. IIRC it lead to a much easier handling inside the classes.
And due to the reported SQL injection I wanted to have as least "simple"
Statements inside the codebase as possible.
The problem is: the PreparedStatment vs Statement issue is very
controversial. Especially if you do not reuse the same PreparedStatment
object. Speed advantage or disadvantage seem to vary from database to
database.
In general you often get the advice [1] to always use Prepared
Statements. But some people are reporting it to be slower - others that
it's faster. So whom shall we kick in the but? Database A user or
database B user? Of course we could make it... configurable *ring* *ring*
IMO it is not cocoon's job to use the best method for each individual
database only because the database engine and/or the driver does not do
"this" or "that" internally. (Why not file a bug against the driver/db?)
I mean we *could* think about some sort of database capabilites
database... (weird name;) some sort of an abstraction thing. Chris, you
remember? I was talking about merging the database abstraction from esql
and the database modules. That could be the first step ...but I don't
really know if we really should follow that path.
I mean: JDBC is already supposed to be some kind of database
abstraction. Well, supposed...
Anyway, Leszek, could you please check with the same ResultSet type
(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY)
as in the JdbcEsqlQuery?
I removed the Statements - so I'll try to help you get this fixed -
anyhow :)
cheers
--
Torsten
[1] http://www.javaperformancetuning.com/tips/jdbc.shtml
Re: big ESQL performance problem - this one's weird
Posted by Christian Haul <ha...@dvs1.informatik.tu-darmstadt.de>.
On 04.Mar.2003 -- 05:44 PM, Leszek Gawron wrote:
> On wto, mar 04, 2003 at 05:26:01 +0100, Christian Haul wrote:
> >
> > If you have a <esql:parameter .../> in your query, esql uses a
> > prepared statement. Otherwise it won't.
> Strange: even though my query does not contain esql:parameter tags what I see
> in my source is:
> //!!!!!!! _esql_query.prepareStatement();
> _esql_query.execute();
Ooops. Torsten, I believe this changed with your refactoring, right?
(2.0.5 still uses plain statements in this case) Any idea why you have
changed this? (OK, PreparedStatements do have advantages, but)
Chris.
--
C h r i s t i a n H a u l
haul@informatik.tu-darmstadt.de
fingerprint: 99B0 1D9D 7919 644A 4837 7D73 FEF9 6856 335A 9E08
Re: big ESQL performance problem - this one's weird
Posted by ha...@dvs1.informatik.tu-darmstadt.de.
On 04.Mar.2003 -- 05:44 PM, Leszek Gawron wrote:
> On wto, mar 04, 2003 at 05:26:01 +0100, Christian Haul wrote:
> >
> > If you have a <esql:parameter .../> in your query, esql uses a
> > prepared statement. Otherwise it won't.
> Strange: even though my query does not contain esql:parameter tags what I see
> in my source is:
> //!!!!!!! _esql_query.prepareStatement();
> _esql_query.execute();
Ooops. Torsten, I believe this changed with your refactoring, right?
(2.0.5 still uses plain statements in this case) Any idea why you have
changed this? (OK, PreparedStatements do have advantages, but)
Chris.
--
C h r i s t i a n H a u l
haul@informatik.tu-darmstadt.de
fingerprint: 99B0 1D9D 7919 644A 4837 7D73 FEF9 6856 335A 9E08
Re: big ESQL performance problem - this one's weird
Posted by Leszek Gawron <ou...@vip.net.pl>.
On wto, mar 04, 2003 at 05:26:01 +0100, Christian Haul wrote:
> On 04.Mar.2003 -- 05:11 PM, Leszek Gawron wrote:
> > On wto, mar 04, 2003 at 05:05:37 +0100, Torsten Curdt wrote:
> > > >while trying to test the esql:get-object I have run just this:
> > > ><esql:execute-query>
> > > > <esql:query>
> > > > SELECT * from kontrah</esql:query>
> > > > <esql:results>
> > > > <esql:row-results><a></a></esql:row-results>
> > > > </esql:results>
> > > ></esql:execute-query>
> > > >
> > > >The execution time does not differ much from first case even though I do
> > > >not do any esql-getXXX.
> > >
> > > Not weird at all!
> > > You are still looping through the ResultSet (row-results)
> > Yes but some people in this discussion blamed the amount of SAX events to
> > handle for bad performance. So now it is clear that it's the rowset traversal
> > that consumes so much CPU, but still why? I'm not skilled in JDBC. The only
> > difference I can tell from Squirell SQL client is that it does not use prepared
> > statements.
>
> If you have a <esql:parameter .../> in your query, esql uses a
> prepared statement. Otherwise it won't.
Strange: even though my query does not contain esql:parameter tags what I see
in my source is:
_esql_query = _esql_connection.createQuery(
String.valueOf(""),
String.valueOf(
""
+ " SELECT"
+ " * from kontrah"
)
);
try {
//!!!!!!! _esql_query.prepareStatement();
_esql_query.execute();
if (getLogger().isDebugEnabled()) {
getLogger().debug("esql query: " + _esql_query.getQueryString());
}
ouzo
--
__
| / \ | Leszek Gawron // \\
\_\\ //_/ ouzo@vip.net.pl _\\()//_
.'/()\'. Phone: +48(600)341118 / // \\ \
\\ // recursive: adj; see recursive | \__/ |
Re: big ESQL performance problem - this one's weird
Posted by Christian Haul <ha...@dvs1.informatik.tu-darmstadt.de>.
On 04.Mar.2003 -- 05:11 PM, Leszek Gawron wrote:
> On wto, mar 04, 2003 at 05:05:37 +0100, Torsten Curdt wrote:
> > >while trying to test the esql:get-object I have run just this:
> > ><esql:execute-query>
> > > <esql:query>
> > > SELECT * from kontrah</esql:query>
> > > <esql:results>
> > > <esql:row-results><a></a></esql:row-results>
> > > </esql:results>
> > ></esql:execute-query>
> > >
> > >The execution time does not differ much from first case even though I do
> > >not do any esql-getXXX.
> >
> > Not weird at all!
> > You are still looping through the ResultSet (row-results)
> Yes but some people in this discussion blamed the amount of SAX events to
> handle for bad performance. So now it is clear that it's the rowset traversal
> that consumes so much CPU, but still why? I'm not skilled in JDBC. The only
> difference I can tell from Squirell SQL client is that it does not use prepared
> statements.
If you have a <esql:parameter .../> in your query, esql uses a
prepared statement. Otherwise it won't.
Another interesting point would be to restrict the data retrieved by
using "select some_column from kontrah" instead of "select * from kontrah"
Might have a performance impact depending on how smart the driver is.
Other things that might apply are options to the jdbc driver on cache
/ transfer size, isolation modes etc.
Chris.
--
C h r i s t i a n H a u l
haul@informatik.tu-darmstadt.de
fingerprint: 99B0 1D9D 7919 644A 4837 7D73 FEF9 6856 335A 9E08
Re: big ESQL performance problem - this one's weird
Posted by Peter Royal <pr...@apache.org>.
On Tuesday, March 4, 2003, at 11:11 AM, Leszek Gawron wrote:
> Yes but some people in this discussion blamed the amount of SAX events
> to
> handle for bad performance. So now it is clear that it's the rowset
> traversal
> that consumes so much CPU, but still why? I'm not skilled in JDBC. The
> only
> difference I can tell from Squirell SQL client is that it does not use
> prepared
> statements.
If you can, stick a profiler on it.
http://www.ej-technologies.com/products/jprofiler/overview.html is nice
and give you a 10-day trial.
We found that prepared statements were very slow under pervasive and
got a 25% boost by removing them. Make sure you are using the latest
pervasive JDBC driver too.
-pete
Re: big ESQL performance problem - this one's weird
Posted by Leszek Gawron <ou...@vip.net.pl>.
On wto, mar 04, 2003 at 05:05:37 +0100, Torsten Curdt wrote:
> >while trying to test the esql:get-object I have run just this:
> ><esql:execute-query>
> > <esql:query>
> > SELECT * from kontrah</esql:query>
> > <esql:results>
> > <esql:row-results><a></a></esql:row-results>
> > </esql:results>
> ></esql:execute-query>
> >
> >The execution time does not differ much from first case even though I do
> >not do any esql-getXXX.
>
> Not weird at all!
> You are still looping through the ResultSet (row-results)
Yes but some people in this discussion blamed the amount of SAX events to
handle for bad performance. So now it is clear that it's the rowset traversal
that consumes so much CPU, but still why? I'm not skilled in JDBC. The only
difference I can tell from Squirell SQL client is that it does not use prepared
statements.
ouzo
--
__
| / \ | Leszek Gawron // \\
\_\\ //_/ ouzo@vip.net.pl _\\()//_
.'/()\'. Phone: +48(600)341118 / // \\ \
\\ // recursive: adj; see recursive | \__/ |
Re: big ESQL performance problem - this one's weird
Posted by Torsten Curdt <tc...@dff.st>.
> while trying to test the esql:get-object I have run just this:
> <esql:execute-query>
> <esql:query>
> SELECT * from kontrah</esql:query>
> <esql:results>
> <esql:row-results><a></a></esql:row-results>
> </esql:results>
> </esql:execute-query>
>
> The execution time does not differ much from first case even though I do not do any esql-getXXX.
Not weird at all!
You are still looping through the ResultSet (row-results)
--
Torsten