Home » SQL & PL/SQL » SQL & PL/SQL » Stored Procedure Performance ..
Stored Procedure Performance .. [message #195024] Tue, 26 September 2006 11:48 Go to next message
frank.svs
Messages: 162
Registered: February 2006
Senior Member
Hi frns,

I wrote a stored procedure which basically used for searching data in a table which contains
9 lakh records. Now based on the search criteria i am returning the resultset ptr. We are also implementing the
paging concept in or stored procedure which also returns the total cnt of the resultset which satisfies the search criteria.
But the problem is that when we are using the following stmt
SELECT COUNT(colname) INTO v_totalpgcnt FROM <SQLQRY>. i think its taking a lot of time.
Can anyone suggest is there any alternate way to get the total count of resultset.

Please suggest me. I am also providing the snippet of how our stored procedure look like.



PROCEDURE SP_MOA_PRACTICE_GET(
p_hlth_grp_name IN VARCHAR2,
p_hlthcare_grp_id IN VARCHAR2,
p_address1 IN VARCHAR2,
p_address2 IN VARCHAR2,
p_phone_num IN VARCHAR2,
p_city IN VARCHAR2,
p_state_cde IN VARCHAR2,
p_zip5_cde IN VARCHAR2,
p_pgno IN NUMBER,
p_recs_per_pg IN NUMBER,
p_uname IN VARCHAR2,
p_legal_entity IN VARCHAR2,
p_cnt OUT NUMBER,
p_errnumber OUT NUMBER,
p_errmsg OUT VARCHAR2,
p_rs OUT resultset)
AS
p_sqlqry1 VARCHAR2(2000);
p_sqlqry2 VARCHAR2(2000);
p_sqlqry3 VARCHAR2(2000);
p_lowerlimit NUMBER;
p_upperlimit NUMBER;
p_legalentity VARCHAR2(200):=p_legal_entity;
BEGIN
p_legalentity:=''''||REPLACE(p_legal_entity,',',''',''')||'''';
p_lowerlimit:=((p_pgno-1)*p_recs_per_pg)+1;
p_upperlimit:=(p_pgno*p_recs_per_pg);
p_sqlqry1:='SELECT ROWNUM sno,
mmd.q_hlthcare_grp_id,
mmd.q_hlth_grp_name,
mmd.address1,
mmd.address2,
mmd.city,
mmd.q_state_cde,
mmd.q_zip5_cde
FROM MOA_MARKET_DETAIL mmd WHERE
mmd.q_hlthcare_grp_id IS NOT NULL ';
IF (LENGTH(TRIM(p_hlth_grp_name))>0) THEN
p_sqlqry1:=p_sqlqry1||' and UPPER(mmd.q_hlth_grp_name) LIKE '||'''%'||UPPER(p_hlth_grp_name)||'%''';
END IF;
IF (LENGTH(TRIM(p_hlthcare_grp_id))>0) THEN
p_sqlqry1:=p_sqlqry1||' and UPPER(mmd.q_hlthcare_grp_id)='||''''||UPPER(p_hlthcare_grp_id)||'''';
END IF;
IF (LENGTH(TRIM(p_address1))>0) THEN
p_sqlqry1:=p_sqlqry1||' and UPPER(mmd.address1) LIKE '||'''%'||UPPER(p_address1)||'%''';
END IF;
IF (LENGTH(TRIM(p_address2))>0) THEN
p_sqlqry1:=p_sqlqry1||' and UPPER(mmd.address2) like '||'''%'||UPPER(p_address2)||'%''';
END IF;
IF (LENGTH(TRIM(p_phone_num))>0) THEN
p_sqlqry1:=p_sqlqry1||' and mmd.q_phone_num='||''''||p_phone_num||'''';
END IF;
IF (LENGTH(TRIM(p_city))>0) THEN
p_sqlqry1:=p_sqlqry1||'and UPPER(mmd.city) LIKE '||'''%'||UPPER(p_city)||'%''';
END IF;
IF (LENGTH(TRIM(p_state_cde))>0) THEN
p_sqlqry1:=p_sqlqry1||' and UPPER (mmd.q_state_cde)='||''''||UPPER(p_state_cde)||'''';
END IF;
IF (LENGTH(TRIM(p_zip5_cde))>0) THEN
p_sqlqry1:=p_sqlqry1||' and UPPER (mmd.q_zip5_cde)='||''''||UPPER(p_zip5_cde)||'''';
END IF;
-- Role Mgmt modified
IF(p_uname!='ADMINISTRATOR')THEN
p_sqlqry1:=p_sqlqry1||' AND EXISTS
(SELECT mvcx.q_perf_lis_id FROM MOA_VENDR_CLT_XRF mvcx WHERE mvcx.q_perf_lis_id= mmd.q_perf_lis_id AND mvcx.q_bl_org_id IN ('||p_legalentity||'))';
END IF;
p_sqlqry2:='SELECT q_hlthcare_grp_id "Practice id",q_hlth_grp_name "Practice name", address1 "Address line1",address2 "Address line2", city "City", q_state_cde "State", q_zip5_cde "Zipcode" FROM ('||p_sqlqry1||') WHERE sno BETWEEN '||p_lowerlimit||' AND '||p_upperlimit;
p_sqlqry3:='SELECT COUNT(sno) FROM ('||p_sqlqry1||')';
EXECUTE IMMEDIATE p_sqlqry3 INTO p_cnt;
OPEN p_rs FOR p_sqlqry2;
EXCEPTION
WHEN OTHERS THEN
p_errnumber:=SQLCODE;
p_errmsg:=SQLERRM;
END SP_MOA_PRACTICE_GET;

Thanks in advance
frank

Re: Stored Procedure Performance .. [message #195109 is a reply to message #195024] Wed, 27 September 2006 02:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
add "count(*) over()" as selected column to your original query. This removes the need for the extra query
Re: Stored Procedure Performance .. [message #195125 is a reply to message #195024] Wed, 27 September 2006 02:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There is no way to know the number of results that the query will return without effectively doing all of the work required to fetch all of the rows form the query.
Tom Kyte has some fairly string views on this in this thread (Search for the 2nd, 3rd and 4th occurrences of GOOGLE).
Re: Stored Procedure Performance .. [message #195778 is a reply to message #195024] Mon, 02 October 2006 06:21 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Hi.

IMHO your query has a number of problems:
1. It doesn't use bind variables, so each execution causes HARD parse.
2. It uses "UPPER(column_name)" so unless you have function based index on UPPER(column_name) it is NOT able to use index.
3. It uses " LIKE '%' || UPPER(value) || '%' " - so againg index usage is impossible.
4. Everything is done twice ( once for query2 and once again for query3), however it's not clear why do you need query2 at all, because there are different number of rows selected in q2 and q3.

1. Try to remove '%' concatenation at least for some of the columns.
2. If most of values in q_hlthcare_grp_id column are NULLs then it will be better to ask "mmd.q_hlthcare_grp_id > ' '" instead of "mmd.q_hlthcare_grp_id IS NOT NULL"
3. Do you have any indexes defined?
4. What are the most common parameters given during proc execution?
Previous Topic: optimisation prob
Next Topic: query taking long time
Goto Forum:
  


Current Time: Sun Dec 11 07:52:55 CST 2016

Total time taken to generate the page: 0.08733 seconds