Home » SQL & PL/SQL » SQL & PL/SQL » Help required in optimizing the query response
icon6.gif  Help required in optimizing the query response [message #215827] Wed, 24 January 2007 00:51 Go to next message
shyamh
Messages: 13
Registered: January 2007
Junior Member
Hi,
I have a requirement to select two columns from 6 million rows which is one database, and go through the result set, take the one of the column value as the criteria and select another column from 10000 rows table.

I have used two methods to work. Both are working but they are very slow.

I am using jdbc for this.
First method:

Following is the pseudo code for the same

--Select all rows from the table 1
while (rs.next())
{
String column1= rs.getString(1);
String Query 2= "Select column2 from table 2 where my_field=column1";
--prepare the statement1 using query2
-- execute the statement1 and copy in to rs1
-- while (rs1.next())
{
do something
}
}

Second method:
In this one instead of querying the database for each row in the first resutl set, used a stored procedure which returns a ref cursor taking the array as the input string.

create or replace type mylist as table of varchar2(20);
/
create or replace type outlist as table of BLOB;
/

create or replace package my_pkg
as
type rc is ref cursor;
procedure p(myval_list in mylist, outcur OUT rc);
end;
/

create or replace package body my_pkg
as
PROCEDURE get_phonetype(myval_list IN mylist,orefcur OUT rc )
AS
l_data outlist := outlist();
tmp_profile BLOB;
BEGIN
--DBMS_OUTPUT.put_line(myval_list.COUNT);
FOR i IN myval_list.FIRST .. myval_list.LAST
LOOP
BEGIN
SELECT column1 INTO tmp_profile FROM table1 WHERE
column2 IN (SELECT column2
FROM table2
WHERE (column3< myval_list(i) AND column4 > myval_list(i)));

--DBMS_OUTPUT.put_line(tmp_phone);
l_data.extend;
l_data(i) := tmp_profile;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
END LOOP;
open outcur for
select * from TABLE( cast (l_data as outlist));
END;
END;
/

in the java program:
--Select all rows from the table 1
while (rs.next())
{
--loop few times to populate the column1 string arrays


OracleCallableStatement stmt1=(OracleCallableStatement)conn.prepareCall("{call p(?,?)}");
-- register the out parameter as ORACLETYPES.CURSOR
--prepare the array descriptor for the array, set the array using column1 values
-- set the first argument as array
-- retrieve the result set
--do something with the result set
} //end of while


I thought using the stored procedure would make my process faster, but the second approach deteriated the execution time.
I could not understand where I did the mistake. FOr the 200000 rows for the first table, first approach executed in 10 minutes and the second was taking more than hours.

Can some one help me in fine tuning the stored procedure or suggest an alternative best method.

Regards,
Syam
Re: Help required in optimizing the query response [message #215942 is a reply to message #215827] Wed, 24 January 2007 06:13 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Hi,
it does not seem to me you are making the same steps. at least the given sql commands differ (however i am not good in Java, so maybe it is hidden somewhere).
Maybe using single select in your pl/sql procedure could help:
create or replace package body my_pkg
as
PROCEDURE get_phonetype( myval_list IN mylist, outcur OUT rc )
AS
BEGIN
open outcur for
SELECT column1 FROM table1 t1, table2 t2, table( myval_list ) t3
WHERE t1.column2 = t2.column2 AND
t2.column3 < t3.column_value AND t2.column4 > t3.column_value;
END get_phonetype;
END my_pkg;
/
Re: Help required in optimizing the query response [message #216050 is a reply to message #215942] Wed, 24 January 2007 19:32 Go to previous messageGo to next message
shyamh
Messages: 13
Registered: January 2007
Junior Member
Hi,
thanks for the response. when I modified the sql query I am getting wrong result set. And the same value (which is wrong) is being returned for values in the different indexes of table (myval_list).


I did not understand which sql commands look differ.

My query is to select a column for table1, where another column in the same table shall match the value as the result of second range query. i.e. the range query will fetch a varchar2 which then will be used as a criteria to select column from table1.

The changes in the query somehow changing the data output.
Regards,
Syam
Re: Help required in optimizing the query response [message #216132 is a reply to message #216050] Thu, 25 January 2007 03:28 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Hi,
I do not have any idea what 'wrong result set' may mean. So I created a little testcase:
create table table1
(
  column1 varchar2(10),
  column2 integer
);

create table table2
(
  column2 integer,
  column3 integer,
  column4 integer
); 

insert into table1( column1, column2 ) values( 'AAA', 1 );
insert into table1( column1, column2 ) values( 'BBB', 2 );
insert into table1( column1, column2 ) values( 'CCC', 3 );
insert into table1( column1, column2 ) values( 'DDD', 4 );
insert into table1( column1, column2 ) values( 'EEE', 5 );
insert into table1( column1, column2 ) values( 'FFF', 6 );

insert into table2( column2, column3, column4 ) values( 1, 10, 20 );
insert into table2( column2, column3, column4 ) values( 1, 50, 60 );
insert into table2( column2, column3, column4 ) values( 2, 20, 30 );
insert into table2( column2, column3, column4 ) values( 2, 60, 70 );
insert into table2( column2, column3, column4 ) values( 3, 30, 40 );
insert into table2( column2, column3, column4 ) values( 3, 70, 80 );
insert into table2( column2, column3, column4 ) values( 4, 10, 30 );
insert into table2( column2, column3, column4 ) values( 4, 50, 70 );
insert into table2( column2, column3, column4 ) values( 5, 20, 40 );
insert into table2( column2, column3, column4 ) values( 5, 60, 80 );
insert into table2( column2, column3, column4 ) values( 6, 10, 50 );
insert into table2( column2, column3, column4 ) values( 6, 50, 90 );
commit;

SQL> SELECT column1 FROM table1 t1, table2 t2, table ( num_ary (15) ) t3
  2  WHERE t1.column2 = t2.column2 AND
  3  t2.column3 < t3.column_value AND t2.column4 > t3.column_value;

COLUMN1
----------
AAA
DDD
FFF

SQL> SELECT column1 FROM table1 t1, table2 t2, table ( num_ary (25) ) t3
  2  WHERE t1.column2 = t2.column2 AND
  3  t2.column3 < t3.column_value AND t2.column4 > t3.column_value;

COLUMN1
----------
DDD
FFF
BBB
EEE

SQL> SELECT column1 FROM table1 t1, table2 t2, table ( num_ary (45) ) t3
  2  WHERE t1.column2 = t2.column2 AND
  3  t2.column3 < t3.column_value AND t2.column4 > t3.column_value;

COLUMN1
----------
FFF

SQL> SELECT column1 FROM table1 t1, table2 t2, table ( num_ary (15, 45) ) t3
  2  WHERE t1.column2 = t2.column2 AND
  3  t2.column3 < t3.column_value AND t2.column4 > t3.column_value;

COLUMN1
----------
AAA
DDD
FFF
FFF

Does not seems wrong to me. If you mind double FFF in the last example, add distinct keyword. Otherwise either state 'correct values' for this testcase to return or provide values which produce 'wrong result set' (or demonstrate your own testcase).
Re: Help required in optimizing the query response [message #216479 is a reply to message #216132] Sun, 28 January 2007 18:39 Go to previous messageGo to next message
shyamh
Messages: 13
Registered: January 2007
Junior Member
Hi,
Thanks for the response. I think you were right, I was not clear when I said wrong result set.
Your little test case did explain that a lot.
What I observe was, the result set was being sorted irrespective of the values stored in the table. For example, I have changed the values in the num_ary and expected that 45 value will be used first to generate the result set, but the result was same irrespective of the values in the num_ary table.

SQL> select column1 from table1 t1, table2 t2, table( num_ary(45,15)) t3
2 where t1.column2 = t2.column2 AND
3 t2.column3 < t3.column_value AND t2.column4 > t3.column_value;

COLUMN1
----------
AAA
DDD
EEE
FFF
FFF

SQL>

with your data schema, I would expect, 45 value first generate the EEE first and then AAA for the 15 which satisfies the query.
How shall I not sort the list, and get the result set using the values in the table in the same sequence as inputted?

Secondly, I observed that the total process time is coming around 17 minutes for the 200000 rows of table 1 and 8000 rows of table2 and 1000 array list for table 3. Is this normal? I see that maximum amount of latency is happening at the stored procedure response?
Regards,
Shyam
Re: Help required in optimizing the query response [message #216480 is a reply to message #215827] Sun, 28 January 2007 18:49 Go to previous messageGo to next message
shyamh
Messages: 13
Registered: January 2007
Junior Member
Just an addition, I have inserted value
insert into table2(column2,column3,column4) values (5, 20, 70);

where as you use (5, 20, 40).
that is the reason why my query is returning EEE. I changed this just to show the difference and what I am expecting.
Regards,
Syam
Re: Help required in optimizing the query response [message #216657 is a reply to message #215827] Mon, 29 January 2007 17:39 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
hi,
result sets of sql queries are unsorted unless you specify it. However in your case you want to order by "order" in list, so you should extend members of mylist with order value (see usage example here).
Maybe the former functional solution (method2) could satisfy, however I would change it to a PIPELINED function returning outlist (not using l_data anymore).
As for performance, it is hard to tell. Index on table1.column2 would help a lot. Also having up-to date statistics is good.
Re: Help required in optimizing the query response [message #216676 is a reply to message #216657] Mon, 29 January 2007 21:15 Go to previous messageGo to next message
shyamh
Messages: 13
Registered: January 2007
Junior Member
Hi,
I did change the stored procedure to pipedline function. But the performance has not improved.
I still get better performance with my jdbc code without using any stored procedure.

I am having index on my table1.column2, and having index on the table2.column3 and table2.column4.

The stored procedure returning cursor is more efficient than the pipedline function.

Not sure why my query is taking so much time. Could this because I am having table of type blob which is my column1?

I still need to collect the statistics.
Regards,
Syam
Re: Help required in optimizing the query response [message #216678 is a reply to message #215827] Mon, 29 January 2007 21:28 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Here is what we (TINW) know.
You have a performance problem.
Here is what we don't know.
OS name & version
Oracle version to 4 decimal places.
Your actual code attempts.
The actual table definitions.
Any EXPLAIN_PLAN.
You claim to have created indexes, but indexes don't always help.
An index on a column which only contains "M" or "F" is a WASTE of space.

At a minimum enable SQL_TRACE at level 12, run the results thru TKPROK & post the results back here.

[Updated on: Mon, 29 January 2007 21:29] by Moderator

Report message to a moderator

Re: Help required in optimizing the query response [message #216901 is a reply to message #216678] Tue, 30 January 2007 16:42 Go to previous messageGo to next message
shyamh
Messages: 13
Registered: January 2007
Junior Member
Hi,
I am using SUN OS 9.0 and oracle9i release 9.2.0.7.0.

I am having the following table schema:

SQL> desc ntms40_phonetypes;
Name Null? Type
----------------------------------------- -------- ----------------------------
PHONETYPE NOT NULL VARCHAR2(100)
DESIGNATION_TYPE VARCHAR2(100)
PROPERTIES BLOB
STATIC_TERMINAL_PROFILE BLOB
PNP NUMBER(1)
X_WAP_PROFILE VARCHAR2(100)
DIRTY SQL> desc ntms40_phone_imei_ranges
Name Null? Type
----------------------------------------- -------- ----------------------------
PHONETYPE NOT NULL VARCHAR2(100)
START_IMEI NOT NULL VARCHAR2(100)
END_IMEI NOT NULL VARCHAR2(100) NOT NULL NUMBER(1)


I have created the following nested table type:

create or replace type imeilist as table of varchar2(20);
/

create or replace package my_pkg
as
type rc is ref cursor;
procedure get_phonetype(myval_list in imeilist, outcur OUT rc);
end;
/

CREATE OR REPLACE package body my_pkg
AS
PROCEDURE get_phonetype(myval_list IN imeilist,orefcur OUT RC )
IS
BEGIN
Open orefcur for
SELECT STATIC_TERMINAL_PROFILE FROM NTMS40_PHONETYPES a, ntms40_phone_imei_ranges b, table(myval_list) c
WHERE a.phonetype=b.phonetype AND (b.start_imei < c.column_value AND b.end_imei > c.column_value);

END;
END;
/

Plan:
I need to select the phonetype from ntms40_phone_imei_ranges table for the imei value falling in the start_imei and end_imei range. After selecting the phonetype, I want to use this phonetype value as the criteria to select static_terminal_profile from ntms40_phonetypes table.
To acheive that , I am giving the imei values as array from the java class, and each imei value in the array needs to retreive one row in the ntms40_phone_imei_ranges table.

I am attaching the sql_trace for the execution I have done. I was inputting an array of 1000 elements and returning the cursor.

Can you please let me know where was the process taking too much of time? Or suggestions to improve the performance.
Thanks,
syam


  • Attachment: output.txt
    (Size: 47.42KB, Downloaded 168 times)

[Updated on: Tue, 30 January 2007 16:44]

Report message to a moderator

Re: Help required in optimizing the query response [message #216902 is a reply to message #215827] Tue, 30 January 2007 16:57 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
SELECT STATIC_TERMINAL_PROFILE 
FROM
 NTMS40_PHONETYPES A, NTMS40_PHONE_IMEI_RANGES B, TABLE(:B1 ) C WHERE 
  A.PHONETYPE=B.PHONETYPE AND (B.START_IMEI < C.COLUMN_VALUE AND B.END_IMEI > 
  C.COLUMN_VALUE)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       58      0.01       0.00          0          0          0           0
Execute     59      0.06       0.09          1          3          0           0
Fetch     5774    255.01     275.60      11001      13746        174       57179
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     5891    255.08     275.70      11002      13749        174       57179

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 30     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
   1000  HASH JOIN  
    914   TABLE ACCESS FULL NTMS40_PHONETYPES 
   1000   MERGE JOIN  
   6691    SORT JOIN 
   6691     TABLE ACCESS FULL NTMS40_PHONE_IMEI_RANGES 
   1000    FILTER  
4434588     SORT JOIN 
   1000      COLLECTION ITERATOR PICKLER FETCH 

If there is not an index on a.phonetype, there should be 1.
Since no data is being returned from NTMS40_PHONE_IMEI_RANGES B, TABLE(:B1 ) C they should be eliminated out of the FROM clause
& subordinated into the WHERE clause using EXISTS
Re: Help required in optimizing the query response [message #216905 is a reply to message #216902] Tue, 30 January 2007 17:51 Go to previous messageGo to next message
shyamh
Messages: 13
Registered: January 2007
Junior Member
Hi,
I have the unique index on a.phonetype,
SQL> select index_name, uniqueness from user_indexes where table_name='NTMS40_PHONETYPES';

INDEX_NAME UNIQUENES
------------------------------ ---------
NTMS40_INDEX_PHONETYPE_PNP NONUNIQUE
NTMS40_INDEX_PHONETYPE_X_WAP NONUNIQUE
NTMS40_PRIMARY_PHONETYPE UNIQUE
SYS_IL0000026893C00003$$ UNIQUE
SYS_IL0000026893C00004$$ UNIQUE

Also I changed the query as suggested by you.
CREATE OR REPLACE package body my_pkg
AS
PROCEDURE get_phonetype(myval_list IN imeilist,orefcur OUT RC )
IS
BEGIN
Open orefcur for
SELECT STATIC_TERMINAL_PROFILE FROM NTMS40_PHONETYPES a
WHERE EXISTS (SELECT phonetype from ntms40_phone_imei_ranges b, table(myval_list) c where a.phonetype=b.phonetype AND (b.start_imei < c.column_value AND b.end_imei > c.column_value));

END;
END;
/

But Still I see no improvement. Is my query formation is wrong?
Regards,
Syam

[Updated on: Tue, 30 January 2007 17:54]

Report message to a moderator

Re: Help required in optimizing the query response [message #216906 is a reply to message #215827] Tue, 30 January 2007 18:00 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
No promises!
SELECT STATIC_TERMINAL_PROFILE FROM NTMS40_PHONETYPES a
WHEREa.phonetype in (select b.phonetype 
                                   from ntms40_phone_imei_ranges b
                                   where exists ( select '1' from table(myval_list) c 
                                                        where b.start_imei < c.column_value AND b.end_imei > c.column_value
                                                      )
                                  );

Re: Help required in optimizing the query response [message #216907 is a reply to message #216906] Tue, 30 January 2007 18:34 Go to previous messageGo to next message
shyamh
Messages: 13
Registered: January 2007
Junior Member
Hi,
thanks for the help. I could not see any improvement and also some of the result set has not returned back from the query to my java process.

I tried something like below using the ROWNUM
SELECT STATIC_TERMINAL_PROFILE from NTMS40_PHONETYPES a, ntms40_phone_imei_ranges b,
(SELECT myval_list(ROWNUM+1) myvalue from table (myval_list)) c
where a.phonetype=b.phonetype and (b.start_imei< c.myvalue and b.end_imei>c.myvalue);

This query ran fast, may be because I am always selecting the first row of the table.
but is there a possibility for me to use the selection in this way (i.e. using rownum) and at the same time increment the rownum so that I can get the values in the sequence i.e. 1..1000.

Regards,
Syam

Re: Help required in optimizing the query response [message #216908 is a reply to message #215827] Tue, 30 January 2007 18:44 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
please post the EXPLAIN_PLAN for my query.
Re: Help required in optimizing the query response [message #216915 is a reply to message #216908] Tue, 30 January 2007 19:56 Go to previous messageGo to next message
shyamh
Messages: 13
Registered: January 2007
Junior Member
Hi,
Here is the explain plan for the query you have suggested.
ID PARENT OPERATION OBJECT_NAME
---- ------ ----------------------------------- ------------------------------
0 SELECT STATEMENT
1 0 HASH JOIN SEMI
2 1 TABLE ACCESS FULL NTMS40_PHONETYPES
3 1 VIEW
4 3 MERGE JOIN
5 4 SORT JOIN
6 5 TABLE ACCESS FULL NTMS40_PHONE_IMEI_RANGES
7 4 FILTER
8 7 SORT JOIN
9 8 COLLECTION ITERATOR PICKLER F

Regards,
Syam
Re: Help required in optimizing the query response [message #216916 is a reply to message #215827] Tue, 30 January 2007 20:42 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
> 6 5 TABLE ACCESS FULL NTMS40_PHONE_IMEI_RANGES
On the surface it does NOT appear that any index on this table is being used.
Re: Help required in optimizing the query response [message #216922 is a reply to message #216916] Tue, 30 January 2007 21:38 Go to previous messageGo to next message
shyamh
Messages: 13
Registered: January 2007
Junior Member
Hi,
I am hving the following indexes on this table.
NTMS40_PHONE_IMEI_RANGES NTMS40_IND_PHO_IM_RANG_COMB 1 START_IMEI
NTMS40_PHONE_IMEI_RANGES NTMS40_IND_PHO_IM_RANG_COMB 2 END_IMEI
NTMS40_PHONE_IMEI_RANGES NTMS40_IND_PHO_IM_RANG_EN_IMEI 1 END_IMEI
NTMS40_PHONE_IMEI_RANGES NTMS40_IND_PHO_IM_RANG_LPAD 1 SYS_NC00004$
NTMS40_PHONE_IMEI_RANGES NTMS40_IND_PHO_IM_RANG_LPAD 2 SYS_NC00005$
NTMS40_PHONE_IMEI_RANGES NTMS40_IND_PHO_IM_RANG_ST_IMEI 1 START_IMEI
NTMS40_PHONE_IMEI_RANGES NTMS40_IND_PHO_IM_RANG_PHONE 1 PHONETYPE

When I use the query with start_imei< value and end_imei> value, does it not use the index?
Shall I need to create any specific index if that needs to be hit for this kind of query?
Regards.
Syam
Re: Help required in optimizing the query response [message #219775 is a reply to message #216922] Thu, 15 February 2007 23:14 Go to previous message
shyamh
Messages: 13
Registered: January 2007
Junior Member
Hi,
I have changed the query as below giving the index hints. I could see from the tkprof that the indexes are being used to access. But this query is taking some 10 minutes to retrieve the data from the database, for 200000 rows in the input collection array.

I would like to make this work bit more efficient, Can any one suggest me how I can acheive that?

Please find the query I am using and the tkprof trace for the same.

create or replace type imeilist as table of varchar2(20);
/
create or replace type profileArray as table of BLOB;

/

create or replace package my_pkg
as
type rc is ref cursor;
procedure p(myval_list in mylist, outcur OUT rc);
end;
/

CREATE OR REPLACE package body my_pkg
AS
PROCEDURE get_phonetype(myval_list IN imeilist,orefcur OUT RC )
AS
l_data profileArray := profileArray();
ssql varchar2(500);
BEGIN
ssql := 'SELECT /*+ INDEX (NTMS40_PHONETYPES NTMS40_PRIMARY_PHONETYPE) */'
|| 'STATIC_TERMINAL_PROFILE FROM NTMS40_PHONETYPES WHERE PHONETYPE IN '
|| '(SELECT /*+ FIRST_ROWS(1) INDEX (NTMS40_PHONE_IMEI_RANGES NTMS40_IND_PHO_IM_RANG_COMB)'
|| '(NTMS40_PHONE_IMEI_RANGES NTMS40_IND_PHO_IM_RANG_PHONE) */'
|| 'PHONETYPE FROM NTMS40_PHONE_IMEI_RANGES WHERE start_imei < Mad AND END_IMEI > :y and rownum<2)';

FOR i in 1 .. myval_list.COUNT
LOOP
BEGIN

l_data.extend;
execute immediate ssql into l_data(l_data.count) using myval_list(i), myval_list(i);

EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
END LOOP;
open orefcur for select * from table (cast (l_data as profileArray));
END;
END;
/

  • Attachment: tkprof.txt
    (Size: 4.27KB, Downloaded 130 times)
Previous Topic: simple program taking 2 hrs to execute..plz help
Next Topic: Displaying records in Pages
Goto Forum:
  


Current Time: Thu Dec 08 22:23:50 CST 2016

Total time taken to generate the page: 0.08573 seconds