Subversion Repositories DevTools

########################################################################
# Copyright (C) 1998-2012 Vix Technology, All rights reserved
#
# Module name   : jats_rm_play24.pl
# Module type   : Makefile system
# Compiler(s)   : n/a
# Environment(s): jats
#
# Description   : Experiment with invoking direct insertion into Release Manager
#                 This work really well
#                 Aggregate insertion statements to impove performance
#                 50,000 insertions in 90 seconds under windows
#                                      180 seconds under Linux (I am surprised)
#
#                 The size of the aggregation buffer is a key factor
#                 If its too big the process slows down
#                 Size: 30000 Windows: 90, Linux 180
#                 Size: 10000 Windows: 59, Linux 56
#                 Size:  5000 Windows: 82, Linux 53
#                 
#
#......................................................................#

require 5.006_001;
use strict;
use warnings;
use JatsError;
use JatsSystem;
use Getopt::Long;
use Pod::Usage;                             # required for help support
use JatsRmApi;

use DBI;

my $VERSION = "1.2.3";                      # Update this
my $opt_verbose = 1;
my $opt_help = 0;
my $opt_manual;
my $RM_DB;

my $m_sqlBase = "INSERT ALL";
my $m_sqlTail = " SELECT 1 FROM DUAL";
my $m_sqlMid = "";

#-------------------------------------------------------------------------------
# Function        : Main Entry
#
# Description     :
#
# Inputs          :
#
# Returns         :
#
my $result = GetOptions (
                "help+"         => \$opt_help,          # flag, multiple use allowed
                "manual"        => \$opt_manual,        # flag
                "verbose+"      => \$opt_verbose,       # flag
                );

#
#   Process help and manual options
#
pod2usage(-verbose => 0, -message => "Version: $VERSION")  if ($opt_help == 1  || ! $result);
pod2usage(-verbose => 1)  if ($opt_help == 2 );
pod2usage(-verbose => 2)  if ($opt_manual || ($opt_help > 2));


ErrorConfig( 'name'    =>'PLAY27' );

#$ENV{GBE_RM_LOCATION} = 'jdbc:oracle:thin:@auperaora07:1521:RELMANU1';
#$ENV{GBE_RM_USERNAME} = 'release_manager'; 
#$ENV{GBE_RM_PASSWORD} = 'release_manager';

$ENV{GBE_RM_USERNAME} = 'build_user[release_manager]'; 
$ENV{GBE_RM_PASSWORD} = 'frog123';

initInsert();
my $ii = 0;
for ($ii = 0; $ii < 50000; $ii++)
{
    directInject($ii);
}
runInsert();
exit;


#-------------------------------------------------------------------------------
# Function        : directInject 
#
# Description     : Create a partial SQL statement to do the insert
#
# Inputs          : $index          - Uniq number to differentiate the records
#
# Returns         : 
#

sub directInject
{
    my ($index) = @_;

    my $entry = " INTO release_manager.release_components ( pv_id, file_name, file_path, byte_size, crc_cksum, crc_modcrc ) " .
                " VALUES ( 1, 'fred', 'david".$index."/fred', 199, '12345', '67890')";
    $m_sqlMid .= $entry;

    # The size of the aggregation is key to performance
    # Too big is as bad as too small
    if (length($m_sqlMid) > 10000)
    {
        runInsert();
    }
}

#-------------------------------------------------------------------------------
# Function        : runInsert 
#
# Description     : Insert entries using the partial SQL statement 
#                   Must be called when the partial SQL buffer get large
#                   as well as at the end to fluch any outstanding inserts
#
# Inputs          : 
#
# Returns         : 
#
sub runInsert
{
    my $foundDetails = 0;
    my (@row);

    print("Index: $ii\n");
    if (length($m_sqlMid) > 0)
    {

        #
        #   Connect to the Database - once
        #
        connectRM(\$RM_DB, 0) unless $RM_DB;

        #
        #   Create the full Query
        #
        my $m_sqlstr = $m_sqlBase . $m_sqlMid . $m_sqlTail;
        my $sth = $RM_DB->prepare($m_sqlstr);
        if ( defined($sth) )
        {
            if ( $sth->execute() )
            {
#           print "--- Execute\n";
#           if ( $sth->rows )
#           {
#               print "--- Execute ROWS\n";
#               while ( @row = $sth->fetchrow_array )
#               {
#                   print "Data: @row\n";
#               }
#           }
#           print "--- Finish\n";
                $sth->finish();
            }
            else
            {
                Error("Execute failure: $m_sqlstr", $sth->errstr() );
            }
        }
        else
        {
            Error("Prepare failure" );
        }
    }
    $m_sqlMid = ""; 
}

#-------------------------------------------------------------------------------
# Function        : initInsert 
#
# Description     : Delete all entries with the PV_ID that I'm about to use
#
# Inputs          : 
#
# Returns         : 
#

sub initInsert
{
    my $foundDetails = 0;
    my (@row);


    #
    #   Connect to the Database - once
    #
    connectRM(\$RM_DB, 0) unless $RM_DB;

    #
    #   Create the full Query
    #
    my $m_sqlstr = "delete from release_components where pv_id=1";
    my $sth = $RM_DB->prepare($m_sqlstr);
    if ( defined($sth) )
    {
        if ( $sth->execute() )
        {
#           print "--- Execute\n";
#           if ( $sth->rows )
#           {
#               print "--- Execute ROWS\n";
#               while ( @row = $sth->fetchrow_array )
#               {
#                   print "Data: @row\n";
#               }
#           }
#           print "--- Finish\n";
            $sth->finish();
        }
        else
        {
            Error("Execute failure: $m_sqlstr", $sth->errstr() );
        }
    }
    else
    {
        Error("Prepare failure" );
    }
    $m_sqlMid = ""; 
}