ODBC Queries via PHP extreemly slow, timeout.

October 6, 200421 yr

Newbies

FM7 advanced server running on a Microsoft small business server.

I am basically trying to run a SELECT query on certain records from the FMP7 db using ODBC scripted into PHP and INSERT them into a MySQL database.

All seems to be setup correctly and working fine, but this.

When I run the same query from Microsoft Excel it executes within milisec's.

ps. the php file executes on an apache webserver.

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

############# dbQuery.php #############

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

<html>

<head>

<title>DatabaseQuery</title>

</head>

<body>

<?php

# connect to a DSN "ECM_Product" with a user and password "Removed"

$connect = odbc_connect("ECM_Product", "Removed", "Removed");

# query the productInfo table for info

$query = "SELECT product, brand, inProducts, inSupport, site, Price FROM ECM_Product";

# perform the query

$result = odbc_exec($connect, $query);

# fetch the data from the database

$data = array();

for($x = 0; $x < odbc_num_rows($result); $x++) {

$data[] = odbc_fetch_array($result);

}

# close the connection

odbc_close($connect);

$connect = mysql_connect("Removed", "Removed", "Removed");

mysql_select_db("ecm_product");

mysql_query("TRUNCATE productinfo");

for($x = 0; $x < count($data); $x++) {

$product = $data[$x]["product"];

$brand = $data[$x]["brand"];

$inProducts = $data[$x]["inProducts"];

$inSupport = $data[$x]["inSupport"];

$site_id = $data[$x]["site"];

$price = $data[$x]["Price"];

mysql_query("INSERT INTO productinfo (product, brand, inProducts, inSupport, site_id, price) VALUES

('".$product."','".$brand."','".$inProducts."','".$inSupport."','".$site_id."','".$price."')");

echo mysql_error($connect);

}

mysql_close($connect);

</body>

</html>

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

Please help, deadline is coming close

3 weeks later...

October 25, 200421 yr

Hi there,

we are trying to accomplish the same. Were you able to solve the issue?

Do you have evaluation fields or calculation fields which are not indexed in the FM table that you want to query? That might cause to get a query slow (if I am not mistaken FM conducts a query via ODBC the same way it does when looking up records in client mode).

Greetings

5 months later...

April 12, 200520 yr

Any one ever get anywhere with this?

I'm still gettting a 30 sec time out for

select fiel1, field2 from table

when the result has > 10 rows.

April 19, 200520 yr

ODBC support on FileMaker is horrendous. Especially the presence of Summary Fields or Calculations in the table will bring the computer to its knees. It appears to require computing everything field for every record in the table even for a simple query such as yours.

I have concluded that unless one is quite knowledgeable about FileMaker internals and quite careful in the design, it is easy to get behavior that is exponential in time with the number of records.