How to reuse connection in shell

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

7 thoughts on “How to reuse connection in shell”

  1. This is why I’ll always prefer Korn shell to Bash, even though most *nix distributions have only the ancient ksh 88 rather than ksh 93.

    zsh seems to support coprocesses as well but I haven’t experimented with it.

  2. Hey, that’s a great tip! I’ll keep this idea in mind (and not just for the weekend..)

  3. Pingback: How to reuse connection in shell | Oracle

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>