Help answer threads with 0 replies.
Go Back > Forums > Linux Forums > Linux - Newbie
User Name
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!


  Search this Thread
Old 01-21-2011, 02:10 PM   #1
Registered: Nov 2010
Posts: 43

Rep: Reputation: 0
speeding up script by opening and closing DB connection just once

Hi all

I have a script which
  1. takes a file as input and reads it in while loop
  2. cuts the name of table and stores in a variable
  3. opens DB connection and queries for rowcount in that table
  4. closes the connection
  5. reads another file and then follows step 1 through 4 till EOF is reached.

Now this script is taking longer to execute maybe because the DB connection is opened and closed each time.

My scripts is:


cat logcountOP | while read LINE

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


if [ $LINE != "" ]


printf  "${TBLName}-" $TBLName

return_count=$(sqlplus -s a/a@xyz5   <<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;

if [ $return_count -ge 0 ]
print  "${TBLName}" "${return_count}" >> DBcountOP4
echo "$TBLName- is missing" >> DBcountOP4


done > DBcountOP3
and the input file looks like :

I was thinking of changing this.I want to open the DB connection just once and then query it for all tables and then exit.

I think that would expedite my script.

Any suggestions in this regard is appreciated!!!

Last edited by Tinkster; 01-28-2011 at 07:58 PM. Reason: replaced QUOTE tags with CODE tags
Old 01-22-2011, 07:04 AM   #2
Senior Member
Registered: Oct 2003
Location: Bonaire
Distribution: Debian Wheezy/Jessie/Stretch/Sid, Linux Mint DE
Posts: 4,634

Rep: Reputation: 713Reputation: 713Reputation: 713Reputation: 713Reputation: 713Reputation: 713Reputation: 713
That is not possible in Bash.

You could either use another script language to achieve that (php, tcl, perl) because their API allow the use of a handle for an opened database. Bash does not. Or I think that it would also be possible to implement the loop part of the script in SQL. In MySQL it would be possible to put the list of tables in a temporary table and let MySQL loop thru it using the native macro language.

Is this a script which has to run often? Is it worth, or is it just an academic excercise?

Old 01-22-2011, 07:15 AM   #3
Senior Member
Registered: Oct 2003
Location: Northeastern Michigan, where Carhartt is a Designer Label
Distribution: Slackware 32- & 64-bit Stable
Posts: 3,541

Rep: Reputation: 1060Reputation: 1060Reputation: 1060Reputation: 1060Reputation: 1060Reputation: 1060Reputation: 1060Reputation: 1060
It may be useful to try something a little different.

Instead of querying from your input file, maybe change your shell program so that it reads each table name and writes an SQL query line into a file until you've run out of table names then execute the DBMS with that SQL file as input.

Where your file would look something like
select tabname, count from taba;
select tabname, count from tabb;
select tabname, count from tabc;
Now that's simplified, but pretty much that's what you'd want to do (and, you know, add whatever criteria you want to the select).

Then your shell program would
create an empty file (with > file.sql or similar)
open input text file
while read table name
     append query into file.sql
close input text file
connect to DBMS and execute file.sql
Hope this helps some.
Old 01-28-2011, 07:28 PM   #4
Senior Member
Registered: Oct 2004
Location: Houston, TX (usa)
Distribution: MEPIS, Debian, Knoppix,
Posts: 4,727
Blog Entries: 15

Rep: Reputation: 233Reputation: 233Reputation: 233
Originally Posted by smritisingh03 View Post
I was thinking of changing this.I want to open the DB connection just once and then query it for all tables and then exit.
Is there any chance that file descriptors would allow this in bash?

Otherwise the "write the query file in bash" approach sounds interesting.


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
detection opening/closing port qnguyendang Linux - Networking 5 12-15-2008 07:28 PM
cpu is closing and opening on it's own fakhruddinhd Linux - Software 3 09-06-2006 09:10 AM
Opening and closing ports stormtracknole Slackware 10 01-11-2006 08:29 AM
opening/closing window sounds matt_com911 Fedora 1 06-18-2005 09:17 PM
Opening/Closing Ports on Debian 3.0 addowen Linux - Newbie 0 04-22-2004 06:43 PM > Forums > Linux Forums > Linux - Newbie

All times are GMT -5. The time now is 02:40 PM.

Main Menu
Write for LQ is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration