Re: [PERFORM] effizient query with jdbc
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 (1) 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 1 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
>
>
>
>
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [PERFORM] effizient query with jdbc
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 1 rows with 1 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, [EMAIL PROTECTED] wrote:
Hi,
I have a java.util.List of values (1) 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 1 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
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [PERFORM] effizient query with jdbc
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, [EMAIL PROTECTED] wrote:
Hi,
I have a java.util.List of values (1) 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 1 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
[PERFORM] effizient query with jdbc
Hi,
I have a java.util.List of values (1) 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 1 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
[PERFORM] effizient query with jdbc
Hi,
I have a java.util.List of values (1) 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 1 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 4: Have you searched our list archives?
http://archives.postgresql.org
[PERFORM] effizient query with jdbc
Hi,
I have a java.util.List of values (1) 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 1 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
[PERFORM] effizient query with jdbc
Hi,
I have a java.util.List of values (1) 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 1 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 2: Don't 'kill -9' the postmaster
