Insert from Array into MySQL takes time...

[paruby]

Hello, I hope there is a quick answer, but probably not....

I am creating a MySQL DB that pulls data first from an MSSQL DB, drops it into an Array, then from there gets put into the MySQL table. It seemed to be taking a while, so I added in timers at various stages of the process, and this is what I came up with:

1. Connect to the MSSQL DB, then pull data from the MSSQL DB based on a query, then inserts the data into an array. THere are over 17,000 records in the MSSQL DB, and approx 530 are returned by the query. - This takes 0.5 sec - var name = $time1
2. Disconnect from MSSQL DB, Connect to MySQL DB - I did not time this piece.
3. I then delete all records from the MySQL table that I will be inserting the aray into. This is var name $time2, and takes 0.05 seconds
4. I then insert into the MySQL table the approx 530 records into the MySQL table. This takes approx 24 seconds! This is var name $time3.

So my question is this, how can the pull w/ a query from 17,000 MSQL records take 1/2 sec, but an insert w/ 530 records (1 column!) take almost 50x as long?

Code is below ~ am I doing something wrong??

THANX!

[code]
        $AssignTo = "Name"
    $time_start1 = microtime(true);

    $dbcnx1 = mssql_connect("server1", "user", "pass");
    if (!$dbcnx1) {
        print "<P>Unable to connect to the CCare DB Server at this time.</P>\n";
        exit();
    }
    else   {
        mssql_select_db("DBName", $dbcnx1);
        print "<P>Connection to the MSSQL DB server successful!</P>\n";
    }

    
    $result3 = mssql_query("SELECT * FROM nxstar_e WHERE assigned_to = '".$AssignTo."'") or die("Error selecting Cases: ".mssql_error());

    $q = 0;
    $CaseArr = array();    // create array

    while ( $row = mssql_fetch_array($result3) )   {         // Gather case info, create array
            $tar_num = $row["tar_num"];

        $CaseArr[$q] = array($tar_num);
           $q++;
    }


    $time_end1 = microtime(true);
    $time1 = $time_end1 - $time_start1;

    print "<p>Copying from SQLDB to Array took: $time1 second(s)</p>";

    mssql_close($dbcnx1);

    include("conn.inc");  // Connect to MySQL DB

    $time_start2 = microtime(true);

        $result4 = mysql_query("DELETE From Cases") or die("Error deleting from Cases: ".mysql_error());

    $time_end2 = microtime(true);
    $time2 = $time_end2 - $time_start2;

    print "<p>Deleting from MySQLDB PRUBY_Cases took: $time2 second(s)</p>";

    $time_start3 = microtime(true);

    for ($i = 0; $i < $q; $i++)  {       // Insert into PRUBY_Cases
            $result5 = mysql_query("INSERT INTO MyCases (tar_num) VALUES ('".$CaseArr[$i]{0}."')") or die("Error inserting in MyCases: ".mysql_error());
        }

    $time_end3 = microtime(true);
    $time3 = $time_end3 - $time_start3;

    print "<p>Copying to MySQL DB from Array took: $time3 second(s)</p>";[/code]

[wickning1]

You're performing 530 communications.. that's a lot of overhead. Try bundling it up into one query. You just need to do some string manipulation and use the "INSERT INTO table(col1, col2) VALUES ('data1','data2'),('data3','data4')" syntax.

[XenoPhage]

[!--quoteo(post=352394:date=Mar 6 2006, 11:36 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 6 2006, 11:36 PM) [snapback]352394[/snapback][/div][div class=\'quotemain\'][!--quotec--]
You're performing 530 communications.. that's a lot of overhead. Try bundling it up into one query. You just need to do some string manipulation and use the "INSERT INTO table(col1, col2) VALUES ('data1','data2'),('data3','data4')" syntax.
[/quote]

And, if this is a one-time insert, you may want to make sure that there are no indexes on the data.. (If you use a single insert, I believe there's a way to ignore indexes until the insert is complete.. I forget the exact syntax tho)

[paruby]

Thanx for the help guys. I trimmed down my code so that I completely bypassed the Array. I just select from the MSSQL DB, then in a while loop, insert each into the MySQL DB. But that alone did not trim the time. I changed my table type from InnoDB to MyISAM. Now what ook 25 seconds is taking under 1/2 sec. Which leads to another question - What is the differnce between those 2 table types that would make an insert statement run so much slower?

[wickning1]

InnoDB appears to go faster because it uses insert buffering. Basically it updates the index after it sends a reply saying the insert was successful. Updating the index is what makes inserts take as long as they do. Under InnoDB, the database continues to work after you think you're done.

Choosing properly between MyISAM and InnoDB is somewhat involved. I suggest you stick with MyISAM until you know the difference.

Now, back to the matter at hand, you SHOULD be preprocessing your input values and concatenating them all into the same string. Only ONE query is required, not 530. Your insert query should look something like this:

[code]INSERT INTO table (columnname) VALUES
('row1'),
('row2'),
('row3'),
(...)[/code]