Procedure I wrote and am trying to optimize

I would like to post a procedure that I wrote and see if someone may have a way to optimize what I have written. It is running extremely slow and is not really acceptable, in my opinion. I have a source query that I do a BULK COLLECT INTO a TYPE. Then with those values I loop through each record and set parameters to get an Address and I then set a bunch of variables and format the variables and ultimately I do an Insert into a table. There has got to be a better way of doing this. Maybe its the functions Ive created that take time to return, Im not sure. I would love to be able to do an Insert from Source to Destination and then maybe format/Update the table after the records are in the table? Any help would be great. I have attached the file to hopefully make it more readable. If you would rather me post it please just ask for anything you need. Im attaching the procedure but if you need to see the functions Im using just let me know.
Please help!!  This is my first package ive ever written and my first assignment at a new job so any help would seriously be greatly appreciated....Thanks everyone...
PCOV-Policy-V1.txt
jknj72Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
First quick scan through it...

First thing I would do is take the advice we have previously offered:
-Do away with in-memory tables.
-I'm still not sure why you can't use a Global Temporary Table.

What does the table TEMP_BASE_PCOV_POLICY look like?
If the columns are varchar2, why all the RPADS?
Make them CHAR and columns get right padded automatically.

At least make the variables CHAR for this reason:
declare
	junk1 varchar2(10) := 'Hello';
	junk2 char(10) := 'Hello';
begin
	dbms_output.put_line(':' || junk1 || ':');
	dbms_output.put_line(':' || junk2 || ':');
end;
/

Open in new window


All these can be combined:
 l_COV_ISSUING_STREET := CLEAN_NON_ASCII(l_POLICY_TAB(i).CARR_ISSUING_STREET);    
                IF LENGTH(l_COV_ISSUING_STREET) > 50 THEN
                  l_COV_ISSUING_STREET := SUBSTR(l_COV_ISSUING_STREET, 1, 50);
                ELSE
                  l_COV_ISSUING_STREET := RPAD(l_COV_ISSUING_STREET, 50, ' ');
                END IF;

Something like:
rpad(SUBSTR(CLEAN_NON_ASCII(l_POLICY_TAB(i).CARR_ISSUING_STREET, 1, 50),50,' ');
0
Mark GeerlingsDatabase AdministratorCommented:
Instead of reviewing what you gave written (which appears to be overly-complex) I would like to focus on the task you are trying to do, and find the simplest way to do that.  It looks like that is mainly:
"do an Insert from Source to Destination" and a secondary task:  "then maybe format/Update the table after the records are in the table".

To do "an Insert from Source to Destination" you don't need PL\SQL and: a query, a bulk collect, a type, a loop, etc.  The simplest (and fastest) way to do this is usually in simple SQL like this:
insert into [target_table]
(optional list of columns)
select [whatever you need]
from [wherever the data is now]

In some case, if you need to do complex data manipulations, conversions, validations. etc., it may be necessary to use PL\SQL and/or a global temporary table to hold intermediate data.

Can you describe the task you are trying to do?  Then we can help you determine the best approach to the problem.  That may not involve PL\SQL at all.

I hope your goal is not trying to design complex solutions to problems.  I hope your goal is trying to solve business problems in the simplest possible manner.  That is what we will try to help you do.
0
jknj72Author Commented:
Mark, I don't see why you would ask  "I hope your goal is not trying to design complex solutions to problems.  I hope your goal is trying to solve business problems in the simplest possible manner?"

 Why in the world would I want to do that? I am in fact here asking this question to solve my problem in the simplest manner. Not sure what other reason I would be here?

My goal is simple. I have to get data from a number of tables and with certain logic, rules and data requirements in place into a destination table.

I am relatively new to Oracle. Ive written statements in the past but this is the first time I've written a package and the procedures that run the code. I don't even know how I can get it to run in a certain sequence when I'm done but I will cross that road when I come to it..

All I was asking is maybe there are some better techniques that I can use instead of what I currently have in place.

Slight, you commented that I shouldn't use the In Memory processes and I actually started to move in that direction in the beginning of this project but I had a hard time with it so I went back to the original approach, which is what the other packages that I've looked at are using and this is what I came up with. Also, there are 3 of these tables that Im creating with 3 packages but this is the main one. The other 2 read from the table Im Inserting into that I sent in this question as the source so it is local and runs very fast. Its this first one that is taking the majority of the time.  Im new here at this job so I figured it may be best to complete the task and then try to optimize it the best I can.  The old task takes 8 hours and mine takes 2 hours so its definitely an improvement but Id like to get the whole process under an hour. I will definitely take your advice with the padding and changing types to Char. With that said, I actually was just given the ability to debug and for the past 2 days I have gained so much momentum because I now know what were some of my issues. So now that I have these capabilities I can change some of the code and will be able to tell if Im doing it correctly in a very timely fashion.
Slight, when you mentioned using the Global Temp tables. How would I use them? I am assuming to Insert the Source query data to a Temp table and read, manipulate and format the data from that table then do an Insert?  The codes all there I just want to use the best methods of moving the data around for my needs. Thanks
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
Don't be that hard on Mark.  He might not have seen the series of questions that lead up to this one.  We have quite a few members that join and expect the Experts to write all the code for them for their membership payments.  Basically very cheap contractors...

Global Temporary Tables are special tables in Oracle that are created once and used over and over.  Only the session that inserts into them can see the data and once the session ends, the data is automatically purged.

The data can also be purged on a commit depending on how you create them.

As long as the subsequent processing is done in the same session, I would look into them.

Even if you don't use them, you will gain a little performance on the regular table if you created it NOLOGGING.  This means no redo is generated for DML on them.  The downside is they aren't recoverable in a crash but since this is a temp table, it shouldn't matter!

The docs have what you need on them and there are several papers/blogs out there that talk about them.

>>which is what the other packages that I've looked at are using

Just because everyone else jumps off a bridge...  ;)

>>but I had a hard time with it so I went back to the original approach,

So, when you took off the training wheels and skinned your knees, did you put them back on?

When you stumbled, you should have asked us!

My gut feeling is that ALL of the core logic can probably be done with simple SQL.

OK, maybe not all that simple but at least all in SQL and no PL/SQL logic.

The inserts into the MSG table might be an issue and still likely require logic.

An explicit cursor like I showed you in a previous question will be better than the in-memory table and can include MOST if not ALL of the padding/cleaning/etc... logic.

Then you can do your individual checks and log 'bad' data.

Also pretty sure the address logic can be simplified.

All that said:
We'll need simple test cases that simulate your setup.  Make them as simple as possible and still representative of what you need.  Sorry but we cannot understand the entire big picture...   That is unless you hire some of us (there is a Hire Me button in some of our profiles...  but don't look in mine...).

Table structures, sample data and expected results.  This way we can create the same thing on our end and provide tested code. If the logic isn't obvious in the data, a brief explanation will help and save some time.
0
jknj72Author Commented:
Understood. Didn't mean to offend, maybe I took it the wrong way..
0
Mark GeerlingsDatabase AdministratorCommented:
My comment here was based on this question by itself.  I did not assume that this question was connected or related to any possible previous questions on this site.  When you told us that this is the first PL\SQL package that you have written, and that this is a new job for you, I was concerned.  You asked for help on improving this package, but this is just one possible tool (or approach) to solving a problem.  Since we don't have a complete understanding of what the business problem is, we don't know that this package is the best way to solve the problem.

I wanted to take step back and have you give us a high-level description of the problem.  Then we can much more effectively decide if using a PL\SQL package like this is the best method to use for solving this problem.  Otherwise, we can spend time and energy on tweaking your package, but only later realize we should have taken a totally-different approach to the problem.

When I was learning this line of work on-the-job many years ago (I have no college training related to computers or programming) I had a mentor who encouraged me to always try to find three possible ways of solving the problem, consider the pros and cons of each, then choose the best (or in some cases, the least-worst) option.

In this case, slightwv and I both mentioned direct SQL inserts and a global temporary table, since we have both used those options for business problems that (to us at least) sound similar to what you are trying to do.  Have you explored that possibility and determined that it is not a good option for your business problem?
0
jknj72Author Commented:
Its actually not a business problem, we are just trying to improve the time that it takes to get this process completed. I am re-writing the existing program that generates the tables that are being picked up by a vendor of ours to do what they have to do.

Mark...
>>When I was learning this line of work on-the-job many years ago (I have no college training related to computers or >>programming) I had a mentor who encouraged me to always try to find three possible ways of solving the problem, >>consider the pros and cons of each, then choose the best (or in some cases, the least-worst) option.

I really like what your mentor said to you...I would have loved that kind of direction by a senior guy when I was starting off. My mentor was the worst. He would cause problems with all the database guys(I was a front end developer when I started out) and I was attached to him , like his sidekick, and it made for very uncomfortable situations. If he didn't get his way he would cry and b1tch until he got his way, very childish. He was a used car salesman when he wasn't programming and he used coding techniques that were very outdated and would fight against using something new that he didn't know. He got me some good jobs though, throughout the years, but his ego always got in the way and surprisingly enough they always ended up firing him and keeping me(at least 3 positions I can think of). I worked with him at my last position and they got rid of him shortly after I got hired..

I've helped people that Ive worked with and spoke to them years later and they always bring up that I was able to teach then in such a simple manner that  they still use the same techniques I taught them 10 years ago. I love helping people and feel I missed my calling and should have been a teacher. I actually help people here at work and they now all come to me with questions because I am always willing to help.

But Annnnnnway, sorry for being a chatty Kathy!!

The program runs a query and BULK COLLECTS INTO a TYPE table variable and loops through the records. I set variables and the size of the vars are truncated or padded(which I will change Slight..). I bypass records that don't have appropriate values or Dates in some of my checks. I also do a build of a key with a lot of the vars being concatenated. That key will be used in my next processes so I can join the tables later on. I use the table being built in this process as the source for my other processes.  
 
I then have to check tables for an appropriate address to use with this Policy record. There is a sequence of tables to check to get the Address needed for the Insert(Only 1 address for each Policy).  First table is the ENDORSE query(Using the Bulk Collect again) with params from the policy and if there is a record, I go through the checks and finally do the insert. The Address logic is not really complex but there are many checks(checking dates and querying tables for values via functions) and formatting before the Insert. The second address check(if no return value from check1) is from the INSURED_LOCATION table and there is an ADDTYPE that we check and if its a 2 that's the preferred Type, so we use that one, if not we use ADDType 1. After final vars are set then we do the Insert.

I am about to leave work for a weekend getaway so I wanted to at least respond to you guys before I left.
With the main logic I just explained when and where would you use the Temp table? I'm not asking for anyone to write the code for me, Ill never learn that way, but if you could maybe steer me in a better direction Id appreciate it.

Also if you need table structures or function code just let me know and I can send you a script. I may have to do that Monday though.

Thanks guys and have a great weekend!!
0
slightwv (䄆 Netminder) Commented:
I bet you don't need any BULK-COLLECT.

IMHO, The main reason for using them is if you have a small-ish result set that you need to use multiple times or need to have random access.

Looks like you are only making one pass through the results.  this tells me, no need for it and a cursor (implicit or explicit) will be better.

>> I use the table being built in this process as the source for my other processes.  

You never answered:
Will these other processes run in the same session or will they have their own database connection/session?

That will decide a Global Temp table or regular table.

Still say nologging on the regular table...

>>not really complex but there are many checks(checking dates and querying tables for values via functions)

Can they all not be done in SQL?

Since I don't fully understand the 'logic', I'll dummy something up to show the point.

Set up small examples to work things out...

In the sample below:  

If the address type in tab1 is 1, use add1 table.  If 2, use add2 table.

drop table tab1 purge;
drop table add1 purge;
drop table add2 purge;

create table tab1(some_id number, col1 char(1), add_type number);
insert into tab1 values(1,'a',1);
insert into tab1 values(2,'b',2);
insert into tab1 values(3,'c',1);

create table add1(some_id number, street1 varchar2(10), street2 varchar2(10));
insert into add1 values(1,'add1 str1','add1 str2');

create table add2(some_id number, street1 varchar2(10), street2 varchar2(10));
insert into add2 values(2,'add2 str1','add2 str2');
commit;


select t1.col1,
	case t1.add_type when 1 then a1.street1  when 2 then a2.street1 end street1,
	case t1.add_type when 2 then a1.street2  when 2 then a2.street2 end street2
from tab1 t1
	left outer join add1 a1 on t1.some_id=a1.some_id
	left outer join add2 a2 on t1.some_id=a2.some_id
/

Open in new window


As long as you join the tables properly, you can get as complex as you want in the SQL to return the correct values.

>>I bypass records that don't have appropriate values or Dates in some of my checks

That is what a WHERE clause is for...  include or exclude rows from the results.

>> I also do a build of a key with a lot of the vars being concatenated. That key will be used in my next processes so I can join the tables later on

I have nothing here...  that is because I don't understand the 'big picture' but I bet this probably isn't needed either (only a small bet in case I lose...).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
(No, I didn't see slightwv's latest comment before I posted this,  I edited this comment after I had posted what follows, when I noticed his comment.)

"we are just trying to improve the time that it takes to get this process completed"

I consider that a business problem, or at least a business challenge or opportunity.  It looks like this task is basically:
1. Select some information (from the COVERAGE and CARR tables)
2. Perform some validations and/or transformations on the selected data
3. Collect and format/transform the related address information
4. Insert the results (into the TEMP_BASE_PCOV_POLICY)

In theory, this kind of task can be handled most efficiently by an:
  insert into ...
  select ...
  from ...
   where...
statement.  This avoids the PL\SQL overhead.  I haven't taken the time to review all of the validations and data transformations in your package, so I can't say for sure in this case that PL\SQL could be avoided here.  But, that is always my first approach: try to get the job done in SQL unless doing the job in a one-step SQL statement becomes overly-complex to write and maintain, and/or slow to execute.  Only then do I consider the various PL/SQL options.

I will add that I am definitely not a fan of CHAR variables or columns!  In Oracle, they are usually a waste of resources.  In one of our apps that needs fixed-length values, I partition the big historical tables by month, and compress the previous month's data early in each new month.  That allows us to reduce the size of the historical data by at least 95%!

I only use CHAR columns or variables for non-Oracle applications that use an Oracle database and require/expect fixed-length column values.  Is that your situation?  That is, the app that uses the results of your process requires fixed-length values?  Even then, I would explore creating a view that presents the info to the app in fixed-length format, but allows Oracle to store and use the smaller, and more-efficient variable-length values in the database.  And, if you need to support updates from an app that requires fixed-length records, you could explore using an "instead of" trigger on the view to support those updates, while still eliminating all of the trailing spaces in the database.
0
slightwv (䄆 Netminder) Commented:
>>I will add that I am definitely not a fan of CHAR variables or columns!

Columns, I agree 100%.  Variables, I'm on the fence.

Since you aren't storing the values permanently, and want right padded values, I'm not sure why you wouldn't use them as variables instead of rpad'ing them when necessary.  Well, unless you have millions of rows in memory (hint hint BULK COLLECT!!!!!).

Anyway, this should be an academic point since we both agree there should be VERY MINIMAL variable use here.

>>Is that your situation?

Sounds like it from the comment:  "generates the tables that are being picked up by a vendor of ours to do what they have to do"

I'm guessing this is part of a larger ETL workflow.


>>No, I didn't see slightwv's latest comment before I posted this

hehe...  happens to ALL of us!!!
0
slightwv (䄆 Netminder) Commented:
I'm curious.

What all did you end up doing and did you get it down to an hour or less?
0
jknj72Author Commented:
Well, I finished the code that I had sent to this thread and was able to validate everything I needed and the only errors I get are NO_DATA_FOUND, so they are legit errors and are being captured and everything is working fine. I used advice from both you and Mark, hence the split of points, but with the help of the debugger(and your advice, even from earlier questions/answers), I did an Insert into a staging table with the idea that keeping a connection to another server/tables open and doing the BULK COLLECT INTO a TYPE were not optimal. So I run pretty much the same query but Inserting it into a Stage table and then opening up the, now local, table using a FOR LOOP. I still have BULK COLLECTS INTO in the address portion but thinking on changing that as well. It is getting run for every record in the source FOR LOOP so I think using another FOR LOOP would be better than loading a TYPE for every row. What do you think? I'm wondering if there is anything else I can do to speed up the process(index the Stage table) or do you think the global temp table would speed up the process?...Maybe I will ask another question about it
Although I feel much better about the new code I have just finished I have not noticed a significant difference in the processing time. Its definitely a little faster but it still is taking  about 2 hours to finish. I eventually would like to reduce the amount of code by trying to get this into an Insert statement with the majority of the logic being performed within the statement(CASE WHENs and WHERE clause) is possible. I still think I will have to do an Update to the table but I will see what I can come up with.
Thanks again for your help!!
0
slightwv (䄆 Netminder) Commented:
Thanks for the update.

I'm still thinking there is no need for any of the staging tables (either in-memory, global temp tables or regular tables).

I still think all  the address logic can be simplified if not moved completely to native SQL.

Keeping an index on a staging table can be problematic because of the updates for each row.  If an index would help with a query of the staging table, try building it AFTER you have loaded all the data.

Indexes help with random and specific access on a table.  If you query the staging table based on specific rows, then an index might help.

If you access ALL the rows only once, no index will help and will only take time to create/maintain.
0
jknj72Author Commented:
yeah I think your right. I indexed the table and didn't find any increase in the time it takes to run.
I hate to sound stupid but when you say
"I still think all  the address logic can be simplified if not moved completely to native SQL."
What exactly do you mean by that? Im going to try and get all the address logic figured out and then do an Insert with everything included, without the Update to the table. If you have any tips please feel free to send them to me.

Thanks
John
0
jknj72Author Commented:
Im gonna ask  question about some of the logic of the Insert Im gonna try and do
0
slightwv (䄆 Netminder) Commented:
>>What exactly do you mean by that?

From the quick scan of the logic, I think all the checks to return the 'correct' address values can be moved into the BIG select with CASE logic.

I'm thinking the entire thing can be one large
insert into ... select ...

Unless you really need the tracking inserts, then one large FOR LOOP with very little actual pl/sql code.
0
jknj72Author Commented:
Ok I was just writing the question. I have my select statement and I am going to put that after my insert and put what I have in a question and let you see where im at and any advice you could give me would be great...Will post question soon...Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.