Subscribe to DSC Newsletter

Code to run SQL queries 10 times faster than Brio, Toad etc. (for business analysts)

As a data scientist, I have helped business analysts write Oracle queries in batch mode run 20 times faster via Perl/Python scripts: instead of waiting for hours for Brio or Toad to return results in a browser (with a crash if the number of rows being returned was above 50,000), it took only minutes on a Unix station: I also spent 30 minutes providing basic Unix training to analysts so they could run and fix my Perl scripts alone. It was a win-win-win: for the business analyst, for executive management, and for myself.

One thing that helped a lot is the fact that my scripts would accept (as input) a SQL query (text file - something business analysts could easily and quickly produce with Brio or Toad and then export to the Unix station) and produced as output the results of the query (tab-separated text file) and another file about the success/failure of the query (with explanations as why the query failed, if it did, to help fix bugs in the SQL code). This made the life of the business analyst very easy. I also taught them about 20 important Unix/FTP commands that could solve all their Unix needs. 

Here's the code:

Or in plain text:

#!/usr/bin/perl -w
# name:
# desc: extracts a tab-delimited file from the specified database with the specified sql file as input
# usage: -s <user@db> -p <password>
# usage: use redirects for input sql statement and output text file
# $header$

use lib '/usr/pkg/perl/lib/oracle9.2.0';

use Getopt::Std;
use utf8;
use DBI;
use DBD::Oracle;

##/ initialize variables and command line parameters

my $debug = 1;
my %options;
&getopts('s:p:d:c:', \%options);
my $param = $options{'c'} || undef;
my $fieldSeparator = $options{'d'} || "\t";
my $connect_string = $options{'s'} || 'granvi@xyz-xxx-db'; # ie. 'user@db'
my $db_password = $options{'p'} || 'granvizzz';
my $db;

##/ test for input via STDIN or file argument

if (defined $db_password && -f $db_password) {
unless (open(IN,"$db_password")) {
print STDERR "Could not open file: $db_password";
while (<IN>) {
$db_password = $_;

##/ check syntax, output usage if invalid

#&printUsage() if (!exists $options{'s'});

# extract values from $connect_string

my ($db_user, $db_host) = split(/@/, $connect_string);
print STDERR "Connecting to $db_host as $db_user.\n" if $debug;;

# read SQL query from SQL file
my $sql_file=$ARGV[0];
if ($sql_file eq "") { $sql_file="query.sql"; }
open(IN,"<$sql_file") || die "SQL file does not exist\n";
while ($i=<IN>) { $sqlquery=$sqlquery.$i; }

# connect to db
$db = DBI->connect("dbi:Oracle:$db_host",$db_user,$db_password,{AutoCommit=>1,PrintError=>0}) ||
die "Could not connect to DB: $DBI::errstr";

print STDERR "Preparing...\n" if $debug;

unless ($db->do('alter session set current_schema=bi')) {
print STDERR "$DBI::errstr\n"

unless ($db->do("alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'")) {
print STDERR "$DBI::errstr\n"

$sth = $db->prepare($sqlquery) or die $db->errstr;

if (exists $options{'c'}) {
@params = split /,/,$param;
} else {

$refcolumns = $sth->{NAME};
print join($fieldSeparator, @$refcolumns), "\n"; ##- print OUT
while (@row = $sth->fetchrow_array) {
my @formattedResults;
for (my $i = 0; $i <= $#row; $i++) {
$_ = $row[$i];
if (not defined) {
push @formattedResults, '';
} else {
push @formattedResults, $_;
# s/^\s*(.*?)\s*$/$1/;
# s/\"/\"\"/g;
# push @formattedResults, "\"$_\"";

print join($fieldSeparator, @formattedResults), "\n"; ##- print OUT


Views: 7395

Replies to This Discussion

Would this be of use in Teradata?

Yes, you just need to use the right database driver in your Perl environment.


Follow Us

On Data Science Central

On DataViz

On Hadoop

© 2016 is a subsidiary and dedicated channel of Data Science Central LLC   Powered by

Badges  |  Report an Issue  |  Terms of Service