Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2012
    Posts
    2

    Question Postgres vs Firebird... hmmm

    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?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,817
    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.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Nov 2012
    Posts
    2

    Hallo

    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;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •