X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 42C479DCAC1 for ; Thu, 22 Dec 2005 11:31:13 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 02681-05 for ; Thu, 22 Dec 2005 11:31:14 -0400 (AST) X-Greylist: delayed 00:06:40.281309 by SQLgrey- Received: from mail.zpfe.com (mail.zpfe.com [208.42.168.33]) by postgresql.org (Postfix) with SMTP id 85C7B9DC844 for ; Thu, 22 Dec 2005 11:31:06 -0400 (AST) Received: (qmail 7281 invoked from network); 22 Dec 2005 15:24:30 -0000 Received: from unknown (HELO revere.zpfe.com) (208.42.168.33) by mail.zpfe.com with SMTP; 22 Dec 2005 15:24:30 -0000 Message-Id: <6.2.3.4.0.20051222090735.05452aa8@localhost> X-Mailer: QUALCOMM Windows Eudora Version 6.2.3.4 Date: Thu, 22 Dec 2005 09:23:49 -0600 To: pgsql-performance@postgresql.org From: Steve Peterson Subject: Re: effizient query with jdbc In-Reply-To: <1380B56B-1419-4F4D-894B-979F075F18CF@fastcrypt.com> References: <200512151444.jBFEiNF25690@core.kontent.de> <1380B56B-1419-4F4D-894B-979F075F18CF@fastcrypt.com> Mime-Version: 1.0 Content-Type: text/plain; charset="0"; format=flowed X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200512/571 X-Sequence-Number: 16392 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