Postgres vs Firebird... hmmm

jurhas · #1 (**permalink**) 11-14-12, 16:37

Hallo, I am NEW... I am interested to the functionality of PostgreSQL.. but the beginn text has failed... so I think I am guilty...
The Text is very easy calculate the first 10,000 prime number... result around 1 second for Firebird(Flamerobin GUI), more then 13 s for PostgreSQL on pgAdmin III. The table has just a column "Numeri" without keys.
This is the code of PostgreSQL :

Code:

-- Function: "NumeriPrimi"(integer)

-- DROP FUNCTION "NumeriPrimi"(integer);

CREATE OR REPLACE FUNCTION "NumeriPrimi"("Qty" integer)
  RETURNS bigint AS
$BODY$
declare MaxVal bigint;
declare curValue bigint; 
declare buf bigint; 
declare Iterazioni int; 
DECLARE  cur1 SCROLL cursor for (Select "Numeri" from "Num");

BEGIN

	open cur1;
	fetch last from cur1 into buf;
	if buf is Null then
		 insert into "Num" values (2);
		insert into "Num" values (3);
		close cur1; -- close and open because i do not find a way to update cursor
		open cur1;
		curValue=3;
	else
		curValue =buf;
	end if;
	Iterazioni=0;
	while (iterazioni <"Qty") loop
		
	    curvalue=curvalue+2;
		   fetch last from cur1 into buf;
		if buf*buf< curvalue then
			close cur1;-- close and open for update cur1..
			open cur1;
		end if; 
		fetch first from cur1 into buf;
		while NOT (curvalue%buf=0)loop   
			if buf*buf> curvalue or buf is Null then
				insert into "Num" values (curvalue); 
				iterazioni=iterazioni+1;
				exit;
			end if; 
				fetch cur1 into buf; 
		end loop;
	end loop;
	return curvalue;
end; $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION "NumeriPrimi"(integer)
  OWNER TO postgres;

For who is interested Firebird has a very ugly code, was my first attempt to Programming... it is here

Code:

SET TERM ^ ;
ALTER PROCEDURE NUMPRIMI (
    QUANT Integer )
AS
DECLARE VARIABLE MaxValue bigint;
DECLARE VARIABLE curValue bigint; 
DECLARE VARIABLE buf bigint; 
DECLARE VARIABLE Iterazioni int; 
DECLARE  cur cursor for (Select num from numeriprimi);
BEGIN
execute procedure setfirst returning_values :curValue;
maxvalue=curValue*curValue;
Iterazioni=0;
while (iterazioni != quant) do
    begin 
    curvalue=curvalue+2;
    maxvalue= trunc(sqrt(curvalue));
    for select num from numeriprimi into :buf do
    begin
        if (buf >maxvalue) then
        begin 
            insert into numeriprimi values (:curvalue); 
            iterazioni=iterazioni+1;
            leave;
        end
        if (mod(curvalue,buf)=0) then leave;
    end
    
    
    end
END^
SET TERM ; ^


GRANT EXECUTE
 ON PROCEDURE NUMPRIMI TO  SYSDBA;

+ initializing function

Code:

SET TERM ^ ;
ALTER PROCEDURE SETFIRST
RETURNS (
    FT Bigint )
AS
declare cur cursor for (select num from Numeriprimi); 
declare variable buf bigint; 
BEGIN
   
    OPEN CUR;
    fetch cur into buf;
    if (row_count = 0) then 
    begin 
    insert into numeriprimi values (2);
    insert into numeriprimi values (3);
    ft=3;
    end
    else   
        begin 
            while (row_count = 1) do 
            begin
            ft=buf;
            fetch cur into buf;
        end
    end
END^
SET TERM ; ^


GRANT EXECUTE
 ON PROCEDURE SETFIRST TO  SYSDBA;

I made something wrong or PostgreSQL cannot give the better in this kind of solution?

shammat · #2 (**permalink**) 11-14-12, 17:04

No need to write a program

Code:

with primes (num) as (
  select generate_series(2,10000)
)
SELECT num
FROM primes p1
WHERE num not in (
    SELECT p1.num
    FROM primes p2
    WHERE p2.num < p1.num
    AND MOD(p1.num, p2.num) = 0
);

But it's probably going to be still slower then a procedural solution.

But to answer your question: I don't know what the reason is. But calculating prime numbers is hardly an adequate performance test for a relational database.

jurhas · #3 (**permalink**) 11-15-12, 16:04

Thanks for the answer. Was my interesting test the performance of the programming becaue I plan to do a Job white a very complicated Hierarchical Tree and was my intention utilize the database to manage the basic operation, delete,pruning, traverse blba bla..

I solve it:
there was a fetch more at the begin of the main WHILE, at each increment SCROLL the cursor run to the end and then move again to begin. I move the fetch inside the IF, so it is called each LastValue^2.
Now is amazing fast. Ok for the first 10000, firebird is still faster. I think because in PostgreSQL at the beginn the update of the cursor is made a lot of times. But after the first 10 000, is incredible fast. Calculate 10 000 000 number in 200 secs...

Code:

CREATE OR REPLACE FUNCTION "NumeriPrimi"("Qty" integer)
  RETURNS bigint AS
$BODY$
declare MaxVal bigint;
declare curValue bigint; 
declare buf bigint; 
declare Iterazioni int; 
DECLARE  cur1 SCROLL cursor for (Select "Numeri" from "Num");

BEGIN

	open cur1;
	fetch last from cur1 into buf;
	if buf is Null then
		 insert into "Num" values (2);
		insert into "Num" values (3);
		close cur1;
		open cur1;
		curValue=3;
		maxVal =9;
	else
		curValue =buf;
		maxVal=curValue * curValue;
	end if;
	Iterazioni=0;
	while (iterazioni <"Qty") loop
		
	    curvalue=curvalue+2;
		   
		if maxVAl< curvalue then
			close cur1;
			open cur1;
			fetch last from cur1 into buf;
			maxVAl=buf*buf;
		end if; 
		fetch first from cur1 into buf;
		while NOT (curvalue%buf=0)loop   
			if buf*buf> curvalue or buf is Null then
				insert into "Num" values (curvalue); 
				iterazioni=iterazioni+1;
				exit;
			end if; 
				fetch cur1 into buf; 
		end loop;
	end loop;
	return curValue;
end; $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION "NumeriPrimi"(integer)
  OWNER TO postgres;

Internet Services

Web Development

Digital Marketing

Consumer Tech