Обсуждение: Performance problem iterating a resultset

Hi Riyaz,
One option would be to write a stored function and invoke it from your jsp
page. This way you dont have
to do the resultset iteration in the client.

Thanks,
Nagarajan.

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Riyaz@umapinc.com
Sent: Friday, October 18, 2002 4:18 PM
To: pgsql-jdbc@postgresql.org; pgsql-general@postgresql.org
Subject: [JDBC] Performance problem iterating a resultset



Hello,
      I am using JDBC driver for Postgresql to retrieve results from a JSP
page, iterating through the resultset to extract the results and
concatenating it to a string. Everthing is working except for that while I
am iterating through the resultset to generate a string its taking a good
amount of time. Is there a way in Postgresql where I can export the
resultset directly to a xml file?(The data I am receiving from the database
are xml nodes)

Or, Is there a faster way I can retrieve results from the resultset?



Thanks,
-Riyaz

What else are you doing in the jsp, can we see the code?

Dave
On Fri, 2002-10-18 at 10:17, Riyaz@umapinc.com wrote:
> Hello,
>       I am using JDBC driver for Postgresql to retrieve results from a JSP
> page, iterating through the resultset to extract the results and
> concatenating it to a string. Everthing is working except for that while I
> am iterating through the resultset to generate a string its taking a good
> amount of time. Is there a way in Postgresql where I can export the
> resultset directly to a xml file?(The data I am receiving from the
> database are xml nodes)
>
> Or, Is there a faster way I can retrieve results from the resultset?
>
>
>
> Thanks,
> -Riyaz

Can someone confirm for me that the back end doesn't support parameters in
the order by clause for jdbc prepared statements?  I error out through JDBC
and through pgadmin when attempting to create one:

this works:
PREPARE JDBC_STATEMENT_100(text) AS
SELECT partid from parts ORDER BY partid;


this errors out with "parse error at or near ";" at character 72":
PREPARE JDBC_STATEMENT_100(text) AS
SELECT partid from parts ORDER BY ?;


this errors out with "Non-integer constant in ORDER BY":
PREPARE JDBC_STATEMENT_100(text) AS
SELECT partid from parts ORDER BY '?';

Anyone shed some light on this for me?

Hi,

While testing PostgreSQL JDBC I have noticed that it uses byte[]->String
conversion everywhere via 'new String(byte[], encoding)' constructor. I
found the implementation of that constructor painfully slow at least in
Sun's JDK 1.3.1 and 1.4.0, a bit better in gcj 3.2.  I have made a patch
that uses my own UTF-8 Unicode byte[]->String constructor and it speeds
up iterating a resultset ~1.5-2x on Sun's JDK.

Please confirm if anyone has similar experience. I will post the patch
to this mailing list if you are interested in it. Too bad it only works
with Unicode databases, and uses the default String constructor for other
character codings.

Teofilis Martisius,
teo@teohome.lzua.lt

Teofilis,

I have spent some time looking at your patch and trying it on different
JVMs.  I was amazed how poorly the sun JVM handles this case.  In fact I
saw performance differences of as much as 40X between the your code and
the sun code.  (I think that was on a 1.2 jvm).

So I am inclined to include this patch.  The reason I haven't yet, is
that I am investigating setting the 'client_encoding' parameter on the
server which would cause the server to encode everything to/from UTF8 so
that the client could always use this faster code.  This would only be
possible if using a 7.3 server.  Earlier releases didn't always have
support for this built in since it was a compile time parameter.  But my
understanding is that in 7.3 the server always has this capability and
it is no longer a compile time option.

thanks,
--Barry

Teofilis Martisius wrote:
> Hi,
>
> While testing PostgreSQL JDBC I have noticed that it uses byte[]->String
> conversion everywhere via 'new String(byte[], encoding)' constructor. I
> found the implementation of that constructor painfully slow at least in
> Sun's JDK 1.3.1 and 1.4.0, a bit better in gcj 3.2.  I have made a patch
> that uses my own UTF-8 Unicode byte[]->String constructor and it speeds
> up iterating a resultset ~1.5-2x on Sun's JDK.
>
> Please confirm if anyone has similar experience. I will post the patch
> to this mailing list if you are interested in it. Too bad it only works
> with Unicode databases, and uses the default String constructor for other
> character codings.
>
> Teofilis Martisius,
> teo@teohome.lzua.lt
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>