LinuxQuestions.org - script taking long time to execute

- Linux - Newbie (https://www.linuxquestions.org/questions/linux-newbie-8/)

- - script taking long time to execute (https://www.linuxquestions.org/questions/linux-newbie-8/script-taking-long-time-to-execute-858567/)

script taking long time to execute

Quote:

#!/bin/ksh

DBcounttry_finalnofunc()

{

cat logcountOP | while read LINE

TBLName=`echo $LINE|cut -d "-" -f1`

do

if [ $LINE != "" ]

then

printf "${TBLName}-" $TBLName

return_count=$(sqlplus -s a/a@adv5 <<EOF

set heading off feedback off pagesize 0 linesize 30000 trimout on;
whenever sqlerror exit 1;
whenever oserror exit 1;

select count (*) from ${TBLName};
exit 0;
EOF)

if [ $return_count -ge 0 ]
then
print "${TBLName}- ${return_count}" >> DBcountOP400
else
echo "$TBLName- is missing" >> DBcountOP400
fi

else
exit
#break
fi

the above is the code.sample of input file is:-

Quote:

ACCOUNT_MISSING_FRM_RCIS_LINK-4
ADP_COMMENT-2311
ADP_CONFIG-11
ADP_FIELD-36323
ADP_HEADER-1
ADP_INDEX-579
ADP_JOIN-14570
ADP_LANGUAGE-2
ADP_NATIVE_SQL-228
ADP_OBJECT-2405
ADP_OBJECT_NEW-2010
ADP_RELATION-4974
ADP_TBL_OID-838
ADP_TBL_OID_UNUSED-0
ADP_UPGRADE_OPS-0
ADP_VIEW_FIELD-43780
AUTHEN_NE_CON_BUS_PROD-0
BEFORE_CLNUP_TABLE_ADDRESS-135414
BEFORE_CLNUP_TABLE_SITE-135468

sample of output file is:

ACCOUNT_MISSING_FRM_RCIS_LINK- is missing
ADP_COMMENT- 2384
ADP_CONFIG- 11
ADP_FIELD- 36333
ADP_HEADER- 1
ADP_INDEX- 609
ADP_JOIN- 14572
ADP_LANGUAGE- 2
ADP_NATIVE_SQL- 228
ADP_OBJECT- 2405
ADP_OBJECT_NEW- is missing
ADP_RELATION- 4974
ADP_TBL_OID- 839
ADP_TBL_OID_UNUSED- 0
ADP_UPGRADE_OPS- 0
ADP_VIEW_FIELD- 43791
AUTHEN_NE_CON_BUS_PROD- is missing
BEFORE_CLNUP_TABLE_ADDRESS- is missing
BEFORE_CLNUP_TABLE_SITE- is missing

now my question is why the script is taking 5 mins to execute and let me say that the input log file has 1000 tables.so is it normal or there is some problem in the script.

How long is a piece of string? We know nothing about your hardware,
nothing about your database layout, indexes and table-sizes.

First thing you could do is to find out WHERE it spends its time.
Output time-stamps after each critical step.

Cheers,
Tink

It seems to me that the only part that would take any significant time is the separate sqlplus lookup for each line. Everything else is handled by shell built-ins or fast-working tools like cat and cut.

Is is possible to collect all the entries you want to look up and check them all in one single operation? Then loop through the results of that for your final printout instead? That would probably save you some processing time.

I'm no ksh user, but it does look like the code could be cleaned up a bit in a few places too. Instead of cut, you could use parameter substitution, for example.

Code:

TBLName=${LINE%-*}

And catting input data into a while loop using a pipe is generally bad form in bash, so maybe it is here too.

What exactly is the output of the sqlplus command anyway--the one stored in $return_count? Is "if [ $return_count -ge 0 ]" correct? Because only negative numbers would evaluate as false here, and in bash a null value will throw up an error.

Thanks Tinkster but please tell me how and what do I put so that it would give me the timestamp

Hi david

the sql command queries for rowcount of each table in DB and if the table does not exist,it says "is missing".yes the OP of sql query is being stored in $return_count.

so david what do I substitute if [ $return_count -ge 0 ]for? I ve been trying things but therez always a problem

What I wanted to confirm is what the actual value stored in $return_count is. -ge means "greater than or equal to", so a value of 0 also would be true. If you want to test for numbers "greater than" zero, use -gt.

If the output can be a text string, such as "is missing", then you shouldn't be using a unary operator at all. You need a string operator instead.

Code:

if [ "$return_count" = "is missing" ]

then

echo "$TBLName- is missing" >> DBcountOP400

else

print "${TBLName}- ${return_count}" >> DBcountOP400

fi

Note that I'm working from my experience in bash here though, so not everything I say may translate directly to ksh. I'm guessing that ksh doesn't throw up a unary error like bash does and simply evaluates to false, which is why it works for you.

But if $return_count can have the exact string value "is missing", then you don't really need the test at all, do you? Simply...

Code:

print "${TBLName}- ${return_count}" >> DBcountOP400

...should give you the proper format no matter what the value.