Home » SQL & PL/SQL » SQL & PL/SQL » Performance issues with paging a stored procedure
| Performance issues with paging a stored procedure [message #114977] |
Wed, 06 April 2005 11:48  |
Vishal_7
Messages: 63
Registered: April 2005
|
Member |
|
|
Hello,
I used the stored procedure described in the thread by Barbard to do the paging. However I am having performance issues with this sp. I am using 7 tables, with several fields and several joins. When using the same SQL without the same procedure, then it takes approx 44 secs to return 607 rows. When using the sp it takes 2min 14secs to return 25 rows. Can I do something with the performance on that sp?
Thanks
|
|
|
|
| Re: Performance issues with paging a stored procedure [message #114995 is a reply to message #114977] |
Wed, 06 April 2005 13:42  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
I'm not sure if Barbara saw my comment in the original thread about performance, but keep in mind that this new query you are using does not take advantage of the COUNT STOPKEY optimization that was part of my initial reply.
I have not been following that thread though since you veered off in such a totally different direction.
|
|
|
|
|
|
|
|
| Re: Performance issues with paging a stored procedure [message #115035 is a reply to message #114998] |
Wed, 06 April 2005 20:43 |
 |
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Here is a demonstration of a revision that uses rownum which will show as count (stopkey) in the explain plan, instead of the row_number analytic function that I used previously. I also moved the two subqueries to derive the more_reocrds and total_pages from the select clause to the from clause. Please test it and let us know if it is any faster. I suspect that the difference will be negligible. The primary things that are slowing it down are the two subqueries to get the more_records and total_pages and the fact that it is using dynamic sql.
scott@ORA92> CREATE OR REPLACE PROCEDURE GlobalGetSortedPage
2 -- input and output parameters:
3 (p_RefCursor OUT SYS_REFCURSOR,
4 p_TableNames IN VARCHAR2,
5 P_SelectFields IN VARCHAR2,
6 P_GroupByFields IN VARCHAR2 := NULL,
7 p_PageIndex IN INT := 1,
8 p_PageSize IN INT := 25,
9 p_SortFields IN VARCHAR2 := '1',
10 p_QueryFilter IN VARCHAR2 := NULL)
11 AS
12 -- declare local variables and assign values to them:
13 v_query VARCHAR2(32767);
14 v_FirstRec INT := (p_PageIndex - 1) * p_PageSize;
15 v_LastRec INT := p_PageIndex * p_PageSize + 1;
16 v_groupby VARCHAR2(32767) := p_GroupbyFields;
17 BEGIN
18 -- assemble query string:
19 IF v_groupby IS NOT NULL THEN
20 v_groupby := ' GROUP BY ' || v_groupby;
21 END IF;
22 v_query := 'SELECT t3.*,t4.more_records,t5.total_pages'
23 || CHR(10) || ' FROM (SELECT t2.*'
24 || CHR(10) || ' FROM (SELECT ROWNUM id,t1.*'
25 || CHR(10) || ' FROM (SELECT ' || p_SelectFields
26 || CHR(10) || ' FROM ' || p_TableNames
27 || CHR(10) || ' WHERE ' || NVL (p_QueryFilter, '1=1')
28 || CHR(10) || v_groupby
29 || CHR(10) || ' ORDER BY ' || p_sortfields || ') t1'
30 || CHR(10) || ' WHERE ROWNUM<:b_LastRec) t2'
31 || CHR(10) || ' WHERE t2.id>:b_FirstRec) t3,'
32 || CHR(10) || '(SELECT GREATEST((COUNT(*)-:b_LastRec)+1,0) more_records'
33 || CHR(10) || ' FROM (SELECT ' || p_SelectFields
34 || CHR(10) || ' FROM ' || p_Tablenames
35 || CHR(10) || ' WHERE ' || NVL (p_QueryFilter, '1=1')
36 || CHR(10) || v_groupby
37 || CHR(10) || ')) t4,'
38 || CHR(10) || '(SELECT CEIL(COUNT(*)/:b_PageSize) total_pages'
39 || CHR(10) || ' FROM (SELECT ' || p_SelectFields
40 || CHR(10) || ' FROM ' || p_Tablenames
41 || CHR(10) || ' WHERE ' || NVL (p_QueryFilter, '1=1')
42 || CHR(10) || v_groupby
43 || CHR(10) || ')) t5';
44 -- optional display of dynamic query:
45 FOR i IN 0 .. CEIL (LENGTH (v_query) / 250) LOOP
46 DBMS_OUTPUT.PUT_LINE (SUBSTR (v_query, (i * 250) + 1, 250));
47 END LOOP;
48 -- open dynamic ref cursor for page of ordered, numbered records
49 -- and how many more:
50 OPEN p_RefCursor FOR v_query
51 USING v_LastRec, v_FirstRec, v_LastRec, p_PageSize;
52 END GlobalGetSortedPage;
53 /
Procedure created.
scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> SET SERVEROUTPUT ON
scott@ORA92> SET AUTOPRINT ON
scott@ORA92> VARIABLE g_ref REFCURSOR
scott@ORA92> EXEC GlobalGetSortedPage (:g_ref,'emp','emp.*',null,1,5,'ename')
SELECT t3.*,t4.more_records,t5.total_pages
FROM (SELECT t2.*
FROM (SELECT ROWNUM id,t1.*
FROM (SELECT emp.*
FROM emp
WHERE 1=1
ORDER BY ename) t1
WHERE ROWNUM<:b_LastRec) t2
WHERE t2.id>:b_FirstRec) t3,
(SELECT GREATEST((COUNT(*)-:b_LastRe
c)+1,0) more_records
FROM (SELECT emp.*
FROM emp
WHERE 1=1
)) t4,
(SELECT CEIL(COUNT(*)/:b_PageSize) total_pages
FROM (SELECT emp.*
FROM emp
WHERE 1=1
)) t5
PL/SQL procedure successfully completed.
ID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO MORE_RECORDS TOTAL_PAGES
---------- ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ------------ -----------
1 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 9 3
2 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 9 3
3 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 9 3
4 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 9 3
5 7902 FORD ANALYST 7566 03-DEC-81 3000 20 9 3
scott@ORA92> EXEC GlobalGetSortedPage (:g_ref,'emp','emp.*',null,2,5,'ename')
SELECT t3.*,t4.more_records,t5.total_pages
FROM (SELECT t2.*
FROM (SELECT ROWNUM id,t1.*
FROM (SELECT emp.*
FROM emp
WHERE 1=1
ORDER BY ename) t1
WHERE ROWNUM<:b_LastRec) t2
WHERE t2.id>:b_FirstRec) t3,
(SELECT GREATEST((COUNT(*)-:b_LastRe
c)+1,0) more_records
FROM (SELECT emp.*
FROM emp
WHERE 1=1
)) t4,
(SELECT CEIL(COUNT(*)/:b_PageSize) total_pages
FROM (SELECT emp.*
FROM emp
WHERE 1=1
)) t5
PL/SQL procedure successfully completed.
ID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO MORE_RECORDS TOTAL_PAGES
---------- ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ------------ -----------
6 7900 JAMES CLERK 7698 03-DEC-81 950 30 4 3
7 7566 JONES MANAGER 7839 02-APR-81 2975 20 4 3
8 7839 KING PRESIDENT 17-NOV-81 5000 10 4 3
9 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 4 3
10 7934 MILLER CLERK 7782 23-JAN-82 1300 10 4 3
scott@ORA92> EXEC GlobalGetSortedPage (:g_ref,'emp','emp.*',null,3,5,'ename')
SELECT t3.*,t4.more_records,t5.total_pages
FROM (SELECT t2.*
FROM (SELECT ROWNUM id,t1.*
FROM (SELECT emp.*
FROM emp
WHERE 1=1
ORDER BY ename) t1
WHERE ROWNUM<:b_LastRec) t2
WHERE t2.id>:b_FirstRec) t3,
(SELECT GREATEST((COUNT(*)-:b_LastRe
c)+1,0) more_records
FROM (SELECT emp.*
FROM emp
WHERE 1=1
)) t4,
(SELECT CEIL(COUNT(*)/:b_PageSize) total_pages
FROM (SELECT emp.*
FROM emp
WHERE 1=1
)) t5
PL/SQL procedure successfully completed.
ID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO MORE_RECORDS TOTAL_PAGES
---------- ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ------------ -----------
11 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 0 3
12 7369 SMITH CLERK 7902 17-DEC-80 800 20 0 3
13 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 0 3
14 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 0 3
scott@ORA92>
[Updated on: Wed, 06 April 2005 21:53] Report message to a moderator |
|
|
|
| Re: Performance issues with paging a stored procedure [message #115040 is a reply to message #114995] |
Wed, 06 April 2005 22:08 |
|
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Todd,
Vishal's requirements are not just the usual requirement to return a page of a result set. You know you have seen me use and advocate rownum and count (stopkey) for that a thousand times. We have all also used the row_number analytic function for the same thing and the differences are neglible, although I agree that rownum is still slightly faster. His requirements are to dyanimcally return a page of a result set, where the user passes in any table name, columns to be displayed, filter conditions, group by conditions and order by conditions, plus the usual page size and number of pages. In addition to that, he is insistent upon having the number of remaining records and the total number of pages returned as part of that result set. I have explained that this is not usually done, but that is the requirement. That is why I have added the additional two subqueries. If you look within the dynamic sql and ignore those two subqeries, you will see that the remaining third query contains the usual page retrieval method. In the eaerlier versions, before the requiremenets expanded, I used row_number instead of rownum, because it required one less level of nesting of subqueries, which was handy at the time, and I knew the performance difference was there, but so minor that it is negligible. However, since you are insistent, I have rewritten it to user rownum instead of row_number. The dyanmic sql generates a query something like the following which, as you can see, now includes the count(stopkey). Don't worry about the cartesian joins, because they are joins to single-row subqueries and don't worry about the full table scan because I am returning most of the rows from a 14-row table ordered by a non-indexed column. The plan will be different for larger tables with indexed columns.
scott@ORA92> SET AUTOTRACE ON EXPLAIN
scott@ORA92> SELECT t3.*,t4.more_records,t5.total_pages
2 FROM -- the usual method to retrieve a page:
3 (SELECT t2.*
4 FROM (SELECT ROWNUM id,t1.*
5 FROM (SELECT emp.*
6 FROM emp
7 WHERE 1=1
8 ORDER BY ename) t1
9 WHERE ROWNUM<6) t2
10 WHERE t2.id>0) t3,
11 -- subquery to get how many more records there are:
12 (SELECT GREATEST((COUNT(*)-6)+1,0) more_records
13 FROM (SELECT emp.*
14 FROM emp
15 WHERE 1=1)) t4,
16 -- subquery to get the total number of pages:
17 (SELECT CEIL(COUNT(*)/5) total_pages
18 FROM (SELECT emp.*
19 FROM emp
20 WHERE 1=1)) t5
21 /
ID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO MORE_RECORDS TOTAL_PAGES
---------- ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ------------ -----------
1 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 9 3
2 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 9 3
3 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 9 3
4 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 9 3
5 7902 FORD ANALYST 7566 03-DEC-81 3000 20 9 3
Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=5 Bytes=630)
1 0
MERGE JOIN (CARTESIAN) (Cost=8 Card=5 Bytes=630)
2 1
MERGE JOIN (CARTESIAN) (Cost=4 Card=1 Bytes=26)
3 2
VIEW (Cost=2 Card=1 Bytes=13)
4 3
SORT (AGGREGATE)
5 4
TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14)
6 2
FIRST ROW
7 6
VIEW
8 7
SORT (AGGREGATE)
9 8
TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14)
10 1
VIEW
11 10
COUNT (STOPKEY)
12 11
VIEW (Cost=4 Card=14 Bytes=1218)
13 12
SORT (ORDER BY STOPKEY) (Cost=4 Card=14 Bytes=560)
14 13
TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=560)
scott@ORA92> SET AUTOTRACE OFF
|
|
|
|
| Re: Performance issues with paging a stored procedure [message #115041 is a reply to message #115040] |
Wed, 06 April 2005 22:26 |
|
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I put the subqueries back in the select clause and eliminated one level of nesting, but kept the rownum and count (stopkey). I think it has a better explain plan. Here is another demo of the latest revision, followed by the explain plan from the generated query. I have substituted the actual values for the bind variables when running the generated sql with autotrace to generate the explain plan.
scott@ORA92> CREATE OR REPLACE PROCEDURE GlobalGetSortedPage
2 -- input and output parameters:
3 (p_RefCursor OUT SYS_REFCURSOR,
4 p_TableNames IN VARCHAR2,
5 P_SelectFields IN VARCHAR2,
6 P_GroupByFields IN VARCHAR2 := NULL,
7 p_PageIndex IN INT := 1,
8 p_PageSize IN INT := 25,
9 p_SortFields IN VARCHAR2 := '1',
10 p_QueryFilter IN VARCHAR2 := NULL)
11 AS
12 -- declare local variables and assign values to them:
13 v_query VARCHAR2(32767);
14 v_FirstRec INT := (p_PageIndex - 1) * p_PageSize;
15 v_LastRec INT := p_PageIndex * p_PageSize + 1;
16 v_groupby VARCHAR2(32767) := p_GroupbyFields;
17 BEGIN
18 -- assemble query string:
19 IF v_groupby IS NOT NULL THEN
20 v_groupby := ' GROUP BY ' || v_groupby;
21 END IF;
22 v_query := ' SELECT t2.*,'
23 || CHR(10) || '(SELECT GREATEST((COUNT(*)-:b_LastRec)+1,0)'
24 || CHR(10) || ' FROM (SELECT ' || p_SelectFields
25 || CHR(10) || ' FROM ' || p_Tablenames
26 || CHR(10) || ' WHERE ' || NVL (p_QueryFilter, '1=1')
27 || CHR(10) || v_groupby || ')) more_records,'
28 || CHR(10) || '(SELECT CEIL(COUNT(*)/:b_PageSize)'
29 || CHR(10) || ' FROM (SELECT ' || p_SelectFields
30 || CHR(10) || ' FROM ' || p_Tablenames
31 || CHR(10) || ' WHERE ' || NVL (p_QueryFilter, '1=1')
32 || CHR(10) || v_groupby || ')) total_pages'
33 || CHR(10) || ' FROM (SELECT ROWNUM id,t1.*'
34 || CHR(10) || ' FROM (SELECT ' || p_SelectFields
35 || CHR(10) || ' FROM ' || p_TableNames
36 || CHR(10) || ' WHERE ' || NVL (p_QueryFilter, '1=1')
37 || CHR(10) || v_groupby
38 || CHR(10) || ' ORDER BY ' || p_sortfields || ') t1'
39 || CHR(10) || ' WHERE ROWNUM<:b_LastRec) t2'
40 || CHR(10) || ' WHERE t2.id>:b_FirstRec';
41 -- optional display of dynamic query:
42 FOR i IN 0 .. CEIL (LENGTH (v_query) / 250) LOOP
43 DBMS_OUTPUT.PUT_LINE (SUBSTR (v_query, (i * 250) + 1, 250));
44 END LOOP;
45 -- open dynamic ref cursor for page of ordered, numbered records
46 -- and how many more:
47 OPEN p_RefCursor FOR v_query
48 USING v_LastRec, p_PageSize, v_LastRec, v_FirstRec;
49 END GlobalGetSortedPage;
50 /
Procedure created.
scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> SET SERVEROUTPUT ON
scott@ORA92> SET AUTOPRINT ON
scott@ORA92> VARIABLE g_ref REFCURSOR
scott@ORA92> EXEC GlobalGetSortedPage (:g_ref,'emp','emp.*',null,1,5,'ename')
SELECT t2.*,
(SELECT GREATEST((COUNT(*)-:b_LastRec)+1,0)
FROM (SELECT emp.*
FROM emp
WHERE 1=1
)) more_records,
(SELECT CEIL(COUNT(*)/:b_PageSize)
FROM (SELECT emp.*
FROM emp
WHERE 1=1
)) total_pages
FROM (SELECT ROWNUM id,t1.*
FROM (SELECT
emp.*
FROM emp
WHERE 1=1
ORDER BY ename) t1
WHERE ROWNUM<:b_LastRec) t2
WHERE t2.id>:b_FirstRec
PL/SQL procedure successfully completed.
ID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO MORE_RECORDS TOTAL_PAGES
---------- ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ------------ -----------
1 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 9 3
2 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 9 3
3 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 9 3
4 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 9 3
5 7902 FORD ANALYST 7566 03-DEC-81 3000 20 9 3
scott@ORA92> EXEC GlobalGetSortedPage (:g_ref,'emp','emp.*',null,2,5,'ename')
SELECT t2.*,
(SELECT GREATEST((COUNT(*)-:b_LastRec)+1,0)
FROM (SELECT emp.*
FROM emp
WHERE 1=1
)) more_records,
(SELECT CEIL(COUNT(*)/:b_PageSize)
FROM (SELECT emp.*
FROM emp
WHERE 1=1
)) total_pages
FROM (SELECT ROWNUM id,t1.*
FROM (SELECT
emp.*
FROM emp
WHERE 1=1
ORDER BY ename) t1
WHERE ROWNUM<:b_LastRec) t2
WHERE t2.id>:b_FirstRec
PL/SQL procedure successfully completed.
ID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO MORE_RECORDS TOTAL_PAGES
---------- ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ------------ -----------
6 7900 JAMES CLERK 7698 03-DEC-81 950 30 4 3
7 7566 JONES MANAGER 7839 02-APR-81 2975 20 4 3
8 7839 KING PRESIDENT 17-NOV-81 5000 10 4 3
9 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 4 3
10 7934 MILLER CLERK 7782 23-JAN-82 1300 10 4 3
scott@ORA92> EXEC GlobalGetSortedPage (:g_ref,'emp','emp.*',null,3,5,'ename')
SELECT t2.*,
(SELECT GREATEST((COUNT(*)-:b_LastRec)+1,0)
FROM (SELECT emp.*
FROM emp
WHERE 1=1
)) more_records,
(SELECT CEIL(COUNT(*)/:b_PageSize)
FROM (SELECT emp.*
FROM emp
WHERE 1=1
)) total_pages
FROM (SELECT ROWNUM id,t1.*
FROM (SELECT
emp.*
FROM emp
WHERE 1=1
ORDER BY ename) t1
WHERE ROWNUM<:b_LastRec) t2
WHERE t2.id>:b_FirstRec
PL/SQL procedure successfully completed.
ID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO MORE_RECORDS TOTAL_PAGES
---------- ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ------------ -----------
11 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 0 3
12 7369 SMITH CLERK 7902 17-DEC-80 800 20 0 3
13 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 0 3
14 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 0 3
scott@ORA92>
scott@ORA92> SET AUTOTRACE ON EXPLAIN
scott@ORA92> SELECT t2.*,
2 (SELECT GREATEST((COUNT(*)-6)+1,0)
3 FROM (SELECT emp.*
4 FROM emp
5 WHERE 1=1)) more_records,
6 (SELECT CEIL(COUNT(*)/5)
7 FROM (SELECT emp.*
8 FROM emp
9 WHERE 1=1)) total_pages
10 FROM (SELECT ROWNUM id,t1.*
11 FROM (SELECT emp.*
12 FROM emp
13 WHERE 1=1
14 ORDER BY ename) t1
15 WHERE ROWNUM<6) t2
16 WHERE t2.id>0
17 /
ID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO MORE_RECORDS TOTAL_PAGES
---------- ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ------------ -----------
1 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 9 3
2 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 9 3
3 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 9 3
4 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 9 3
5 7902 FORD ANALYST 7566 03-DEC-81 3000 20 9 3
Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=5 Bytes=500)
1 0
VIEW (Cost=4 Card=5 Bytes=500)
2 1
COUNT (STOPKEY)
3 2
VIEW (Cost=4 Card=14 Bytes=1218)
4 3
SORT (ORDER BY STOPKEY) (Cost=4 Card=14 Bytes=560)
5 4
TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=560)
scott@ORA92> SET AUTOTRACE OFF
[Updated on: Wed, 06 April 2005 22:27] Report message to a moderator |
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Jun 28 20:02:44 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
