LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
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!

Notices


Reply
  Search this Thread
Old 01-21-2011, 03:10 PM   #1
smritisingh03
Member
 
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:

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

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

Rep: Reputation: 635Reputation: 635Reputation: 635Reputation: 635Reputation: 635Reputation: 635
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
 
Old 01-22-2011, 08:15 AM   #3
tronayne
Senior Member
 
Registered: Oct 2003
Location: Northeastern Michigan, where Carhartt is a Designer Label
Distribution: Slackware 32- & 64-bit Stable
Posts: 3,476

Rep: Reputation: 1020Reputation: 1020Reputation: 1020Reputation: 1020Reputation: 1020Reputation: 1020Reputation: 1020Reputation: 1020
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.
 
Old 01-28-2011, 08:28 PM   #4
archtoad6
Senior Member
 
Registered: Oct 2004
Location: Houston, TX (usa)
Distribution: MEPIS, Debian, Knoppix,
Posts: 4,727
Blog Entries: 15

Rep: Reputation: 233Reputation: 233Reputation: 233
Quote:
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.
 
  


Reply


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 08:28 PM
cpu is closing and opening on it's own fakhruddinhd Linux - Software 3 09-06-2006 10:10 AM
Opening and closing ports stormtracknole Slackware 10 01-11-2006 09:29 AM
opening/closing window sounds matt_com911 Fedora 1 06-18-2005 10:17 PM
Opening/Closing Ports on Debian 3.0 addowen Linux - Newbie 0 04-22-2004 07:43 PM


All times are GMT -5. The time now is 08:10 PM.

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