If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > PostgreSQL > Postgres vs Firebird... hmmm

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 2,800
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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;
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On