Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_RANDOM (Slow program Random number generator using DBMS_RANDOM)
DBMS_RANDOM [message #338339] Mon, 04 August 2008 11:18 Go to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
I pasting a portion of my code below that generates random number. The program is taking approx 1 sec to generate / number.

Ex 100 number take about 100 sec. Could someone advice something to improve this.


FOR i IN 1 .. 10000
LOOP


SELECT hsecs
INTO rn
FROM gv$timer;
DBMS_RANDOM.initialize (rn);

SELECT SUBSTR (TO_CHAR (ABS (DBMS_RANDOM.random)),
1,
v_coupcod_len_frm_val
)
INTO v_coup_code
FROM DUAL;
DBMS_LOCK.sleep (1);

insert into a table
end loop;
Re: DBMS_RANDOM [message #338340 is a reply to message #338339] Mon, 04 August 2008 11:22 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, DUH!!!

The program is taking approx 1 sec to generate / number.


DBMS_LOCK.sleep (1);


Of course, when you let it sleep one second after generating every number.
Re: DBMS_RANDOM [message #338341 is a reply to message #338339] Mon, 04 August 2008 11:23 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
1 (one) second delay, you say? So try to get rid of
DBMS_LOCK.sleep (1);
and see what happens.

A good one, Thomas, isn't it? ./fa/1963/0/

[Updated on: Mon, 04 August 2008 11:25]

Report message to a moderator

Re: DBMS_RANDOM [message #338344 is a reply to message #338341] Mon, 04 August 2008 11:34 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:
A good one, Thomas, isn't it?


Yep. It kinda gave me the idea that I should maybe put a couple of sleeps in my own stuff. Then when a customer comes with a "speed it up!!" request, I can comply quite easily. Very Happy
Re: DBMS_RANDOM [message #338347 is a reply to message #338344] Mon, 04 August 2008 12:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Then when a customer comes with a "speed it up!!" request, I can comply quite easily.

Don't laugh, I worked for a computer company and it was this way we upgraded computers. When a customer wanted to upgrade from model XXXX to model YYYY, we came, opened the box, stripped off some capacitors and there "it's ok you have the new model". Laughing

Regards
Michel
Re: DBMS_RANDOM [message #338353 is a reply to message #338340] Mon, 04 August 2008 13:17 Go to previous messageGo to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
yes ....for 100 number it takes 100 secs.

The sleep(1) is 100th of a second where is it using 990 secs
Re: DBMS_RANDOM [message #338354 is a reply to message #338341] Mon, 04 August 2008 13:20 Go to previous messageGo to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
Sleep(1) is just 10 millisecond. If I remove sleep it will bring duplicate numbers.

[Updated on: Mon, 04 August 2008 13:21]

Report message to a moderator

Re: DBMS_RANDOM [message #338355 is a reply to message #338344] Mon, 04 August 2008 13:27 Go to previous messageGo to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
Thomas,
I do appreciate your sense of humour. But have you used the package ever to generate random numbers. Or did you run the program and see what happens.
Re: DBMS_RANDOM [message #338358 is a reply to message #338354] Mon, 04 August 2008 13:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
chintu00 wrote on Mon, 04 August 2008 20:20
Sleep(1) is just 10 millisecond. If I remove sleep it will bring duplicate numbers.

Sleep(1) is one second.
I got MY info from the docs, now where did YOU get yours from?

And who told you random numbers would never be duplicates?
That would make it quite easy to guess the next one then, after a while, wouldn't it?

[Updated on: Mon, 04 August 2008 13:42]

Report message to a moderator

Re: DBMS_RANDOM [message #338382 is a reply to message #338358] Mon, 04 August 2008 15:52 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

Thomas,
I do appreciate your sense of humour. But have you used the package ever to generate random numbers. Or did you run the program and see what happens.



Yes, I have used dbms_random countless times, and have used dbms_lock.sleep() countless times. I couldn't run YOUR code exactly, though, because it's 1) not completely valid syntax and 2) not a complete test case.

You only need to initialize it ONCE by the way. BEFORE the loop. Not before every single random number generation.

And, as Frank said, there will definitely be duplicates sooner or later when you use random numbers.
Re: DBMS_RANDOM [message #338496 is a reply to message #338354] Tue, 05 August 2008 04:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You only get duplicate numbers because you initialise the Random number generator every time you call it, and with a value that only changes every 100th of a second.
Put this code into a package, and put the call to initialise Dbms_Random into the package body initialisation code - that way it will get run once at the first call to this package for a session.

Also, a call to DBMS_UTILITY.GET_TIME takes roughly 1/45 of the time that your SELECT from v$timer does.
Re: DBMS_RANDOM [message #338680 is a reply to message #338358] Tue, 05 August 2008 11:19 Go to previous messageGo to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
Thanks for pointing out I can't remember where I read or heard that sleep(1) was 10 millisec.
I have removed the sleep and and found an alternate way of avoiding the duplicate. The performance is many times better.
Re: DBMS_RANDOM [message #338682 is a reply to message #338680] Tue, 05 August 2008 11:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
found an alternate way of avoiding the duplicate

Which one?

Regards
Michel
Re: DBMS_RANDOM [message #339023 is a reply to message #338682] Wed, 06 August 2008 10:17 Go to previous messageGo to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
I am inserting into the table to check for duplicate.


CREATE TABLE COUPON
(
COUP_ID NUMBER
)

/


declare
rn NUMBER;
v_coup_code number;
num number;
y pls_integer;
x pls_integer;
d pls_integer;
begin

FOR i IN 1 .. 1000 LOOP
<<top>>
SELECT hsecs
INTO rn
FROM gv$timer;
DBMS_RANDOM.initialize (rn);
SELECT SUBSTR (TO_CHAR (ABS (DBMS_RANDOM.random)),1,7)
INTO v_coup_code
FROM DUAL;

begin
select 1 from coupon a where a.coup_id = v_coup_code;
if sql%found then
goto top;
end if;
exception
when no_data_found then
insert into coupon values(v_coup_code);
end;
commit;
end loop;
exception
when others then
DBMS_OUTPUT.put_line('Error -'|| SQLERRM);
end ;
/

[Updated on: Wed, 06 August 2008 10:21]

Report message to a moderator

Re: DBMS_RANDOM [message #339035 is a reply to message #339023] Wed, 06 August 2008 11:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Really ugly and awful code, one of the worst I have ever seen for a long time.

In addition, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel
Re: DBMS_RANDOM [message #339040 is a reply to message #339023] Wed, 06 August 2008 11:12 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It will run much quicker if you rewrite it like this:
 

DBMS_RANDOM.initialize (dbms_utility.get_time);
FOR i IN 1 .. 1000 LOOP
<<top>>
  v_coup_code := SUBSTR (TO_CHAR (ABS (DBMS_RANDOM.random)),1,7)

  select count(*)
  into v_val
  from dual
  where exists (select 1 from coupon a where a.coup_id = v_coup_code);
  if v_val > 0 then
    goto top;
  else;
    insert into coupon values(v_coup_code);
  end if;

end loop;
commit;

That way you only initialise the generator once, you remove 2000 unneccessary select statements, you remove a lot of duplicate numbers (you were still initialising the generator every time, and if you initialise it with the same number as last time, you'll get the same random number back), you only commit once, and the select to find out if the number exists is about an order of magnitude more effificient
Previous Topic: help in SQL Sorting
Next Topic: query on "Can one retrieve only the Nth row from a table? "
Goto Forum:
  


Current Time: Wed Dec 07 04:40:27 CST 2016

Total time taken to generate the page: 0.12883 seconds