>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

I created a dummy table in MySql with 200k records:

0 Hits    1st 0.000000   Avg 0.000000   SET DEFAULT TO "c:\"
                                        
0 Hits    1st 0.000000   Avg 0.000000   SET COVERAGE TO myssqltest.log
1 Hit     1st 1.055072   Avg 1.055072   CREATE DATABASE MYSQLTEST
                                        CREATE CONNECTION mysql_connection ;
                                        		CONNSTRING "DRIVER={MySQL ODBC 5.1 Driver}; "+;
                                        		           "SERVER=localhost; "+;
1 Hit     1st 0.000409   Avg 0.000409   		           "UID=xxxx; PWD=xxxxx; DATABASE=xxxx; OPTIONS=19"
                                        *DBSETPROP('mysql_connection', 'Connection', 'Asynchronous', .T.)
                                        
                                        CREATE SQL VIEW "mytest_view" REMOTE CONNECT "mysql_connection" AS ;
1 Hit     1st 0.255146   Avg 0.255146          SELECT * FROM test WHERE field1 = ?cKey
                                        	
1 Hit     1st 0.001730   Avg 0.001730   CLOSE DATABASES
                                        
                                        
1 Hit     1st 0.000043   Avg 0.000043   set coverage to

SET DEFAULT TO "c:\"

SET COVERAGE TO myssqltest.log
CREATE DATABASE MYSQLTEST
CREATE CONNECTION mysql_connection ;
		CONNSTRING "DRIVER={MySQL ODBC 5.1 Driver}; "+;
		           "SERVER=localhost; "+;
		           "UID=xxxx; PWD=xxxxx; DATABASE=xxxx; OPTIONS=19"

CREATE SQL VIEW "mytest_view" REMOTE CONNECT "mysql_connection" AS ;
       SELECT * FROM test WHERE field1 = ?cKey
	
CLOSE DATABASES

set coverage to

As you see, no problems here. driver 5.01.05.00, MySql 5.0.67 How about you post the structure of your table so I can test with that?

Carlos