[SQL] Ask To Optimize Looping
Hi guys, can anyone help to optimize this loop : for i in select kodedivisi,kodeseksi,kodewip,nobatch,ket1,ket2,ket3,ket4,NILAIPROP as nilaiygdibagi from EDP040_07_23 order by kodedivisi,kodeseksi,kodewip,nobatch,ket1,ket2,ket3,ket4 loop jmlData2 = jmlData2 + 1; if (jmlData2 = 500) then jmlData3 = jmlData3 + jmlData2; jmlData2 = 0; Raise Notice '' 12 - 2 - EDP040_07_23 = %'', jmlData3; end if; Update EP_ES06_N_TEMP2 Set NilPakai = NilPakai + case when i.nilaiygdibagi > 0 then VNilaiPembagi else -1*VNilaiPembagi end Where (THBLTR,DIVCOD,WIPCOD,NBATCH,SKSFOH,MAINAC,SUB_AC,BAGCOD,SLGIDC,DSRTRF, JAMPAKAI,NILPAKAI,KodeHsl,NoBatchHsl) in (Select THBLTR,DIVCOD,WIPCOD,NBATCH,SKSFOH,MAINAC,SUB_AC,BAGCOD,SLGIDC,DSRTRF, JAMPAKAI,NILPAKAI,KodeHsl,NoBatchHsl From EP_ES06_N_TEMP2 Where DIVCOD = i.kodedivisi and WIPCOD = i.kodewip and NBATCH = i.nobatch and SKSFOH = i.kodeseksi and trim(MAINAC) = trim(i.ket1) and trim(SUB_AC) = trim(i.ket2) and trim(BAGCOD) = trim(i.ket3) and trim(SLGIDC) = trim(i.ket4) Order by thbltr desc,divcod desc,wipcod desc,nbatch desc, SKSFOH desc,mainac desc,sub_ac desc,bagcod desc,slgidc desc,dsrtrf desc, jampakai desc,nilpakai desc,kodehsl desc,nobatchhsl desc limit (abs(i.nilaiygdibagi) / VNilaiPembagi)); end loop; This loop need 30 minutes (18.000 record data), and the raise notice will show every 50 second (500 record data). Thanks anyway. -- --- "He who is quick to become angry will commit folly, and a crafty man is hated"
[SQL] Ask About SQL
Hi All. Can help to create sql queries for this data : tabel A field1, field2, field3 x1, y1, 5 x1, y2, 1 x2, y1, 2 x2, y3, 4 x1, y3, 4 I want to get 2 record with the max value at field3 for each kombination of field1 : tabel B field1, field2, field3 x1, y1, 5 x1, y3, 4 x2, y3, 4 x2, y1, 2 Anyone have an ideas? Thanks anyway. -- --- "He who is quick to become angry will commit folly, and a crafty man is hated"
Re: [SQL] Ask About SQL
In response to Otniel Michael : > Hi All. > > Can help to create sql queries for this data : > > tabel A > field1, field2, field3 > x1, y1, 5 > x1, y2, 1 > x2, y1, 2 > x2, y3, 4 > x1, y3, 4 > > I want to get 2 record with the max value at field3 for each kombination of > field1 : > > tabel B > field1, field2, field3 > x1, y1, 5 > x1, y3, 4 > x2, y3, 4 > x2, y1, 2 > > Anyone have an ideas? Works since 8.4: test=*# select * from table_a ; field1 | field2 | field3 ++ x1 | y1 | 5 x1 | y2 | 1 x2 | y1 | 2 x2 | y3 | 4 x1 | y3 | 4 (5 rows) test=*# select field1, field2, field3 from (select field1, field2, field3, row_number() over(partition by field1 order by field3 desc) from table_a order by field1, field3) foo where row_number < 3 order by field1, field2; field1 | field2 | field3 ++ x1 | y1 | 5 x1 | y3 | 4 x2 | y1 | 2 x2 | y3 | 4 (4 rows) Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Ask About SQL
On Wed, Aug 19, 2009 at 05:17:07PM +0700, Otniel Michael wrote: >Hi All. > >Can help to create sql queries for this data : > >tabel A >field1, field2, field3 >x1, y1, 5 >x1, y2, 1 >x2, y1, 2 >x2, y3, 4 >x1, y3, 4 > >I want to get 2 record with the max value at field3 for each kombination >of field1 : > >tabel B >field1, field2, field3 >x1, y1, 5 >x1, y3, 4 >x2, y3, 4 >x2, y1, 2 > >Anyone have an ideas? >Thanks anyway. > >-- >--- >"He who is quick to become angry will commit folly, and a crafty man is >hated" Your example doesn't match your description (the combination of x1 and y2 isn't listed). However, from your description it looks like what you want is DISTINCT ON http://www.postgresql.org/docs/8.4/interactive/sql-select.html#SQL-DISTINCT Something like this: SELECT DISTINCT ON (field1, field2) field1, field2, field3 FROM A ORDER BY field1, field2, field3; Here's an example. # select * from b order by f1, f2, f3; f1 | f2 | f3 ++ x1 | y1 | 5 x1 | y2 | 1 x1 | y2 | 3 x2 | y3 | 2 x2 | y3 | 4 (5 rows) # select distinct on (f1, f2) f1, f2, f3 from b order by 1, 2, 3 desc; f1 | f2 | f3 ++ x1 | y1 | 5 x1 | y2 | 3 x2 | y3 | 4 (3 rows) -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [SQL] Ask To Optimize Looping
Hello, I would try to replace the loop with a single UPDATE FROM Statement: Update EP_ES06_N_TEMP2 Set FROM ( select kodedivisi,kodeseksi,kodewip,nobatch,ket1,ket2,ket3,ket4,NILAIPROP as nilaiygdibagi from EDP040_07_23 --order by kodedivisi,kodeseksi,kodewip,nobatch,ket1,ket2,ket3,ket4 ) i WHERE .. Here a simple example for this syntax: create table test (i int); insert into test select * from generate_series (1,20); update test set i =0 from (select * from generate_series (1,10) s)i where test.i=i.s; --Query returned successfully: 10 rows affected But beware the limitation of update from: "When a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the fromlist, and each output row of the join represents an update operation for the target table. When using FROM you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable. Because of this indeterminacy, referencing other tables only within sub-selects is safer, though often harder to read and slower than using a join. " HTH, Marc Mamin
Re: [SQL] [PERFORM] SQL Query Performance - what gives?
The bitmask allows the setting of multiple permissions but the table definition doesn't have to change (well, so long as the bits fit into a word!) Finally, this is a message forum - the actual code itself is template-driven and the bitmask permission structure is ALL OVER the templates; getting that out of there would be a really nasty rewrite, not to mention breaking the user (non-developer, but owner) extensibility of the current structure. Is there a way to TELL the planner how to deal with this, even if it makes the SQL non-portable or is a hack on the source mandatory? You could use an integer array instead of a bit mask, make a gist index on it, and instead of doing "mask & xxx" do "array contains xxx", which is indexable with gist. The idea is that it can get much better row estimation. Instead of 1,2,3, you can use 1,2,4,8, etc if you like. you'd probably need a function to convert a bitmask into ints and another to do the conversion back, so the rest of your app gets the expected bitmasks. Or add a bitmask type to postgres with ptoper statistics... -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Multiple simultaneous queries on single connection
Hello Craig, list Is there a way to have two or more connections see each others uncommitted things? Not at present. You should be able to use explicitly declared cursors and FETCH to interleave requests for results from one or more queries in the same transation using the one connection, but only one FETCH may be active at a time. Thanks for your info. I verified with a pl/pgsql function that from a pl/pgsql perspective, this is a good solution. However, our client application needs to access the postgres backend with libpq. I'm currently looking at PQsendPrepare / PQSendQueryPrepared together with what the exec_execute_message does. Each of these has a portal name as parameter. It would be nice if PQgetResult could also provide a portal name to fetch a result from. The question is how to multiplex data from different portals on the tcp/ip connection, or maybe have more than one tcp/ip connection per PQconn object when more than one portal is active at the same time. -- Yeb -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Call Procedure From Trigger Function
I am in the process of coverting an Oracle 10.2.0.3 database to Postgresql 8.3. I have a number of triggers in Oracle that make a call to packages. I know I will need to re-write the Oracle packages to postgres functions. The issue which I have can I make a procedure call from inside a postgres trigger function? Thanks, Doug P.
Re: [SQL] Call Procedure From Trigger Function
On Wed, Aug 19, 2009 at 3:53 PM, Doug Pisarek wrote: > I am in the process of coverting an Oracle 10.2.0.3 database to Postgresql > 8.3. I have a number of triggers in Oracle that make a call to packages. > I know I will need to re-write the Oracle packages to postgres functions. > The issue which I have can I make a procedure call from inside a postgres > trigger function? Yep -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Call Procedure From Trigger Function
Yes you can make a procedure call in a trigger function.
Example
Trigger block
{
PERFORM Procedure();
}
Regards.
Ram
- Original Message -
From: "Scott Marlowe"
To: "Doug Pisarek"
Cc:
Sent: Thursday, August 20, 2009 9:31 AM
Subject: Re: [SQL] Call Procedure From Trigger Function
On Wed, Aug 19, 2009 at 3:53 PM, Doug Pisarek wrote:
I am in the process of coverting an Oracle 10.2.0.3 database to
Postgresql
8.3. I have a number of triggers in Oracle that make a call to packages.
I know I will need to re-write the Oracle packages to postgres functions.
The issue which I have can I make a procedure call from inside a postgres
trigger function?
Yep
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
