Home » SQL & PL/SQL » SQL & PL/SQL » SQL Types and Procedure
SQL Types and Procedure [message #216045] Wed, 24 January 2007 17:33 Go to next message
raghu.nm
Messages: 5
Registered: October 2006
Location: US
Junior Member
Hello,
I have a performance problem in using SQLTypes.
find the below details

create table desc ( sku varchar2(100), description varchar2 (1000) );

create table sku_det (sku varchar2(100),manf_date date, comp_name varcha2(1000));

CREATE OR REPLACE
type skuType as object (sku varchar2(25));

CREATE OR REPLACE
type productType as table of skuType;

function contains_product (arrays in string_array) return productType as
product_data productType := productType();
begin
for i in 1 .. arrays.count loop
product_data.extend;
product_data(i) := orderbleProductType(arrays(i));
end loop;
return product_data;
end contains_product;

I am using JDBC to get string array type and using below query to get the reult set via procedure

select * from desc a, sku_det b ,(select sku from THE select cast( contains_product (string_array) as
mytableType ) from dual )) selec
where a.sku=b.sku and
a.sku=selec.sku.

this query is taking long time, if i pass a one sku.
Plz let me know how i can increase the performance of the query.

thanks in advance




Re: SQL Types and Procedure [message #216048 is a reply to message #216045] Wed, 24 January 2007 19:19 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
>I have a performance problem in using SQLTypes.
What is the business case for using TYPE?

What problem are you really trying to solve (besides homework)?
Re: SQL Types and Procedure [message #216220 is a reply to message #216048] Thu, 25 January 2007 12:05 Go to previous messageGo to next message
raghu.nm
Messages: 5
Registered: October 2006
Location: US
Junior Member
Hello,
Currently i am using JDBC to call Procedure where i am passing string array which contains 1... n number of sku's. If I use Prepared statement with IN operator of SQL query, will limit the number sku's to 1....999. SQL TYPE will solve this limitation. How ever excution time is more with SQLTYPE when compare to SQL query with IN operator.
Plz let me know how can i improve the performance.

thanks in advance,
Re: SQL Types and Procedure [message #216221 is a reply to message #216045] Thu, 25 January 2007 12:09 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
>Plz let me know how can i improve the performance.
Stop using JAVA & use PL/SQL inside the database!
Re: SQL Types and Procedure [message #216223 is a reply to message #216221] Thu, 25 January 2007 12:18 Go to previous messageGo to next message
raghu.nm
Messages: 5
Registered: October 2006
Location: US
Junior Member
i am using below procedure to get the result set via JDBC,
---- start
procedure proc_skuquery ( arrays in string_array,vcursor out SYS_REFCURSOR) as
myskuType skuType;
myProductType:=contains_product (arrays);
open vcursor for
select * from desc a, sku_det b ,(select sku from THE select cast( contains_product (string_array) as
mytableType ) from dual )) selec
where a.sku=b.sku and
a.sku=selec.sku;
end;
-- end

but if i use
---sql query ---
select * from desc a, sku_det b where a.sku=b.sku and a.sku in ('sku1','sku2');
----------------
is more faster but it limits the search for only 999. But if i use sqltype there is not limit on the search but with poor performance.





Re: SQL Types and Procedure [message #216260 is a reply to message #216045] Thu, 25 January 2007 16:23 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
another alternative might Global Temporary Table
icon7.gif  Re: SQL Types and Procedure [message #216351 is a reply to message #216260] Fri, 26 January 2007 14:54 Go to previous message
raghu.nm
Messages: 5
Registered: October 2006
Location: US
Junior Member
thanks ... this alternate works fine.
Previous Topic: How to retrive only Duplicate rows from table-reg
Next Topic: Function Based Index
Goto Forum:
  


Current Time: Wed Dec 07 14:38:22 CST 2016

Total time taken to generate the page: 0.14347 seconds