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 <

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. William Robertson

    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. Chen Shapira

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

  3. Pingback: Log Buffer #147: a Carnival of the Vanities for DBAs | Pythian Group Blog

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

Comments are closed.