Well the ODBC data source I am connecting to is kind of a mess and I'm sure the speed could be a problem just with the way I'm connecting. It's a pvx odbc driver that I had to bridge to, because there is no unix client support with the crappy database. I use easysoft's OOB (demo) right now to do some test.
To query one row with
SELECT column0 FROM table WHERE coulmn1 = 'var';
and if it's a database with 29,000 lines it will take about 4 minutes.
The biggest problem I have is some databases are 300,000+ some are even up to 6 million rows. So it takes a long time for those ones.
now yes I am trying to make this quicker by moving these databases to mysql. Problem is I would have to do it every night to keep the information up to date. I wish we could just make our own software rather than use this other crappy pvx one. However I'm not the boss.
so I've made something like this.
$result = "select * from $odbctable";
$data = odbc_exec($odbclink, $result);
while ($row = odbc_fetch_array($data))
foreach ($row as $odbcinfo)
$mysqlinfo = $mysqlinfo.", '".addslashes($odbcinfo)."'";
$insertsql = "INSERT INTO ".$odbctable." (".$odbccolumns.") VALUES (".$mysqlinfo.")";
$mysqlinfo = "";
so this takes about 3.5 minutes to do for a database with 29,900 rows it doesn't actually start putting that data in until about 1 or so minutes after i've submitted the information. I was wondering what was slowing it down. Now I'm sure it's because it is getting all the information from the query before it will start to submit. Now it's safe to say that getting the information from two different datasource managers, one local, and one on the other side of the bridge in windows, will have something to do with that.
But is there anyway to make this quicker? I've set the bridge to may different fetch sizes to see if that would help. Nothing yet.
Better yet is there already a program that can export ODBC databases to mySQL that can be setup to run everynight. Is this a sad little dream that I have and should never expect it to be possible in the 12 hour night that I have to get it done in?