pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedeffizient query with jdbc
7+ messages / 6 participants
[nested] [flat]
* effizient query with jdbc
@ 2005-12-14 21:28 Bühler, Johannes <jbuehler@geotask.ch>
0 siblings, 0 replies; 7+ messages in thread
From: Bühler, Johannes @ 2005-12-14 21:28 UTC (permalink / raw)
To: pgsql-performance
Hi,
I have a java.util.List of values (10000) which i wanted to use for a query in the where clause of an simple select statement. iterating over the list and and use an prepared Statement is quite slow. Is there a more efficient way to execute such a query.
Thanks for any help.
Johannes
.....
List ids = new ArrayList();
.... List is filled with 10000 values ...
List uuids = new ArrayList();
PreparedStatement pstat = db.prepareStatement("SELECT UUID FROM MDM.KEYWORDS_INFO WHERE KEYWORDS_ID = ?");
for (Iterator iter = ids.iterator(); iter.hasNext();) {
String id = (String) iter.next();
pstat.setString(1, id);
rs = pstat.executeQuery();
if (rs.next()) {
uuids.add(rs.getString(1));
}
rs.close();
}
...
^ permalink raw reply [nested|flat] 7+ messages in thread
* effizient query with jdbc
@ 2005-12-15 06:22 Bühler, Johannes <jbuehler@geotask.ch>
0 siblings, 0 replies; 7+ messages in thread
From: Bühler, Johannes @ 2005-12-15 06:22 UTC (permalink / raw)
To: pgsql-performance
Hi,
I have a java.util.List of values (10000) which i wanted to use for a query in the where clause of an simple select statement. iterating over the list and and use an prepared Statement is quite slow. Is there a more efficient way to execute such a query.
Thanks for any help.
Johannes
.....
List ids = new ArrayList();
.... List is filled with 10000 values ...
List uuids = new ArrayList();
PreparedStatement pstat = db.prepareStatement("SELECT UUID FROM MDM.KEYWORDS_INFO WHERE KEYWORDS_ID = ?");
for (Iterator iter = ids.iterator(); iter.hasNext();) {
String id = (String) iter.next();
pstat.setString(1, id);
rs = pstat.executeQuery();
if (rs.next()) {
uuids.add(rs.getString(1));
}
rs.close();
}
...
^ permalink raw reply [nested|flat] 7+ messages in thread
* effizient query with jdbc
@ 2005-12-15 07:07 Johannes Bühler <jbuehler@geotask.ch>
2006-01-06 14:03 ` Re: effizient query with jdbc Sebastian Hennebrueder <usenet@laliluna.de>
0 siblings, 1 reply; 7+ messages in thread
From: Johannes Bühler @ 2005-12-15 07:07 UTC (permalink / raw)
Hi,
I have a java.util.List of values (10000) which i wanted to use for a
query in the where clause of an simple select statement. iterating
over the list and and use an prepared Statement is quite slow. Is
there a more efficient way to execute such a query. Thanks for any
help. Johannes
.....
List ids = new ArrayList();
.... List is filled with 10000 values ...
List uuids = new ArrayList();
PreparedStatement pstat = db.prepareStatement("SELECT UUID FROM
MDM.KEYWORDS_INFO WHERE KEYWORDS_ID = ?"); for (Iterator iter =
ids.iterator(); iter.hasNext();) { String id = (String) iter.next();
pstat.setString(1, id);
rs = pstat.executeQuery();
if (rs.next()) {
uuids.add(rs.getString(1));
}
rs.close();
}
...
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: effizient query with jdbc
2005-12-15 07:07 effizient query with jdbc Johannes Bühler <jbuehler@geotask.ch>
@ 2006-01-06 14:03 ` Sebastian Hennebrueder <usenet@laliluna.de>
0 siblings, 0 replies; 7+ messages in thread
From: Sebastian Hennebrueder @ 2006-01-06 14:03 UTC (permalink / raw)
To: pgsql-performance
You could issue one query containing a
select uuid FROM MDM.KEYWORDS_INFO WHERE KEYWORDS_ID in (xy)
where xy is a large comma separated list of your values.
Best Regards / Viele Grüße
Sebastian Hennebrueder
-----
http://www.laliluna.de
* Tutorials for JSP, JavaServer Faces, Struts, Hibernate and EJB
* Seminars and Education at reasonable prices
* Get professional support and consulting for these technologies
Johannes Bühler schrieb:
> Hi,
> I have a java.util.List of values (10000) which i wanted to use for a
> query in the where clause of an simple select statement. iterating
> over the list and and use an prepared Statement is quite slow. Is
> there a more efficient way to execute such a query. Thanks for any
> help. Johannes
> .....
> List ids = new ArrayList();
>
> .... List is filled with 10000 values ...
>
> List uuids = new ArrayList();
> PreparedStatement pstat = db.prepareStatement("SELECT UUID FROM
> MDM.KEYWORDS_INFO WHERE KEYWORDS_ID = ?"); for (Iterator iter =
> ids.iterator(); iter.hasNext();) { String id = (String) iter.next();
> pstat.setString(1, id);
> rs = pstat.executeQuery();
> if (rs.next()) {
> uuids.add(rs.getString(1));
> }
> rs.close();
> }
> ...
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* effizient query with jdbc
@ 2005-12-15 14:44 johannesbuehler@oderbruecke.de
2005-12-22 14:17 ` Re: effizient query with jdbc Dave Cramer <pg@fastcrypt.com>
0 siblings, 1 reply; 7+ messages in thread
From: johannesbuehler@oderbruecke.de @ 2005-12-15 14:44 UTC (permalink / raw)
To: pgsql-performance
Hi,
I have a java.util.List of values (10000) which i wanted to use for a query in the where clause of an simple select statement. iterating over the list and and use an prepared Statement is quite slow. Is there a more efficient way to execute such a query.
Thanks for any help.
Johannes
.....
List ids = new ArrayList();
.... List is filled with 10000 values ...
List uuids = new ArrayList();
PreparedStatement pstat = db.prepareStatement("SELECT UUID FROM MDM.KEYWORDS_INFO WHERE KEYWORDS_ID = ?");
for (Iterator iter = ids.iterator(); iter.hasNext();) {
String id = (String) iter.next();
pstat.setString(1, id);
rs = pstat.executeQuery();
if (rs.next()) {
uuids.add(rs.getString(1));
}
rs.close();
}
...
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: effizient query with jdbc
2005-12-15 14:44 effizient query with jdbc johannesbuehler@oderbruecke.de
@ 2005-12-22 14:17 ` Dave Cramer <pg@fastcrypt.com>
2005-12-22 15:23 ` Re: effizient query with jdbc Steve Peterson <stevep-hv@zpfe.com>
0 siblings, 1 reply; 7+ messages in thread
From: Dave Cramer @ 2005-12-22 14:17 UTC (permalink / raw)
To: johannesbuehler@oderbruecke.de; +Cc: pgsql-performance
The problem is you are getting the entire list back at once.
You may want to try using a cursor.
Dave
On 15-Dec-05, at 9:44 AM, johannesbuehler@oderbruecke.de wrote:
> Hi,
> I have a java.util.List of values (10000) which i wanted to use for
> a query in the where clause of an simple select statement.
> iterating over the list and and use an prepared Statement is quite
> slow. Is there a more efficient way to execute such a query.
>
> Thanks for any help.
> Johannes
> .....
> List ids = new ArrayList();
>
> .... List is filled with 10000 values ...
>
> List uuids = new ArrayList();
> PreparedStatement pstat = db.prepareStatement("SELECT UUID FROM
> MDM.KEYWORDS_INFO WHERE KEYWORDS_ID = ?");
> for (Iterator iter = ids.iterator(); iter.hasNext();) {
> String id = (String) iter.next();
> pstat.setString(1, id);
> rs = pstat.executeQuery();
> if (rs.next()) {
> uuids.add(rs.getString(1));
> }
> rs.close();
> }
> ...
>
>
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: effizient query with jdbc
2005-12-15 14:44 effizient query with jdbc johannesbuehler@oderbruecke.de
2005-12-22 14:17 ` Re: effizient query with jdbc Dave Cramer <pg@fastcrypt.com>
@ 2005-12-22 15:23 ` Steve Peterson <stevep-hv@zpfe.com>
0 siblings, 0 replies; 7+ messages in thread
From: Steve Peterson @ 2005-12-22 15:23 UTC (permalink / raw)
To: pgsql-performance
Is there a reason you can't rewrite your SELECT like:
SELECT UUID FROM MDM.KEYWORDS_INFO WHERE KEYWORDS_ID IN (a, b, c, d)
Even doing them 100 at a time will make a big difference; you should
put as many in the list as pgsql supports. I'm assuming that there's
an index over KEYWORDS_ID.
Retrieving 10000 rows with 10000 statements is generally a Bad Idea.
S
At 08:17 AM 12/22/2005, Dave Cramer wrote:
>The problem is you are getting the entire list back at once.
>
>You may want to try using a cursor.
>
>Dave
>On 15-Dec-05, at 9:44 AM, johannesbuehler@oderbruecke.de wrote:
>
>>Hi,
>>I have a java.util.List of values (10000) which i wanted to use for
>>a query in the where clause of an simple select statement.
>>iterating over the list and and use an prepared Statement is quite
>>slow. Is there a more efficient way to execute such a query.
>>
>>Thanks for any help.
>>Johannes
>>.....
>>List ids = new ArrayList();
>>
>>.... List is filled with 10000 values ...
>>
>>List uuids = new ArrayList();
>>PreparedStatement pstat = db.prepareStatement("SELECT UUID FROM
>>MDM.KEYWORDS_INFO WHERE KEYWORDS_ID = ?");
>>for (Iterator iter = ids.iterator(); iter.hasNext();) {
>>String id = (String) iter.next();
>>pstat.setString(1, id);
>>rs = pstat.executeQuery();
>>if (rs.next()) {
>>uuids.add(rs.getString(1));
>>}
>>rs.close();
>>}
>>...
>>
>>
>>
>>
>>
>>
>>---------------------------(end of
>>broadcast)---------------------------
>>TIP 6: explain analyze is your friend
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2006-01-06 14:03 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-14 21:28 effizient query with jdbc Bühler, Johannes <jbuehler@geotask.ch>
2005-12-15 06:22 effizient query with jdbc Bühler, Johannes <jbuehler@geotask.ch>
2005-12-15 07:07 effizient query with jdbc Johannes Bühler <jbuehler@geotask.ch>
2006-01-06 14:03 ` Sebastian Hennebrueder <usenet@laliluna.de>
2005-12-15 14:44 effizient query with jdbc johannesbuehler@oderbruecke.de
2005-12-22 14:17 ` Dave Cramer <pg@fastcrypt.com>
2005-12-22 15:23 ` Steve Peterson <stevep-hv@zpfe.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox