From: Steve Peterson <stevep-hv@zpfe.com>
To: pgsql-performance@postgresql.org
Subject: Re: effizient query with jdbc
Date: Thu, 22 Dec 2005 09:23:49 -0600
Message-ID: <6.2.3.4.0.20051222090735.05452aa8@localhost> (raw)
In-Reply-To: <1380B56B-1419-4F4D-894B-979F075F18CF@fastcrypt.com>
References: <200512151444.jBFEiNF25690@core.kontent.de>
<1380B56B-1419-4F4D-894B-979F075F18CF@fastcrypt.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: stevep-hv@zpfe.com
Subject: Re: effizient query with jdbc
In-Reply-To: <6.2.3.4.0.20051222090735.05452aa8@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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