Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 5D28C9FB9A3 for ; Fri, 22 Jun 2007 06:37:47 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 12842-09 for ; Fri, 22 Jun 2007 06:37:38 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.239]) by postgresql.org (Postfix) with ESMTP id BDF5E9FB53C for ; Fri, 22 Jun 2007 06:37:43 -0300 (ADT) Received: by wx-out-0506.google.com with SMTP id s16so713286wxc for ; Fri, 22 Jun 2007 02:37:40 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=DEPb145oyM/EalQbWYlXf4pUv9rYTVxaI1D9nIG2eOEwaRVpVAil9YoTyP3fBlOjkW1vCrX1G+YjE3gROF/sfjrLFVXFdfNsCJY5EJpt6O+nBZXpGs8NJy5KUHqI4FHPatCeh7mHeHTAdA64MHuZki3O/8hAMfa7z2iyOA3c1sY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=gdCIJ1vqUfydR0G8go4SaLx0Ffv+Nve5RhAlKcj9ZlYSCPqioDxLnJ93zm0zcWFklC8FYl7DzNSkDEI+ZtiJA5U4Cq9wjN4wKPDJCmlzzPhjEzXTEu9F3+Q+ImT8OtpoV/lzrD5glHKWf8O0O0cQhPo/xcP2NHBPEFZ7OYwVXng= Received: by 10.70.84.6 with SMTP id h6mr4579074wxb.1182505060479; Fri, 22 Jun 2007 02:37:40 -0700 (PDT) Received: by 10.70.90.7 with HTTP; Fri, 22 Jun 2007 02:37:40 -0700 (PDT) Message-ID: <5482c80a0706220237k23ee1dd8h48160aa73250033e@mail.gmail.com> Date: Fri, 22 Jun 2007 11:37:40 +0200 From: Dimitri To: "Rainer Bauer" Subject: Re: Data transfer very slow when connected via DSL Cc: pgsql-performance@postgresql.org In-Reply-To: <9q3n73hcv5ekhl97spfj9bp6erq80j0241@4ax.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <16953.1182439969@sss.pgh.pa.us> <5482c80a0706211432g170154e7x4ee36e5148d272d5@mail.gmail.com> <5482c80a0706211538ka531c18gcf681cd5a68066b2@mail.gmail.com> <9q3n73hcv5ekhl97spfj9bp6erq80j0241@4ax.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/517 X-Sequence-Number: 25550 Rainer, but did you try initial query with FETCH_COUNT equal to 100?... Rgds, -Dimitri On 6/22/07, Rainer Bauer wrote: > Hello Dimitri, > > >Let's stay optimist - at least now you know the main source of your > problem! :)) > > > >Let's see now with CURSOR... > > > >Firstly try this: > >munnin=>\timing > >munnin=>\set FETCH_COUNT 1; > >munnin=>select * from "tblItem"; > > > >what's the time you see here? (I think your application is working in > >this manner) > > That's it! It takes exactly 8 seconds like my program. > > I retrieve the data through a bound column: > SELECT * FROM tblItem WHERE intItemIDCnt = ? > > After converting this to > SELECT * FROM tblItem WHERE intItemIDCnt IN (...) > the query is as fast as psql: 409ms > > So the problem is identified and the solution is to recode my application. > > Rainer > > PS: When enabling UseDeclareFetch as suggested by Tom then the runtime is > still three times slower: 1192ms. But I guess that problem is for the ODBC > list. > > ---------------------------(end of broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate >