Home » SQL & PL/SQL » SQL & PL/SQL » Stored Procedure too slow
Stored Procedure too slow [message #403568] Sun, 17 May 2009 02:00 Go to next message
karthikpt
Messages: 11
Registered: May 2009
Junior Member
Stored Procedure too slow. A select from a column of type CHAR is too slow using the TRIM function. The execution period is around 3 minutes.

This is slow even when run on the PL/SQL editor.

Now removing the TRIM function for the column, when executed the query on a PL/SQL editor is supremely fast and gave the desired results. Less than 10 seconds.

However without the TRIM function on the same "Select" and inside an stored procedure gave NO RESULTS. This is completely weird and confusing.

NOTE: We are retrieving the values from the production table which is really huge. The synopsis of the select statement is,

SELECT DISTINCT RKEY
FROM TABLE_XYZ
WHERE (
(
(i_LAND IS NULL)
OR
(NVL(SUBSTR(TRIM(COL_AAA),1,Cool, '99991231') =
(NVL(TO_CHAR(TO_DATE(i_COL_AAA,'DD-MON-YYYY'),'YYYYMMDD') ,'99991230')))
)
AND
((i_COL_BBB IS NULL) OR (TRIM(COL_BBB = i_COL_BBB))
)

Can anyone suggest an apt solution to this problem?
Re: Stored Procedure too slow [message #403569 is a reply to message #403568] Sun, 17 May 2009 02:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Let me guess: you have an index on col_aaa, right?
Now using TRIM(col_aaa) causes Oracle not to use the index (functions render indexes useless)

You have two options here:
- the sane solution: alter the datatype to varchar2: CHAR datatypes are for ANSI-compatibility and should NOT be used for columns > 1 character (or at all)
- create a function based index on trim(col_aaa)
Re: Stored Procedure too slow [message #403572 is a reply to message #403569] Sun, 17 May 2009 02:38 Go to previous messageGo to next message
karthikpt
Messages: 11
Registered: May 2009
Junior Member
Hi Frank,

Thanks for quick and immediate reply.

Well as you have mentioned one of the option had been thought by us and we weren't able to proceed due to design and other reasons beyond our control.

Changing char to Varchar. You are absolutely right in mentioning "CHAR should NOT be used for columns > 1" however this is a design already implemented and system can't be changed.

Well can you please explain what is function based index? Providing an example is the best help I can get.

Thanks!

Regards,
Karthik
Re: Stored Procedure too slow [message #403573 is a reply to message #403572] Sun, 17 May 2009 02:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why can't it be changed? Just because the powers that be say so?
Reason with them. Tell them that using CHAR instead of VARCHAR2 will lead to hard-to-find bugs (queries giving "wrong" results because some developer did not think of trailing spaces), performance issues like you get now, etcetera.

If you want to know more about Function Based Indexes (or anything Oracle, for that matter), start with checking at http://tahiti.oracle.com
If you cannot find it there, use Google. If you still cannot find it, ask us here and tell us what you tried so far.
Re: Stored Procedure too slow [message #403574 is a reply to message #403573] Sun, 17 May 2009 03:06 Go to previous messageGo to next message
karthikpt
Messages: 11
Registered: May 2009
Junior Member
Frank,

I certainly understand your point and infact as a developer stand with you that we have to change the features which will improve in a long way in terms of
consistency, performance, bugs (cos of space).

However this is a product database (having an SLA) from which we are accessing/retrieving information. We do not have control on the structure and of course can put foward our point/concern which will take its own course of their analysis, RFC, Costing etc. End of which, I'm pretty sure will be rejected!!!!

So all this pain of work around Sad. From your mail, I got this idea of
Creating a View
Add an Function based Index to the required column of that view
Access the View

Will this help us? I could certainly try it, however your and our forum thoughts on it?

Regards,
Karthik
Re: Stored Procedure too slow [message #403589 is a reply to message #403573] Sun, 17 May 2009 05:50 Go to previous messageGo to next message
karthikpt
Messages: 11
Registered: May 2009
Junior Member
Frank,

Another important point which is puzzling me is,

The stored procedure returns no records when executed with the query "without" TRIM function.
However when executed from the editor it returns the required rows from the table.

The input argument to the SP is of type VARCHAR2 into the same column.

Any idea why this is failing?

Regards,
Karthik
Re: Stored Procedure too slow [message #403590 is a reply to message #403574] Sun, 17 May 2009 05:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
karthikpt wrote on Sun, 17 May 2009 10:06
So all this pain of work around Sad. From your mail, I got this idea of
Creating a View
Add an Function based Index to the required column of that view
Access the View



Indexes are built on tables, not on views. You creating a view in-between does not change anything.

Quote:
Frank,

Another important point which is puzzling me is,

The stored procedure returns no records when executed with the query "without" TRIM function.
However when executed from the editor it returns the required rows from the table.

The input argument to the SP is of type VARCHAR2 into the same column.

Any idea why this is failing?


This gives me an idea for a third option:
convert within your stored procedure your varchar2 (that you receive) to a char.
Use table.column%type for the datatype of the used variable, that will ensure that you use the correct length.
Have not tried it, don't want to start up my db to try it, but you could.
Re: Stored Procedure too slow [message #403591 is a reply to message #403590] Sun, 17 May 2009 06:08 Go to previous messageGo to next message
karthikpt
Messages: 11
Registered: May 2009
Junior Member
Hi Frank,

Its still slow!!! Yeah you are right in mentioning that an index is created on a table and would hold good for a view. My mistake (Perhaps my fustration was vented with such a thought Sad )

I don't see any other option....I'm stuck!!!
Re: Stored Procedure too slow [message #403593 is a reply to message #403591] Sun, 17 May 2009 06:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
karthikpt wrote on Sun, 17 May 2009 13:08
Hi Frank,

Its still slow!!!


What exactly did you do?
Re: Stored Procedure too slow [message #403594 is a reply to message #403593] Sun, 17 May 2009 06:27 Go to previous messageGo to next message
karthikpt
Messages: 11
Registered: May 2009
Junior Member
On the input argument changed to CHAR.

Inside the SP changed the code to

DECLARE trimmed_i_COL_BBB CHAR(12);
trimmed_i_COL_BBB := rpad(i_AC_REG_NO,12,' ');

and changed the select to

SELECT DISTINCT RKEY
FROM TABLE_XYZ
WHERE (
(
(i_LAND IS NULL)
OR
(NVL(SUBSTR(TRIM(COL_AAA),1,, '99991231') =
(NVL(TO_CHAR(TO_DATE(i_COL_AAA,'DD-MON-YYYY'),'YYYYMMDD') ,'99991230')))
)
AND
((i_COL_BBB IS NULL) OR ((COL_BBB = trimmed_i_COL_BBB))
)

This was slow and did not return any records from the SP.
Re: Stored Procedure too slow [message #403599 is a reply to message #403594] Sun, 17 May 2009 06:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since you now compare CHARs to CHARs, you can remove the TRIM, that was the whole idea.
Also, you don't need to rpad.

Looks to me like you don't really get the difference between VARCHAR2 and CHAR.. If so, read the documentation.


[Edit: Sorry, misread your code. If you would have used code-tags, it would have been much clearer. I still see a trim there.]

[Updated on: Sun, 17 May 2009 06:46]

Report message to a moderator

Re: Stored Procedure too slow [message #403600 is a reply to message #403599] Sun, 17 May 2009 06:51 Go to previous messageGo to next message
karthikpt
Messages: 11
Registered: May 2009
Junior Member
I did compare CHAR to CHAR without RTRIM and it gave no results from the SP. Thus stuffed the ' ' with RTRIM.

I have got the concept of CHAR and VARCHAR. However I would certainly not understand if a

Select without TRIM works on a PL/SQL editor

SELECT DISTINCT RKEY FROM TABLE_XYZ WHERE (('TT' IS NULL) OR (REGN= 'TT'))

and this wouldn't work when placed inside a stored procedure with an input argument.
Re: Stored Procedure too slow [message #403602 is a reply to message #403600] Sun, 17 May 2009 06:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Let's get back one step..
Here's your formatted code that does not perform:
SELECT DISTINCT RKEY
FROM   TABLE_XYZ
WHERE  (  i_LAND IS NULL
       OR NVL(SUBSTR(TRIM(COL_AAA), 1), '99991231') = NVL(TO_CHAR(TO_DATE(i_COL_AAA, 'DD-MON-YYYY'), 'YYYYMMDD'), '99991230')
       )
AND    (  i_COL_BBB IS NULL 
       OR COL_BBB = trimmed_i_COL_BBB
       )


Now, exactly which code did you execute (the one you initially mentioned as "without trim") that did perform?
And PLEASE format it and use [code]-tags, or it will be totally unreadable.
Re: Stored Procedure too slow [message #403651 is a reply to message #403602] Sun, 17 May 2009 22:45 Go to previous messageGo to next message
karthikpt
Messages: 11
Registered: May 2009
Junior Member
The code placed inside the SP is,
SELECT DISTINCT RKEY FROM TABLE_XYZ WHERE ((i_col_BBB IS NULL) OR (COL_BBB) = trimmed_i_col_BBB)
and
trimmed_i_col_BBB as mentioned is extracted from the input argument of type CHAR.
This returned "ZERO" rows from the SP.
However this query again with the input argument replaced with actual value when placed in the SP or executed on the editor worked Viz,
SELECT DISTINCT RKEY FROM TABLE_XYZ WHERE (('EEE' IS NULL) OR (COL_BBB) = 'EEE')
This is the disparity. Can you explain it?
Re: Stored Procedure too slow [message #403653 is a reply to message #403568] Sun, 17 May 2009 22:50 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.


Do NOT describe what you think you see.
CUT & PASTE so we can see what you do & how Oracle responds

[Updated on: Sun, 17 May 2009 22:55]

Report message to a moderator

Re: Stored Procedure too slow [message #403654 is a reply to message #403651] Sun, 17 May 2009 22:56 Go to previous messageGo to next message
karthikpt
Messages: 11
Registered: May 2009
Junior Member
The code placed inside the SP is,
SELECT DISTINCT RKEY FROM TABLE_XYZ WHERE ((i_col_BBB IS NULL) OR (COL_BBB) = trimmed_i_col_BBB)

and
trimmed_i_col_BBB as mentioned is extracted from the input argument of type CHAR.
This returned "ZERO" rows from the SP.
However this query again with the input argument replaced with actual value when placed in the SP or executed on the editor worked Viz,
SELECT DISTINCT RKEY FROM TABLE_XYZ WHERE (('EEE' IS NULL) OR (COL_BBB) = 'EEE')

This is the disparity. Can you explain it?
Re: Stored Procedure too slow [message #403655 is a reply to message #403568] Sun, 17 May 2009 23:04 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Since you continue to describe what you think is happening, you won't get any useful responses.

use sqlplus to compile & invoke you procedure both ways so we can see both results ourselves.

[Updated on: Sun, 17 May 2009 23:04]

Report message to a moderator

Re: Stored Procedure too slow [message #403662 is a reply to message #403655] Sun, 17 May 2009 23:36 Go to previous messageGo to next message
karthikpt
Messages: 11
Registered: May 2009
Junior Member
Result from SQL Plus
SELECT DISTINCT RKEY FROM TABLE_XYZ
WHERE (('EY538' IS NULL) OR (COL_BBB = 'EY538'));
      RKEY
----------
  47197979
  47450994
  ........114 rows selected.


and SP code snippet
i_COL_BBB CHAR;
FOR rkey_rec IN (SELECT DISTINCT RKEY FROM TABLE_XYZ
WHERE ((i_COL_BBB IS NULL) OR (COL_BBB = i_COL_BBB)))
LOOP

The output execution time is 248.547 seconds and got the same output (114 rows selected). ./fa/6225/0/Attached in the output of the SP execution.
Why should this code be slow? As mentioned this is a production table and has huge data (> 3 Million).
  • Attachment: untitled.JPG
    (Size: 37.01KB, Downloaded 382 times)
Re: Stored Procedure too slow [message #403663 is a reply to message #403568] Sun, 17 May 2009 23:43 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Why should this code be slow?
It remains a mystery because nobody knows what is happening out of sight.

Re: Stored Procedure too slow [message #403664 is a reply to message #403663] Sun, 17 May 2009 23:51 Go to previous messageGo to next message
karthikpt
Messages: 11
Registered: May 2009
Junior Member
Finally thought of sending the original code itself
This is the SP used for execution and
PROCEDURE Pc_Cp_Staging (i_LAND      VARCHAR2,
                           i_AC_REG_NO CHAR,
o_found OUT NUMBER) IS
  l_total_rec NUMBER(6) := 0;
  l_rec_cnt   NUMBER(10):=0;
  l_reg_no CHAR(12):=' ';  
    /* ******************
    Executable section
    ****************** */
  BEGIN
    /*DELETE FROM ABS_CP_STAGING;
	COMMIT;*/  
	/* *************************
    Selecting records from UFIS data
    which satisfy the conditions
    ************************* */

FOR rkey_rec IN (SELECT DISTINCT COL_AAA
FROM TABLE_XYZ WHERE (((i_LAND IS NULL) OR (NVL(SUBSTR(TRIM(AFTTAB.LAND),1,8), '99991231') = 
(NVL(TO_CHAR(TO_DATE(i_LAND,'DD-MON-YYYY'),'YYYYMMDD') ,'99991230')))) 
AND ((i_AC_REG_NO IS NULL) 
OR ((AFTTAB.REGN) = i_AC_REG_NO))))
LOOP                    

SELECT COUNT(1) INTO l_rec_cnt FROM ABS_CP_STAGING
WHERE UFIS_RELKEY = rkey_rec.COL_AAA;
IF l_rec_cnt > 0 THEN
DELETE FROM ABS_CP_STAGING WHERE UFIS_RELKEY = rkey_rec.COL_AAA;
COMMIT;
END IF;
FOR l_rec IN (SELECT COL_AAA, COL_BBB FROM TABLE_XYZ
	                        WHERE COL_AAA= rkey_rec.COL_AAA) LOOP
	     
	     	      l_total_rec := l_total_rec + 1;
	      INSERT INTO TEMP_TABLE
	        (COL_AAA, COL_BBB)
	      VALUES
	        (trim(l_rec.COL_AAA),
	         trim(l_rec.COL_BBB);
	    END LOOP;
 	    COMMIT;
	END LOOP;
    COMMIT;
	o_found := l_total_rec;
	dbms_output.put_line('Total Records:'||l_total_rec);
	EXCEPTION
	  WHEN NO_DATA_FOUND THEN
	       o_found := 0;
  END Pc_Cp_Staging;
  /* ******************************
  End of Procedure PC_CP_STAGING
  ****************************** */

[Updated on: Sun, 17 May 2009 23:52]

Report message to a moderator

Re: Stored Procedure too slow [message #403684 is a reply to message #403664] Mon, 18 May 2009 01:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This is absolutely unreadable.
Basic indentation: end if / end loop should start at the same position as the if / loop (just to name one).
Now it looks as though you don't have any END LOOPs.

Formatted properly:
PROCEDURE Pc_Cp_Staging 
( i_LAND          VARCHAR2
, i_AC_REG_NO     CHAR
, o_found     OUT NUMBER
) IS
  l_total_rec NUMBER(6) := 0;
  l_rec_cnt   NUMBER(10):=0;
  l_reg_no CHAR(12):=' ';  
  /* ******************
     Executable section
     ****************** */
  BEGIN
  /*DELETE FROM ABS_CP_STAGING;
  COMMIT;*/  
  /* *************************
    Selecting records from UFIS data
    which satisfy the conditions
    ************************* */
  FOR rkey_rec IN (SELECT DISTINCT COL_AAA
                   FROM   TABLE_XYZ 
                   WHERE  (  i_LAND IS NULL 
                          OR NVL(SUBSTR(TRIM(AFTTAB.LAND), 1, 8), '99991231') = NVL(TO_CHAR(TO_DATE(i_LAND,'DD-MON-YYYY'),'YYYYMMDD') ,'99991230')
                          ) 
                   AND    (  i_AC_REG_NO IS NULL
                          OR AFTTAB.REGN) = i_AC_REG_NO
                          )
                  )
  LOOP                    
    SELECT COUNT(*) 
    INTO   l_rec_cnt 
    FROM   ABS_CP_STAGING
    WHERE  UFIS_RELKEY = rkey_rec.COL_AAA;

    IF l_rec_cnt > 0 
    THEN
      DELETE ABS_CP_STAGING 
      WHERE  UFIS_RELKEY = rkey_rec.COL_AAA;
      COMMIT;
    END IF;

    FOR l_rec IN (SELECT COL_AAA, COL_BBB 
                  FROM   TABLE_XYZ
                  WHERE  COL_AAA= rkey_rec.COL_AAA
                 ) 
    LOOP
      l_total_rec := l_total_rec + 1;
      INSERT INTO TEMP_TABLE
      ( COL_AAA
      , COL_BBB
      ) VALUES
      ( trim(l_rec.COL_AAA)
      , trim(l_rec.COL_BBB)
      );
    END LOOP;
    COMMIT;
  END LOOP;
  COMMIT;
  o_found := l_total_rec;
  dbms_output.put_line('Total Records:'||l_total_rec);
  EXCEPTION
    WHEN NO_DATA_FOUND 
    THEN
      o_found := 0;
END Pc_Cp_Staging;
/* ******************************
  End of Procedure PC_CP_STAGING
  ****************************** */


Points that jump out:
There should be at most 1 commit in this procedure if any at all. Depending on who is calling this, commits should preferrably be done by some "orchestrating" procedure.

The row-by-row looping mechanism can easily be rewritten to two straight sqls: one for the insert and one for the delete.

The NO_DATA_FOUND exception will never be raised

The select count(1) (why 1? just use count(*)) doesn't add anything.
Re: Stored Procedure too slow [message #403756 is a reply to message #403662] Mon, 18 May 2009 09:03 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
NVL(SUBSTR(TRIM(AFTTAB.LAND), 1, 8), '99991231') = NVL(TO_CHAR(TO_DATE(i_LAND,'DD-MON-YYYY'),'YYYYMMDD') ,'99991230')


I am not sure whether this is a typo or done delibrately. Why you are comparing 99991231 with 99991230. If you ask me that completely defeat the purpose of having nvl.

Also this one I am struggling to understand why you need that.
SELECT COUNT(*) 
    INTO   l_rec_cnt 
    FROM   ABS_CP_STAGING
    WHERE  UFIS_RELKEY = rkey_rec.COL_AAA;

    IF l_rec_cnt > 0 
    THEN
      DELETE ABS_CP_STAGING 
      WHERE  UFIS_RELKEY = rkey_rec.COL_AAA;
      COMMIT;
    END IF;


http://tkyte.blogspot.com/2008_12_01_archive.html

Read the above link especially this one ("Doing it wrong... ").

The other comment is already been mentioned. Always try to do it in sql statement as far as possible.

Regards

Raj

[Edi: ] Replaced quote tag with code tag

[Updated on: Mon, 18 May 2009 09:17]

Report message to a moderator

Previous Topic: Discoverer Query
Next Topic: Composite range-range table
Goto Forum:
  


Current Time: Sat Dec 10 01:25:55 CST 2016

Total time taken to generate the page: 0.13226 seconds