X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 11A8611F607B for ; Fri, 21 Apr 2006 01:10:21 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.357 X-Spam-Level: X-Spam-Status: No, score=0.357 required=5 tests=[AWL=0.356, HTML_MESSAGE=0.001] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id c8KOJlzie4P8 for ; Fri, 21 Apr 2006 01:10:14 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from stash.illumen.com (unknown [64.207.29.137]) by postgresql.org (Postfix) with ESMTP id 3459511F605B for ; Fri, 21 Apr 2006 01:10:14 -0300 (ADT) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C664F9.7C5A1ECD" Subject: Re: slow cursor X-MimeOLE: Produced By Microsoft Exchange V6.5 Date: Thu, 20 Apr 2006 22:10:12 -0600 Message-ID: <8511B4970E0D124898E973DF496F9B4357E0F6@stash.stackdump.local> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [ADMIN] slow cursor Thread-Index: AcZk7FRVGyTa6a6fQB6JFK2HksJpeQACYM8gAACKptg= References: <6992E470F12A444BB787B5C937B9D4DF0406AD88@ca-mail1.cis.local> From: "Benjamin Krajmalnik" To: "Sriram Dandapani" , "Jim C. Nasby" Cc: X-Archive-Number: 200604/217 X-Sequence-Number: 21386 This is a multi-part message in MIME format. ------_=_NextPart_001_01C664F9.7C5A1ECD Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Siriam, =20 I have no clue what it is you are trying to do, but I have a similar = situation. I have a monitoring system which sends data to our = psotgresql database. The system calls a single stored procedure which = performs on-the-fly data aggregation for the particular test for which = the data is being sent. You may want to look at some numerical methods = books and see if you can do what I am doing. I keep a record with = computed values representing the data up to the previous transaction, = and recompute accordingly. This is done estremely quickly. Then, twice = a day, I run a stored procedure which runs some other aggregations based = on the raw data which was sent over. It was taking about 30 seconds = against a table with about 500K records, aggregating to a table with 2K = records. Since not all of the tests need this particular data = aggregation, an additional field was added to the test descriptor to = flag those tests which need to have the particular aggregation = (essentially, computing statistical process control data). After doing = this, our 30 seconds went down to about 2 seconds. =20 I do not have access to our aggregation dunction, but I am using one = cursor and 2 record types. First, I perform a loop on a selection into the record structure for the = candidate tests. Within this loop, I create a cursor which fetches the = row data into another record structure. This record structure now holds = the data we need in order to update the table on which the primary loop = is running. =20 I do not know if this is the optimum way of doing this within = PostgreSQL, but the perofrmance appears to be fine. I have not goten = into the fine tuning for speed just yet. =20 Just an idea - hope it helps. ________________________________ From: pgsql-admin-owner@postgresql.org on behalf of Sriram Dandapani Sent: Thu 4/20/2006 9:45 PM To: Jim C. Nasby Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] slow cursor Yes..all of it is in one transaction as there is a window of record ids that need to be processed in 1 transaction. Data inflow is very voluminous appx 1 million every 15 minutes and the goal is to create aggregate tables on the fly (the alternative is to use nightly aggregates). -----Original Message----- From: Jim C. Nasby [mailto:jnasby@pervasive.com] Sent: Thursday, April 20, 2006 7:36 PM To: Sriram Dandapani Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] slow cursor On Mon, Apr 17, 2006 at 07:07:54AM -0700, Sriram Dandapani wrote: > I have a cursor that fetches 150K rows and updates or inserts a table > with 150K rows. > > It takes several minutes for the process to complete (about 15 minutes). > The select by itself (without cursor) gets all rows in 15 seconds. > > Is there a way to optimize the cursor to fetch all records and speed up > the process. I still need to do the record by record processing Not likely. Are you at least doing all this inside a transaction? -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org ------_=_NextPart_001_01C664F9.7C5A1ECD Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable =0A= =0A= =0A= =0A= =0A= =0A= Re: [ADMIN] slow cursor=0A= =0A= =0A=
=0A=
Siriam,
=0A=
 
=0A=
I have no clue what it is you = are trying to =0A= do, but I have a similar situation.  I have a monitoring system = which sends =0A= data to our psotgresql database.  The system calls a single stored =0A= procedure which performs on-the-fly data aggregation for the particular = test for =0A= which the data is being sent.  You may want to look at some = numerical =0A= methods books and see if you can do what I am doing.  I keep a = record with =0A= computed values representing the data up to the previous transaction, = and =0A= recompute accordingly.  This is done estremely quickly.  Then, = twice a =0A= day, I run a stored procedure which runs some other aggregations based = on the =0A= raw data which was sent over.  It was taking about 30 seconds = against a =0A= table with about 500K records, aggregating to a table with 2K = records.  =0A= Since not all of the tests need this particular data aggregation, an = additional =0A= field was added to the test descriptor to flag those tests which need to = have =0A= the particular aggregation (essentially, computing statistical process = control =0A= data).  After doing this, our 30 seconds went down to about 2 =0A= seconds.
=0A=
 
=0A=
I do not have access to our = aggregation =0A= dunction, but I am using one cursor and 2 record types.
=0A=
First, I perform a loop on a = selection into =0A= the record structure for the candidate tests.  Within this loop, I = create a =0A= cursor which fetches the row data into another record structure.  = This =0A= record structure now holds the data we need in order to update the table = on =0A= which the primary loop is running.
=0A=
 
=0A=
I do not know if this is the = optimum way of =0A= doing this within PostgreSQL, but the perofrmance appears to be  =0A= fine.  I have not goten into the fine tuning for speed just =0A= yet.
=0A=
 
=0A=
Just an idea - hope it =0A= helps.
=0A=

=0A=
=0A= From: = pgsql-admin-owner@postgresql.org on behalf =0A= of Sriram Dandapani
Sent: Thu 4/20/2006 9:45 PM
To: = Jim C. =0A= Nasby
Cc: pgsql-admin@postgresql.org
Subject: Re: = [ADMIN] =0A= slow cursor

=0A=
=0A=

Yes..all of it is in one transaction as there is a = window of =0A= record ids
that need to be processed in 1 transaction. Data inflow is =0A= very
voluminous appx 1 million every 15 minutes and the goal is to =0A= create
aggregate tables on the fly (the alternative is to use =0A= nightly
aggregates).

-----Original Message-----
From: Jim = C. Nasby =0A= [mailto:jnasby@pervasive.com]
= Sent: =0A= Thursday, April 20, 2006 7:36 PM
To: Sriram Dandapani
Cc: =0A= pgsql-admin@postgresql.org
Subject: Re: [ADMIN] slow cursor

On = Mon, =0A= Apr 17, 2006 at 07:07:54AM -0700, Sriram Dandapani wrote:
> I have = a =0A= cursor that fetches 150K rows and updates or inserts a table
> = with 150K =0A= rows.
>
> It takes several minutes for the process to = complete =0A= (about 15
minutes).
> The select by itself (without cursor) = gets all =0A= rows in 15 seconds.
>
> Is there a way to optimize the = cursor to =0A= fetch all records and speed
up
> the process. I still need to = do the =0A= record by record processing

Not likely. Are you at least doing = all this =0A= inside a transaction?
--
Jim C. Nasby, Sr. Engineering =0A= Consultant      = jnasby@pervasive.com
Pervasive =0A= Software      http://pervasive.com    = work: =0A= 512-231-6117
vcard: http://jim.nasby.net/pervasiv= e.vcf       =0A= cell: 512-569-9461

---------------------------(end of =0A= broadcast)---------------------------
TIP 4: Have you searched our = list =0A= archives?

         &n= bsp;     =0A= http://archives.postgresql.org

=0A= =0A= =0A= ------_=_NextPart_001_01C664F9.7C5A1ECD--