Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Performance issue (Oracle 8i)
PL/SQL Performance issue [message #447157] Fri, 12 March 2010 01:35 Go to next message
Derek N
Messages: 80
Registered: September 2002
Member
I have the following query:
SELECT COUNT(*)
INTO L_DUMMY_1
FROM GL_INTERFACE
WHERE GL_ID = C0.A||C0.B||C0.C;

C0.A||C0.B||C0.C are values that come off a cursor that is based on a table where the fields A,B and C concatenated together make up the gl_id on the GL_INTERFACE table.

The way I have structured the code in the where clause is definitely going cause a performance issue. Any solutions on how I can overcome this performance issue?

Thanks
Re: PL/SQL Performance issue [message #447158 is a reply to message #447157] Fri, 12 March 2010 01:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why do you think this will cause a performance issue? You concatenate three constants (not a overcostly task) and compare a column with the result.
Re: PL/SQL Performance issue [message #447161 is a reply to message #447157] Fri, 12 March 2010 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I bet the performances issue comes from your cursor loop.
I bet this count(*) is useless.

Regards
Michel
Re: PL/SQL Performance issue [message #447166 is a reply to message #447161] Fri, 12 March 2010 02:23 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Don't know how many records were there, but once upon, I got following result.

SELECT COUNT(*) => 6 seconds
SELECT COUNT(ROWID) => 2 seconds
SELECT COUNT(1) => 1 second

regards,
Delna
Re: PL/SQL Performance issue [message #447167 is a reply to message #447166] Fri, 12 March 2010 02:38 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
delna.sexy wrote on Fri, 12 March 2010 08:23
Don't know how many records were there, but once upon, I got following result.

SELECT COUNT(*) => 6 seconds
SELECT COUNT(ROWID) => 2 seconds
SELECT COUNT(1) => 1 second

regards,
Delna


And did you run the test multiple times for each and average the result? You would have had caching kicking in. Count(1) is rewritten to count(*) internally so there is no way it can be faster.
Re: PL/SQL Performance issue [message #447169 is a reply to message #447167] Fri, 12 March 2010 02:44 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hi cookiemonster,
I ran them only once. And in that case, Oracle does hard-parsing for all of them. Right?

Quote:
Count(1) is rewritten to count(*) internally


Can you please give more details?
How can we confirm that Oracle replaces 1 by *?

regards,
Delna
Re: PL/SQL Performance issue [message #447170 is a reply to message #447167] Fri, 12 March 2010 02:47 Go to previous messageGo to next message
Derek N
Messages: 80
Registered: September 2002
Member
K, I have about just over a mil transactions in the GL_INTERFACE table. The count is necessary as I want to find if any id's already exist in the above table before I reload the table with new transactions.

Created anindex on the table and works much faster.

Thanx
Re: PL/SQL Performance issue [message #447171 is a reply to message #447170] Fri, 12 March 2010 03:09 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Derek,

This is Oracle professional forum.
Not a messanger.
So do not use messanger short words.

regards,
Delna
Re: PL/SQL Performance issue [message #447173 is a reply to message #447166] Fri, 12 March 2010 03:13 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
delna.sexy wrote on Fri, 12 March 2010 09:23
Don't know how many records were there, but once upon, I got following result.

SELECT COUNT(*) => 6 seconds
SELECT COUNT(ROWID) => 2 seconds
SELECT COUNT(1) => 1 second

regards,
Delna

NO, no, no!!
Don't go there.
Count(*) is fine. Internally count(1), count(*), count(rowid) are ALL THE SAME!

Once I saw a bicycle overtake a car. Does that mean bicycles are faster than cars?

[Updated on: Fri, 12 March 2010 03:13]

Report message to a moderator

Re: PL/SQL Performance issue [message #447174 is a reply to message #447170] Fri, 12 March 2010 03:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Indexes can do that Smile

If GL_ID is not unique, you can do an existance check faster by rewriting it as:
SELECT 1
INTO   L_DUMMY_1
FROM   DUAL
WHERE EXISTS (SELECT null
              FROM   GL_INTERFACE
              WHERE GL_ID = C0.A||C0.B||C0.C);
- this will stop reading the table/index as soon as it finds the first row that matches the condition, whereas your original code would read the whole table/index looking for the total number of matches
Re: PL/SQL Performance issue [message #447175 is a reply to message #447170] Fri, 12 March 2010 03:18 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Derek N wrote on Fri, 12 March 2010 09:47
K, I have about just over a mil transactions in the GL_INTERFACE table. The count is necessary as I want to find if any id's already exist in the above table before I reload the table with new transactions.

Created anindex on the table and works much faster.

Thanx

If you want to know if something exists you HAVE NOT to count them, only the first one is useful.
If you want to know if a room is empty, do you count all the people in it or do you stop when you see one?

Regards
Michel

Re: PL/SQL Performance issue [message #447179 is a reply to message #447173] Fri, 12 March 2010 03:48 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hi Frank,

Will you please give more details that how are all three same?

regards,
Delna
Re: PL/SQL Performance issue [message #447186 is a reply to message #447179] Fri, 12 March 2010 04:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Internally they all get translated to the same.
Search for it and especially look out for articles by Tom Kyte. He explains it in multiple places.
Re: PL/SQL Performance issue [message #447187 is a reply to message #447186] Fri, 12 March 2010 04:17 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Ok. Let me do that. And I will be back.

regards,
Delna
Re: PL/SQL Performance issue [message #447191 is a reply to message #447187] Fri, 12 March 2010 04:35 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Excited after knowing and examining trace results that all COUNT()s are one and the same.
Thanks all for their contribution.

regards,
Delna
Re: PL/SQL Performance issue [message #447192 is a reply to message #447157] Fri, 12 March 2010 04:42 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
Not all counts are the same.
Count(column) is different.
Re: PL/SQL Performance issue [message #447202 is a reply to message #447192] Fri, 12 March 2010 05:25 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
I was concerned with only performance, not count. Smile

regards,
Delna
Re: PL/SQL Performance issue [message #447234 is a reply to message #447157] Fri, 12 March 2010 10:11 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
not certain but I believe count(column) is also the same, IF THE COLUMN is defined as NOT NULL.

It was true once upon a time that the different formulations of count presented earlier did provide different performance results. But Oracle has had that fixed for some time now.

Kevin
Re: PL/SQL Performance issue [message #447415 is a reply to message #447234] Mon, 15 March 2010 03:15 Go to previous message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Kevin Meade wrote on Fri, 12 March 2010 10:11
not certain but I believe count(column) is also the same, IF THE COLUMN is defined as NOT NULL.

It was true once upon a time that the different formulations of count presented earlier did provide different performance results. But Oracle has had that fixed for some time now.

Kevin


Agree.
Previous Topic: function "dump" in oracle
Next Topic: unable to recreate an object
Goto Forum:
  


Current Time: Sat Oct 01 14:28:04 CDT 2016

Total time taken to generate the page: 0.04234 seconds