Hi Fellow monks, I have a script which with four sql statements getting the latest id, inserting into the main table and inserting into two other linked tables.
what is puzzling me is that i have two servers, one for testing and the other production. When i run the same script connecting to the testing server, it's super fast but when i ran it on the production server each sql takes a long time.
As the server is quite new and we didn't really configure it much, could there be something i need to tweak to run perl scripts?
Attached is a the namespace that i used, the connection opening function and some part of the main code, as it would been too long to paste the whole thing :) thanks!! 
use strict;
use Win32::ODBC;
use Win32::OLE;

#i call this to open the connection
sub OpenMTPDB
{
        my $Pwd = 'W*+h0rW/oU;';

        if ($Connection_Type eq 'P'){
                # Server=pgssqlea01; Database=MTPDB
                unless($db = new Win32::ODBC("dsn=MTPDB_P; UID=EAutoma
+tion; PWD=$Pwd")){
                        print "DB Connection Error: " . Win32::ODBC::E
+rror() . "\n";
                        exit;
                }
        }
        elsif ($Connection_Type eq 'D'){
                # Server=pgssqlea01; Database=MTPDB
                unless($db = new Win32::ODBC("dsn=MTPDB_D; UID=EAutoma
+tion; PWD=$Pwd")){
                        print "DB Connection Error: " . Win32::ODBC::E
+rror() . "\n";
                        exit;
                }
        }
        return $db;
}

# my main part of the code
#Get the FK_lot id
$sql = "SELECT TOP 1 IDX FROM ECAST_LOT ORDER BY IDX DESC ";
                    ## Only get the id once for one summary
                    if ($id eq ""){
                        print "GOTI IN\n";
                        if ($db->Sql($sql)){
                            print "Error executing the sql: $sql\n";
                            $isSqlFailed = 1;
                            last;
                        }
                        else{
                            my %temp;
                            while($db->FetchRow()){
                                %temp = $db->DataHash();
                                $id   = $temp{IDX};
                            }
                            print "ID: $id\n\n";
                        } #end if db->Sql
                    }
                    $id++;
                    
                    #Insert data into the Ecast_Lot table
                    $sql = <<SQL_USAGE;
                    INSERT INTO ECAST_LOT(LOT,OPERATION,SUMMARY,DEVREV
+STEP,DATETIME,TIU,TEST_PROGRAM,[TRIGGER],TOTAL_TESTED,TESTER_ID,HANDL
+ER_ID,COLOR)
                    values('$lot','$oper','$sum','$dev','$date','$tiu'
+,'$tp','$trigger','$ttested','$tester','$handler','$color')
SQL_USAGE
                    print "SQL: $sql\n";
                    if ($db->Sql($sql)){
                        print "Error executing the sql: $sql\n";
                        $isSqlFailed = 1;
                        last;
                    }

[download]

In reply to ActivePerl sql connection~PLEASE HELP!! by MelaOS

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.