LinuxQuestions.org - speeding up script by opening and closing DB connection just once

- Linux - Newbie (https://www.linuxquestions.org/questions/linux-newbie-8/)

- - speeding up script by opening and closing DB connection just once (https://www.linuxquestions.org/questions/linux-newbie-8/speeding-up-script-by-opening-and-closing-db-connection-just-once-857848/)

speeding up script by opening and closing DB connection just once

Hi all

I have a script which

takes a file as input and reads it in while loop
cuts the name of table and stores in a variable
opens DB connection and queries for rowcount in that table
closes the connection
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:

Code:

#!/bin/ksh



cat logcountOP | while read LINE



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





do



if [ $LINE != "" ]



then





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;

EOF)



if [ $return_count -ge 0 ]

then

print  "${TBLName}" "${return_count}" >> DBcountOP4

else

echo "$TBLName- is missing" >> DBcountOP4

fi



else

#exit

break

fi







done > DBcountOP3

and the input file looks like :

Code:

ACCOUNT_MISSING_FRM_RCIS_LINK-4

ADP_COMMENT-2311

ADP_CONFIG-11

ADP_FIELD-36323

ADP_HEADER-1

ADP_INDEX-579

ADP_JOIN-14570

ADP_LANGUAGE-2

ADP_NATIVE_SQL-228

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!!!

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?

jlinkels

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

Code:

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

Code:

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.

Quote:

Originally Posted by smritisingh03 (Post 4233321)

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.