script with loop runs slow

[pauljt]

Hi,

I've thrown a script together which works ok, but it runs pretty slow. Without going into too greater detail - it searches all the stored procedures in a sybase database for a piece of text. (ie: a table name).

If you imagine the following example file exists by the time the script gets to the loop:

create proc A
create proc B
create proc C
create proc D
TESTDATA
create proc E
create proc F

The loop reads the file a line at a time, checks that
the line contains 'create proc' - if it does, then
it checks the next line to see if it contains the
search text. In this case, TESTDATA.
If these conditions are satisfied then the first line is
output. In this case:

create proc D

So, we would know that stored procedure D references
TESTDATA etc.

When running for real, there are over 5000 stored procs
and the process is quite slow.
I suspect what I have done is badly coded but would
appreciate any suggestions on how to improve it!

# for each line of file
line=0
while [[ $line -lt $numlines ]];do
# check if line contains Create Proc
isCreate=`awk NR==$line $scripthome/sp_text2.tmp | grep -i "Create Proc"`
let nextline=$line+1
echo $line/$numlines > $scripthome/sp.tmp
if [ "$isCreate" = "" ]
then
do=nothing
else
# check if next line is Create Proc, if not its our search text
isNextline=`awk NR==$nextline $scripthome/sp_text2.tmp | grep -i "Create Proc"`
if [ "$isNextline" = "" ]
then
# output name of stored proc
echo $isCreate | awk '{ print $3 }'
fi
fi
(( line += 1 ))
done

Cheers

Paul

 

[PHV]

Try something like this:

awk 'BEGIN{proc="?"}
{if(tolower($0)~/create proc/)proc=$3;next}
{print proc}
' $scripthome/sp_text2.tmp

Hope This Help
PH.

 

[Ygor]

You could do everything in one awk program...

awk '
/TESTDATA/ {print prev}
{prev=$0}
' $scripthome/sp_text2.tmp

 

[tikual]

How about sed command:

sed '$!N;s/^\(.*\)\nTESTDATA/\1/;t;D;' yourfile

tikual

 

[pauljt]

Thanks for replies,,,,

Couldn't seem to get the 1st and 3rd suggestions to work.
(using ksh)

However this works mostly:

awk '
/TESTDATA/ {print prev}
{prev=$0}
' $scripthome/sp_text2.tmp

except, rather than searching for TESTDATA each time,
the test will be for different strings.
I can't seem to work out how to pass in a var into the
above code...
eg: the equivalent of:

SearchString="TESTDATA"
awk '
/$SearchString/ {print prev}
{prev=$0}
' $scripthome/sp_text2.tmp

Thanks for any answer in advance!

 

[tikual]

I am not expert in awk. About the variable reason, let Ygor to answer you. I just knew that no "$" sign for variable in awk.

You may turn it to like these:
SearchString="TESTDATA"
awk '
match($0,SearchString) {print prev}
{prev=$0}
' $scripthome/sp_text2.tmp

tikual

 

[Ygor]

See FAQ:"Shell variables in awk" faq271-1281 from the awk forum

 

[PHV]

paultmisys, what is wrong with the 1st awk solution ?

 

[pauljt]

I'll revisit the suggestion and let you know!

 

[pauljt]

For me, the 1st solution just returns nothing at all.

 

[PHV]

Sorry for the typo:

awk 'BEGIN{proc="?"}
{if(tolower($0)~/create proc/){proc=$3;next}}
{print proc":"$0}
' $scripthome/sp_text2.tmp

Hope This Help
PH.