Hi Alec,
Why did you set Asynchronous to .T.?
MartinaJ
>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
