Home » Infrastructure » Unix » Performance issue in reading file in unix (SunOS 5.8, ORACLE 9i)
Performance issue in reading file in unix [message #471374] Fri, 13 August 2010 12:59 Go to next message
amit.sehrawat
Messages: 29
Registered: September 2009
Location: India
Junior Member

Hi,

I have extraction job, am running a shell script to extract the data from database and write to csv files.

I have divided the task into two operations:
1) extract account no. of each client from outfile.txt and write it into a temp table on some condition.
2) extract data from database for each client in database and put it on output file.

Operation 1 is taking 7 minutes for 1300 accounts, which is huge. although operation 2 is not taking much of time, hardly seconds.
my code for file reading is:
/*
cat input_file.txt | while read line
do
num_cnt=`echo $line`

ExtractClient $num_cnt

done


ExtractClient()
{
DATE_RS=`date +%y%m%d%H%M%S`

sqlplus -s "$sChaineConnect" << FINREQ > $DIR_SAVE_FILE/SQLResult.txt
insert into Migration_table(col1,col2,col3,col4) (SELECT col1, col2,col3,col4 from client where client.col1='$1' AND col3 IN(4,5));
commit;

exit;
/
FINREQ
FSQLError
}

*/


Someone please tell me how can i decrease this time?
Or there is any better idea of parsing file in UNIX and putting into database? or any idea, am out of options here Smile

Re: Performance issue in reading file in unix [message #471375 is a reply to message #471374] Fri, 13 August 2010 13:05 Go to previous messageGo to next message
BlackSwan
Messages: 21941
Registered: January 2009
Senior Member
invoking sqlplus once for every record is VERY inefficient!
use EXTERNAL TABLE & read data from file using SELECT which would only open sqlplus once!
Re: Performance issue in reading file in unix [message #471377 is a reply to message #471375] Fri, 13 August 2010 13:07 Go to previous messageGo to next message
amit.sehrawat
Messages: 29
Registered: September 2009
Location: India
Junior Member

I am novice to shell scripting could you give an code snippet as an example, it will really help.
Re: Performance issue in reading file in unix [message #471378 is a reply to message #471374] Fri, 13 August 2010 13:08 Go to previous messageGo to next message
ThomasG
Messages: 3064
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Another option would be using SQL*Loader
Re: Performance issue in reading file in unix [message #471379 is a reply to message #471377] Fri, 13 August 2010 13:09 Go to previous messageGo to next message
BlackSwan
Messages: 21941
Registered: January 2009
Senior Member
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6790030213850
Re: Performance issue in reading file in unix [message #471380 is a reply to message #471377] Fri, 13 August 2010 13:10 Go to previous message
Michel Cadot
Messages: 57609
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
external table.

Regards
Michel

[Updated on: Fri, 13 August 2010 13:10]

Report message to a moderator

Previous Topic: Issue with the Memory
Next Topic: Shell Script
Goto Forum:
  


Current Time: Fri Apr 18 05:48:23 CDT 2014

Total time taken to generate the page: 0.05690 seconds