In a previous post I asked about a problem with remote views to a MySQL database. The issue is that it is taking a long time to get them created and the more records the backend table has the longer it takes. It is as if a SELECT is being executed upon the creation of the remote view. In an attempt to clarify the issue here is some sample code that references a table with 200K rows:

SET COVERAGE TO myssqltest.log
CREATE DATABASE MYSQLTEST
CREATE CONNECTION mysql_connection ;
		CONNSTRING "DRIVER={MySQL ODBC 5.1 Driver}; "+;
		           "SERVER=127.0.0.1 ; "+;
		           "UID=myuser; PWD=mypswrd; DATABASE=mytest; OPTIONS=19"
DBSETPROP('mysql_connection', 'Connection', 'Asynchronous', .T.)

CREATE SQL VIEW "mytest_view" REMOTE CONNECT "mysql_connection" AS ;
       SELECT * FROM mycfstble WHERE cfs_index = ?skey
	
CLOSE DATABASES

Here is the coverage profiler log of that same code:

0 Hits    1st 0.000000   Avg 0.000000   SET COVERAGE TO myssqltest.log
1 Hit     1st 0.011180   Avg 0.011180   CREATE DATABASE MYSQLTEST
                                                        CREATE CONNECTION mysql_connection ;
                                        		CONNSTRING "DRIVER={MySQL ODBC 5.1 Driver}; "+;
                                        		           "SERVER=127.0.0.1 ; "+;
1 Hit     1st 0.003781   Avg 0.003781   		           "UID=myuser; PWD=mypswrd; DATABASE=mytest; OPTIONS=19"
1 Hit     1st 0.000070   Avg 0.000070   DBSETPROP('mysql_connection', 'Connection', 'Asynchronous', .T.)
                                        
                                                       CREATE SQL VIEW "mytest_view" REMOTE CONNECT "mysql_connection" AS ;
1 Hit     1st 129.328417 Avg 129.328417        SELECT * FROM mycfstble WHERE cfs_index = ?skey
                                        	
1 Hit     1st 0.011386   Avg 0.011386   CLOSE DATABASES 

BTW, once the view is created it works as expected. It is also worth mentioning that going against an MS-SQL backend on the same machine the creation of the view is less than 1 second.

Any ideas on why the creation of a remote view would take 1.5 minutes?

Thx

Alec