List:General Discussion« Previous MessageNext Message »
From:Daniel Brown Date:March 20 2008 6:20pm
Subject:Re: Optimize db update
View as plain text  
On Thu, Mar 20, 2008 at 1:41 PM, Velen <velen@stripped> wrote:
>
>  Actually I am updating TableA in DatabaseA with values from TableB in DatabaseB. 
> Database B is on a stand alone PC.  I'm using VB6 to create a .txt file containing data
> from TableB then using VB6 once more to recronstruct the table in DatabaseA then remove
> all data which are already in TableA and insert the remaining.
[snip!]
>  How can I optimise this process? and What are the alternatives available ?

    If you don't absolutely need to use VB6, why not use something
with native support like PHP?

<?
function dba_query($sql) { // Simply return the connection resource ID
        // Select the primary database....
        $dba_conn =
mysql_connect('hostname_a','username_a','password_a') or
die(mysql_error());
        $dba_db = mysql_select_db('database_a',$dba_conn);
        $r = mysql_query($sql,$dba_conn) or die(mysql_error());
        return $r;
}

function dbb_query($sql) { // Simply return the connection resource ID
        // Select the secondary database....
        $dbb_conn =
mysql_connect('hostname_b','username_b','password_b') or
die(mysql_error());
        $dbb_db = mysql_select_db('database_b',$dbb_conn);
        $r = mysql_query($sql,$dbb_conn) or die(mysql_error());
        return $r;
}

$sql = "SELECT field1,field2,field3,field4 FROM table_a";
$result = dba_query($sql) or die(mysql_error());
while($row = mysql_fetch_array($result)) {
    $ssql = "INSERT INTO table_b(field1,field2,field3,field4)
        VALUES(
            '".$row['field1']."',
            '".$row['field2']."',
            '".$row['field3']."',
            '".$row['field4']."'
        }";
    dbb_query($ssql) or die(mysql_error());
}
?>

    If you decide to go that route, I recommend subscribing to the
PHP-DB list at http://php.net/mailinglists (referred to there as
"Databases and PHP").  You should see a significant gain in
performance using a native client as opposed to what you're now using
(probably an ODBC DSN, MyODBC, or a JDBC hack).

-- 
</Daniel P. Brown>
Forensic Services, Senior Unix Engineer
1+ (570-) 362-0283
Thread
Optimize db updateVelen20 Mar
  • Re: Optimize db updateDaniel Brown20 Mar
    • Re: Optimize db updatePhil20 Mar
  • Re: Optimize db updateVelen21 Mar
    • Re: Optimize db updateDaniel Brown21 Mar
  • Re: Optimize db updateVelen21 Mar
    • Re: Optimize db updateDaniel Brown21 Mar
  • Re: Optimize db updateVelen22 Mar