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 14:55:22 UTC
big ESQL performance problem
I've been fighting with this all day and cannot find a cause. I'm using quite
fresh esql implementation to access Pervasive SQL 2000 database. The problem
is I have to pull 5500 rows from database. Performing query in Squirell SQL it
takes 3 second to execute and 16 to build output table (build squirell view).
The same query executed under cocoon takes 3.5 MINUTES! I'm desperate.
Pervasive is a weird database (the SQL access is built on very low lewel
core). Maybe this has something to do with scrolling rowsets or something. My
query is really simple:
<esql:execute-query>
<esql:query>
SELECT
K.K_NIP AS Knt_NipE,
B.GST_KntID AS Knt_KntId,
B.GST_KntID AS Knt_Kod,
K.K_GRUPA AS Knt_Grupa,
K.K_Nazwa1 AS Knt_Nazwa1,
K.K_Nazwa2 AS Knt_Nazwa2,
K.K_NIP AS Knt_Nip,
K.K_Adres1 AS Knt_Ulica,
K.K_Miasto AS Knt_Miasto,
K.K_KodPocztowy AS Knt_KodPocztowy,
B.GST_TypMod AS SyncType
FROM
gemini_syn_knt B LEFT OUTER JOIN kontrah K ON K.K_KOD = B.GST_KntId
WHERE
B.GST_Datetime > CONVERT( '<xsp:expr>time</xsp:expr>', SQL_TIMESTAMP )
AND K.K_GRUPA = 'ODB'
ORDER BY
K.K_Nazwa1
</esql:query>
<esql:results>
<esql:row-results>
<row>
<esql:get-columns/>
</row>
</esql:row-results>
</esql:results>
</esql:execute-query>
I'm not using any row skipping or limit clauses and still the performance is awful.
My second thought is that esql asks the database too often for metadata
information.
Any ideas ? It's crucial for me to solve this ASAP
regards
LG
--
__
| / \ | Leszek Gawron // \\
\_\\ //_/ ouzo@vip.net.pl _\\()//_
.'/()\'. Phone: +48(600)341118 / // \\ \
\\ // recursive: adj; see recursive | \__/ |
Re: big ESQL performance problem
Posted by Leszek Gawron <ou...@vip.net.pl>.
On wto, mar 04, 2003 at 04:34:52 -0000, Upayavira wrote:
> > On wto, mar 04, 2003 at 03:21:27 +0100, Steven Noels wrote:
> > > Leszek Gawron wrote:
> > >
> > > ><esql:execute-query>
> > > ><esql:query>
> > > >SELECT
> > > > K.K_NIP AS Knt_NipE,
> > > > B.GST_KntID AS Knt_KntId,
> > > > B.GST_KntID AS Knt_Kod,
> > > > K.K_GRUPA AS Knt_Grupa,
> > >
>
> It sounds like people are doubting ESQL, so it is worth trying accessing your
> database JDBC with Java, without Cocoon in the picture. Then you can work out
> whether it is Cocoon or JDBC that is causing you a problem. I had to do this
> recently, so I used some code in the O'Reilly "Java Examples in a Nutshell" book.
Just as I said before. I'm using sourceforge JDBC Client named Squirell and
have no problem with retreieving large amounts of data
ouzo
--
__
| / \ | Leszek Gawron // \\
\_\\ //_/ ouzo@vip.net.pl _\\()//_
.'/()\'. Phone: +48(600)341118 / // \\ \
\\ // recursive: adj; see recursive | \__/ |
---------------------------------------------------------------------
To unsubscribe, e-mail: cocoon-users-unsubscribe@xml.apache.org
For additional commands, e-mail: cocoon-users-help@xml.apache.org
Re: big ESQL performance problem
Posted by Upayavira <uv...@upaya.co.uk>.
> On wto, mar 04, 2003 at 03:21:27 +0100, Steven Noels wrote:
> > Leszek Gawron wrote:
> >
> > ><esql:execute-query>
> > ><esql:query>
> > >SELECT
> > > K.K_NIP AS Knt_NipE,
> > > B.GST_KntID AS Knt_KntId,
> > > B.GST_KntID AS Knt_Kod,
> > > K.K_GRUPA AS Knt_Grupa,
> >
It sounds like people are doubting ESQL, so it is worth trying accessing your
database JDBC with Java, without Cocoon in the picture. Then you can work out
whether it is Cocoon or JDBC that is causing you a problem. I had to do this
recently, so I used some code in the O'Reilly "Java Examples in a Nutshell" book.
Regards, Upayavira
---------------------------------------------------------------------
To unsubscribe, e-mail: cocoon-users-unsubscribe@xml.apache.org
For additional commands, e-mail: cocoon-users-help@xml.apache.org
Re: big ESQL performance problem
Posted by Leszek Gawron <ou...@vip.net.pl>.
On wto, mar 04, 2003 at 03:21:27 +0100, Steven Noels wrote:
> Leszek Gawron wrote:
>
> ><esql:execute-query>
> ><esql:query>
> >SELECT
> > K.K_NIP AS Knt_NipE,
> > B.GST_KntID AS Knt_KntId,
> > B.GST_KntID AS Knt_Kod,
> > K.K_GRUPA AS Knt_Grupa,
>
> How does the rest of your pipeline looks like? Can you post the relevant
> sitemap snippet?
Most simple as I could get: match, generate type="serverpages", serialize to
xml
ouzo
--
__
| / \ | Leszek Gawron // \\
\_\\ //_/ ouzo@vip.net.pl _\\()//_
.'/()\'. Phone: +48(600)341118 / // \\ \
\\ // recursive: adj; see recursive | \__/ |
---------------------------------------------------------------------
To unsubscribe, e-mail: cocoon-users-unsubscribe@xml.apache.org
For additional commands, e-mail: cocoon-users-help@xml.apache.org
Re: big ESQL performance problem
Posted by Steven Noels <st...@outerthought.org>.
Leszek Gawron wrote:
> <esql:execute-query>
> <esql:query>
> SELECT
> K.K_NIP AS Knt_NipE,
> B.GST_KntID AS Knt_KntId,
> B.GST_KntID AS Knt_Kod,
> K.K_GRUPA AS Knt_Grupa,
How does the rest of your pipeline looks like? Can you post the relevant
sitemap snippet?
</Steven>
--
Steven Noels http://outerthought.org/
Outerthought - Open Source, Java & XML Competence Support Center
Read my weblog at http://blogs.cocoondev.org/stevenn/
stevenn at outerthought.org stevenn at apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: cocoon-users-unsubscribe@xml.apache.org
For additional commands, e-mail: cocoon-users-help@xml.apache.org
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
Re: big ESQL performance problem - this one's weird
Posted by Leszek Gawron <ou...@vip.net.pl>.
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
Posted by Christian Haul <ha...@dvs1.informatik.tu-darmstadt.de>.
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.
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
Posted by Torsten Curdt <tc...@dff.st>.
> The problem can be because there is no direct support into Cocoon for the
> JDBC for Pervasive. Try to write a specific helper class. PostgreSQL has
> one.
Yes.
Sounds like it's either the mode of the ResultSet and/or the JDBC driver
itself.
Sorry - but I really doubt it's ESQL. ESQL should be a very light
wrapper around straight JDBC. No heavy processing involved...
...but please report back!
> Pervasive. To be honest I heard about this Database first time in my life.
Me too :)
--
Torsten
Re: big ESQL performance problem
Posted by Antonio Gallardo <ag...@agsoftware.dnsalias.com>.
OK.I understand.
The problem can be because there is no direct support into Cocoon for the
JDBC for Pervasive. Try to write a specific helper class. PostgreSQL has
one.
For more info check into the sources into:
xml-cocoon2/src/blocks/databases/java/org/apache/cocoon/components/language/markup/xsp
There are some classes that can help you to write the correct one for
Pervasive. To be honest I heard about this Database first time in my life.
Best Regards,
Antonio Gallardo
Re: big ESQL performance problem
Posted by Leszek Gawron <ou...@vip.net.pl>.
On wto, mar 04, 2003 at 08:12:25 -0600, Antonio Gallardo wrote:
> Hi!
>
> Everybody try to fight against this. :-D
>
> Please note that ESQL is fast enough. It can build the 8,000 rowset in
> milisecs.
>
> Problem starts when the rowset go trought some XSL transformations and
> serialization.
>
> The worse if that after you render this to HTML. The client browser can
> spend more than this 3.5 to render all of this. I made some test and for
> 10,000 rows. It can take more than 5 mins on a Pentium IV 2.0 GHz with 256
> MB of RAM to render it after Cocoon sent back the HTML page! In a slower
> computer with less memory it can takes more than 15 mins!
>
> Please note that this is not a ESQL issue. This is just related to ESQL. I
> think if you can build a page with 8,000 rows without using ESQL you will
> get the same result.
>
> To workaround this issue you must "paginate" the responses instead of
> responde with a big bulk of data. I think people will not read all the
> 8,000 or more rows. :-D
Let me describe what I do:
1. The client is NOT a browser - it's an application on mobile device so data
amount is not a problem.
2. I'm not using cocoon for presenting data but for a unified xml frontend to
various databases (my application has to plug into many exsting database
systems)
3. I have no problem at all to fetch even more data from MS SQL Server or
Sybase. 5k rows gets processed by cocoon in 10 seconds which is fine for me
(with additional nodes -> attributes transformation!!).
4. The output file is about 1Mb big which is compressed by mod_gzip to about
7% of original size.
Summing up: cocoon is able to generate 1MB xml file with about 5k database
rows on PIII 800 MHz without any performance/memory tuning.
The only difference is while working with pervasive:
1. When using Squirell running
select * from table (5.5k rows, 30 columns) I get response:
Query 1 elapsed time (seconds) - Total: 7,381, SQL query: 0,07, Building
output: 7,311
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 ?
Or just plain stupid: There is something row with result set traversal (i.e.
fetching row x every time starts from skipping previous rows
I will have to run another test: Obtain a connection from pool and to it in
pure JDBC omitting esql. If that runs fast it's got to be ESQL
LG
--
__
| / \ | Leszek Gawron // \\
\_\\ //_/ ouzo@vip.net.pl _\\()//_
.'/()\'. Phone: +48(600)341118 / // \\ \
\\ // recursive: adj; see recursive | \__/ |
Re: big ESQL performance problem
Posted by Antonio Gallardo <ag...@agsoftware.dnsalias.com>.
Hi!
Everybody try to fight against this. :-D
Please note that ESQL is fast enough. It can build the 8,000 rowset in
milisecs.
Problem starts when the rowset go trought some XSL transformations and
serialization.
The worse if that after you render this to HTML. The client browser can
spend more than this 3.5 to render all of this. I made some test and for
10,000 rows. It can take more than 5 mins on a Pentium IV 2.0 GHz with 256
MB of RAM to render it after Cocoon sent back the HTML page! In a slower
computer with less memory it can takes more than 15 mins!
Please note that this is not a ESQL issue. This is just related to ESQL. I
think if you can build a page with 8,000 rows without using ESQL you will
get the same result.
To workaround this issue you must "paginate" the responses instead of
responde with a big bulk of data. I think people will not read all the
8,000 or more rows. :-D
To paginate the answers, use the tags: <esql:skip-rows> and <esql:max-rows>.
Best Regards,
Antonio Gallardo.
Re: big ESQL performance problem
Posted by Steven Noels <st...@outerthought.org>.
Leszek Gawron wrote:
> <esql:execute-query>
> <esql:query>
> SELECT
> K.K_NIP AS Knt_NipE,
> B.GST_KntID AS Knt_KntId,
> B.GST_KntID AS Knt_Kod,
> K.K_GRUPA AS Knt_Grupa,
How does the rest of your pipeline looks like? Can you post the relevant
sitemap snippet?
</Steven>
--
Steven Noels http://outerthought.org/
Outerthought - Open Source, Java & XML Competence Support Center
Read my weblog at http://blogs.cocoondev.org/stevenn/
stevenn at outerthought.org stevenn at apache.org
Re: big ESQL performance problem
Posted by Leszek Gawron <ou...@vip.net.pl>.
On wto, mar 04, 2003 at 09:18:29 -0500, Peter Royal wrote:
> On Tuesday, March 4, 2003, at 09:08 AM, Leszek Gawron wrote:
> >Things are not so simple for me because I'm plugging into an existing
> >system
> >(database change is impossible).
>
> We struggled with that for about two years before giving up (of course
> the existing system used the pervasive btrieve api with no
> database-level constraints and thus there was bad data all over the
> place..)
>
> >Second thing is that the same query that works reasonably fast under
> >Squirell
> >JBDC client is a nightmare under cocoon
>
> As it was mentioned in a reply on the dev list, try to show the user a
> subset of the records at a time.. 5500 rows will be pushing a lot of
> data through the cocoon pipelines and rendering engine of your browser.
> How large is the resulting HTML file?
Please see my detailed problem description on cocoon-dev list
ouzo
--
__
| / \ | Leszek Gawron // \\
\_\\ //_/ ouzo@vip.net.pl _\\()//_
.'/()\'. Phone: +48(600)341118 / // \\ \
\\ // recursive: adj; see recursive | \__/ |
---------------------------------------------------------------------
To unsubscribe, e-mail: cocoon-users-unsubscribe@xml.apache.org
For additional commands, e-mail: cocoon-users-help@xml.apache.org
Re: big ESQL performance problem
Posted by Peter Royal <pr...@apache.org>.
On Tuesday, March 4, 2003, at 09:08 AM, Leszek Gawron wrote:
> Things are not so simple for me because I'm plugging into an existing
> system
> (database change is impossible).
We struggled with that for about two years before giving up (of course
the existing system used the pervasive btrieve api with no
database-level constraints and thus there was bad data all over the
place..)
> Second thing is that the same query that works reasonably fast under
> Squirell
> JBDC client is a nightmare under cocoon
As it was mentioned in a reply on the dev list, try to show the user a
subset of the records at a time.. 5500 rows will be pushing a lot of
data through the cocoon pipelines and rendering engine of your browser.
How large is the resulting HTML file?
-pete
---------------------------------------------------------------------
To unsubscribe, e-mail: cocoon-users-unsubscribe@xml.apache.org
For additional commands, e-mail: cocoon-users-help@xml.apache.org
Re: big ESQL performance problem
Posted by Leszek Gawron <ou...@vip.net.pl>.
On wto, mar 04, 2003 at 09:05:44 -0500, Peter Royal wrote:
> On Tuesday, March 4, 2003, at 08:55 AM, Leszek Gawron wrote:
> >I've been fighting with this all day and cannot find a cause. I'm
> >using quite
> >fresh esql implementation to access Pervasive SQL 2000 database. The
> >problem
> >is I have to pull 5500 rows from database. Performing query in
> >Squirell SQL it
> >takes 3 second to execute and 16 to build output table (build squirell
> >view).
> >The same query executed under cocoon takes 3.5 MINUTES! I'm desperate.
> >Pervasive is a weird database (the SQL access is built on very low
> >lewel
> >core).
>
> We are in the process of switching from pervasive to postgresql for
> performance reasons. We have a 70k record table that takes *10 MINUTES*
> to do a "SELECT COUNT(col) FROM table WHERE col2 = X" when col2 is
> indexed. I have also noticed a very quick and steady decrease in
> performance doing "SELECT COUNT(col) FROM table", *no where clause*, as
> records are added.
Things are not so simple for me because I'm plugging into an existing system
(database change is impossible).
Second thing is that the same query that works reasonably fast under Squirell
JBDC client is a nightmare under cocoon
LG
--
__
| / \ | Leszek Gawron // \\
\_\\ //_/ ouzo@vip.net.pl _\\()//_
.'/()\'. Phone: +48(600)341118 / // \\ \
\\ // recursive: adj; see recursive | \__/ |
---------------------------------------------------------------------
To unsubscribe, e-mail: cocoon-users-unsubscribe@xml.apache.org
For additional commands, e-mail: cocoon-users-help@xml.apache.org
Re: big ESQL performance problem
Posted by Peter Royal <pr...@apache.org>.
On Tuesday, March 4, 2003, at 08:55 AM, Leszek Gawron wrote:
> I've been fighting with this all day and cannot find a cause. I'm
> using quite
> fresh esql implementation to access Pervasive SQL 2000 database. The
> problem
> is I have to pull 5500 rows from database. Performing query in
> Squirell SQL it
> takes 3 second to execute and 16 to build output table (build squirell
> view).
> The same query executed under cocoon takes 3.5 MINUTES! I'm desperate.
> Pervasive is a weird database (the SQL access is built on very low
> lewel
> core).
We are in the process of switching from pervasive to postgresql for
performance reasons. We have a 70k record table that takes *10 MINUTES*
to do a "SELECT COUNT(col) FROM table WHERE col2 = X" when col2 is
indexed. I have also noticed a very quick and steady decrease in
performance doing "SELECT COUNT(col) FROM table", *no where clause*, as
records are added.
My advice is to move from pervasive as fast as you can :)
-pete
---------------------------------------------------------------------
To unsubscribe, e-mail: cocoon-users-unsubscribe@xml.apache.org
For additional commands, e-mail: cocoon-users-help@xml.apache.org