Subversion Repositories DevTools

########################################################################

# Copyright (c) VIX TECHNOLOGY (AUST) LTD

#

# 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 = ""; 

}