It is Friday, I wanted to give my readers some stuff for the week-end

Imagine that piece of code :

countlines() {
  c=$(sqlplus -s /nolog <<EOF | grep -v Connected
connect scott/tiger
set feed off head off
select count(*) from $1;
EOF
)
  echo there are $c lines in $1
}

countlines EMP
countlines DEPT

I can run this

time ./script1
there are 14 lines in EMP
there are 4 lines in DEPT

real    0m0.46s
user    0m0.06s
sys     0m0.09s

Sounds like a regular shell script. How could we optimize the countlines function? Well, we could create the connection only once and use coprocess pipe (with |& that is pipe ampersand)

sqlplus -s /nolog |&

print -p "connect scott/tiger"

read -p line
if [ $line != Connected. ]
then
  exit 1
fi

print -p "set feed off head off"

countlines() {
  print -p "select count(*) from $1;"
  read -p c
  echo "there is $c lines in $1"
}

countlines EMP
countlines DEPT

print -p disconnect

A two-ways pipe is opened with sqlplus. There is only one connect, and one disconnect.

Let’s check the performance :

$ time ./script2
there is 14 lines in EMP
there is 4 lines in DEPT

real    0m0.23s
user    0m0.00s
sys     0m0.01s

About twice as fast! Note the “Connected” output may not exist in recent version of sqlplus in silent mode. If you have a script that generates hundreds of connections, or which create a connection every 5 seconds or so, think about it

Enjoy your week-end