Thread: Convert Cursor to array
Hi,
In a procedure, I put data of Cursor in a two-dimensional array. This allows me to sort rows.
The problem is this method is too slow.
In fact, I translate a pl/sql procedure to a plpgsql.
Under Oracle, we use bulk and I search to use equivalent of this under postgresql.
Is that exist ?
Oracle procedure
cursor childCursor is select * from CHILD WHERE......
....
open childCursor;
fetch childCursor bulk collect into children;
close childCursor;
Postgresql procedure :
FOR childRecord IN select * from nico.CHILD WHERE...
LOOP
-- on met les éléments dans le tableau
children[recordcount] := '{'
|| childRecord.child_id || ','
|| childRecord.evolution || ','
|| childRecord.isremoved || ','
|| childRecord.child_class || ','
|| childRecord.indx || ','
|| childRecord.ele_id || ','
|| childRecord.doc_id ||
'}';
recordcount := recordcount + 1;
END LOOP;
Bulk are native Oracle array and it is probably faster than array.
Is there native method postgresql to replace bulk ?
Best regards.
GIROIRE Nicolas (COFRAMI) wrote: > Hi, > > In a procedure, I put data of Cursor in a two-dimensional array. This allows > me to sort rows. > > The problem is this method is too slow. > > In fact, I translate a pl/sql procedure to a plpgsql. > Under Oracle, we use bulk and I search to use equivalent of this under > postgresql. > > Is that exist ? No, but there might be other ways to do this. Can you explain why you need to sort within the function rather than using "order by"? If you're sure you want to sort within the function, take a look at one of the alternative procedural languages. PL/pgsql evaluates all expressions by passing them to the SQL parser, which keeps things simple and consistent but isn't efficient if you want to do a lot of data processing. -- Richard Huxton Archonet Ltd
my sort algorithm is a very particular solution.
In fact, I put record in array to allow me to sort record. I sort them by using one column of my table but the column indx can have duplicate value and it's volontary.
here is the order algorithm :
for i in 1..recordcount loop
tmp_row := children[i];
indx := tmp_row[5];
if (indx<i) then
-- on déplace les éléments pour laisser la place à l'élément qu'on déplace
for j in 0..(i-indx-1) loop
children[i-j] := children[i-j-1];
end loop;
-- on met l'élément à sa nouvelle position
children[indx] := tmp_row;
end if;
end loop;
It's particular to my system. I won't go to explain my choice but if someone has an idea to save time.
-----Message d'origine-----
De : Richard Huxton [mailto:dev@archonet.com]
Envoyé : vendredi 11 mars 2005 15:01
À : GIROIRE Nicolas (COFRAMI)
Cc : 'pgsql-general@postgresql.org'
Objet : Re: [GENERAL] Convert Cursor to array
GIROIRE Nicolas (COFRAMI) wrote:
> Hi,
>
> In a procedure, I put data of Cursor in a two-dimensional array. This allows
> me to sort rows.
>
> The problem is this method is too slow.
>
> In fact, I translate a pl/sql procedure to a plpgsql.
> Under Oracle, we use bulk and I search to use equivalent of this under
> postgresql.
>
> Is that exist ?
No, but there might be other ways to do this. Can you explain why you
need to sort within the function rather than using "order by"?
If you're sure you want to sort within the function, take a look at one
of the alternative procedural languages. PL/pgsql evaluates all
expressions by passing them to the SQL parser, which keeps things simple
and consistent but isn't efficient if you want to do a lot of data
processing.
--
Richard Huxton
Archonet Ltd
This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.
GIROIRE Nicolas (COFRAMI) wrote: > my sort algorithm is a very particular solution. > In fact, I put record in array to allow me to sort record. I sort them by > using one column of my table but the column indx can have duplicate value > and it's volontary. I still can't see why you can't sort by that column with "order by" There's nothing obvious in your description that rules that out. Are you aware that PostgreSQL offers functional and partial indexes? They can make complex selection/ordering quite efficient. > here is the order algorithm : > for i in 1..recordcount loop > tmp_row := children[i]; > indx := tmp_row[5]; > > if (indx<i) then > -- on déplace les éléments pour laisser la > place à l'élément qu'on déplace > for j in 0..(i-indx-1) loop > children[i-j] := children[i-j-1]; > end loop; > -- on met l'élément à sa nouvelle position > children[indx] := tmp_row; > end if; > end loop; > > It's particular to my system. I won't go to explain my choice but if someone > has an idea to save time. If you are going to sort, this looks a particularly slow algorithm for large values of "recordcount". Personally, I'd use plperl/plpython or something with built-in hash sorting capabilities. That will almost certainly be tens or hundreds of times faster. -- Richard Huxton Archonet Ltd
hi
i'm a friend of nicolas and i'm working with him on this project
so this is the problem :
we are working on an application allowing to manage documentation in xml format.
At the the first level, there is the root, and at the next level there are the childs (example : chapters).
the application allows to make evolve docs (by adding chapters for example).
so, if we consider 2 chapters and that we want to add one between them, we need to have an index.
the first idea was to re-number all the index chapter when 1 was added, but it was too slow.
the second idea was to use float index. if we consider chapter 1 with index 1.0 and chapter 2 with index 2.0, and that we want to add new one between them, we set the new index at 1.5 but performances wasn't good.
so we choice to use a different solution which consist on using the index of a chapter and its evolution.
if we have this data :
chapter_id | evolution | index
1 | 0 | 1
2 | 0 | 2
3 | 0 | 3
4 | 1 | 2
by using our sort function we obtain this :
chapter_id | evolution | index
1 | 0 | 1
4 | 1 | 2
2 | 0 | 2
3 | 0 | 3
in consequence a new chapter has been added between two others.
this solution works fine under oracle, but under postgresql, performance are bad and we are working on the tunning of the DB.
although this solution works, we don't find it very elegant and we think that we can find a better one.
the principal problem is how to add leaf between two others whitout modifing too much data.
the size of the documents are between 200MB and 800MB;
By hoping to have been clear.
-----Message d'origine-----
De : pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]De la part de Richard Huxton
Envoyé : lundi 14 mars 2005 09:31
À : GIROIRE Nicolas (COFRAMI)
Cc : 'pgsql-general@postgresql.org'
Objet : Re: [GENERAL] Convert Cursor to array
GIROIRE Nicolas (COFRAMI) wrote:
> my sort algorithm is a very particular solution.
> In fact, I put record in array to allow me to sort record. I sort them by
> using one column of my table but the column indx can have duplicate value
> and it's volontary.
I still can't see why you can't sort by that column with "order by"
There's nothing obvious in your description that rules that out. Are you
aware that PostgreSQL offers functional and partial indexes? They can
make complex selection/ordering quite efficient.
> here is the order algorithm :
> for i in 1..recordcount loop
> tmp_row := children[i];
> indx := tmp_row[5];
>
> if (indx<i) then
> -- on déplace les éléments pour laisser la
> place à l'élément qu'on déplace
> for j in 0..(i-indx-1) loop
> children[i-j] := children[i-j-1];
> end loop;
> -- on met l'élément à sa nouvelle position
> children[indx] := tmp_row;
> end if;
> end loop;
>
> It's particular to my system. I won't go to explain my choice but if someone
> has an idea to save time.
If you are going to sort, this looks a particularly slow algorithm for
large values of "recordcount". Personally, I'd use plperl/plpython or
something with built-in hash sorting capabilities. That will almost
certainly be tens or hundreds of times faster.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.
On Mon, 2005-03-14 at 10:44 +0100, FERREIRA William (COFRAMI) wrote: > so we choice to use a different solution which consist on using the > index of a chapter and its evolution. > if we have this data : > chapter_id | evolution | index > 1 | 0 | 1 > 2 | 0 | 2 > 3 | 0 | 3 > 4 | 1 | 2 > > by using our sort function we obtain this : > chapter_id | evolution | index > 1 | 0 | 1 > 4 | 1 | 2 > 2 | 0 | 2 > 3 | 0 | 3 > in what way is this different than ... ORDER BY index ASC, evolution DESC; ? gnari
well my example was incomplete :
at the begin :
chapter_id | evolution | index
1 | 0 | 1
2 | 0 | 2
3 | 0 | 3
4 | 1 | 2
5 | 1 | 4
by using the sort function i obtain this :
chapter_id | evolution | index
1 | 0 | 1
4 | 1 | 2
2 | 0 | 2
5 | 1 | 4
3 | 0 | 3
that why i can't use ORDER BY...
-----Message d'origine-----
De : Ragnar Hafstað [mailto:gnari@simnet.is]
Envoyé : lundi 14 mars 2005 12:11
À : FERREIRA William (COFRAMI)
Cc : 'Richard Huxton'; GIROIRE Nicolas (COFRAMI);
'pgsql-general@postgresql.org'
Objet : Re: [GENERAL] Convert Cursor to array
On Mon, 2005-03-14 at 10:44 +0100, FERREIRA William (COFRAMI) wrote:
> so we choice to use a different solution which consist on using the
> index of a chapter and its evolution.
> if we have this data :
> chapter_id | evolution | index
> 1 | 0 | 1
> 2 | 0 | 2
> 3 | 0 | 3
> 4 | 1 | 2
>
> by using our sort function we obtain this :
> chapter_id | evolution | index
> 1 | 0 | 1
> 4 | 1 | 2
> 2 | 0 | 2
> 3 | 0 | 3
>
in what way is this different than
... ORDER BY index ASC, evolution DESC;
?
gnari
This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.
ok, i think that i will try to use pl/perl or pl/python
but i don't know which one is better for my problem
maybe you can help me.
thanks
-----Message d'origine-----
De : pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]De la part de Richard Huxton
Envoyé : lundi 14 mars 2005 09:31
À : GIROIRE Nicolas (COFRAMI)
Cc : 'pgsql-general@postgresql.org'
Objet : Re: [GENERAL] Convert Cursor to array
GIROIRE Nicolas (COFRAMI) wrote:
> my sort algorithm is a very particular solution.
> In fact, I put record in array to allow me to sort record. I sort them by
> using one column of my table but the column indx can have duplicate value
> and it's volontary.
I still can't see why you can't sort by that column with "order by"
There's nothing obvious in your description that rules that out. Are you
aware that PostgreSQL offers functional and partial indexes? They can
make complex selection/ordering quite efficient.
> here is the order algorithm :
> for i in 1..recordcount loop
> tmp_row := children[i];
> indx := tmp_row[5];
>
> if (indx<i) then
> -- on déplace les éléments pour laisser la
> place à l'élément qu'on déplace
> for j in 0..(i-indx-1) loop
> children[i-j] := children[i-j-1];
> end loop;
> -- on met l'élément à sa nouvelle position
> children[indx] := tmp_row;
> end if;
> end loop;
>
> It's particular to my system. I won't go to explain my choice but if someone
> has an idea to save time.
If you are going to sort, this looks a particularly slow algorithm for
large values of "recordcount". Personally, I'd use plperl/plpython or
something with built-in hash sorting capabilities. That will almost
certainly be tens or hundreds of times faster.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.
FERREIRA William (COFRAMI) wrote: > ok, i think that i will try to use pl/perl or pl/python > but i don't know which one is better for my problem Whichever you know better is the best solution. -- Richard Huxton Archonet Ltd