Hi all!

Creating a remote view connected to MySQL data take a very long time to =

complete,

as if the code to create the view is actually causing a SELECT to be =

performed on the MySQL backend database.

Sample code:

create sql view "MyView" remote connection "MySqlConnection" as ;

select * ;

from MyTable ;

where MyCode =3D ?vp_code

The creation of the remote view takes a long time and the amount of time =

it takes

seems to be proportional to the amount of data in the backend table.

The same operations connecting to MS-SQL data are almost istantaneous.

Anyway, once created, these views work as they should.

Anyway SPT access to MySQL data is fast.

All tests are done locally.

Does this behavior sound familliar to anyone?

Does anyone have any suggestions?

My system is as follow:

Windows XP Pro SP3

VFP 9 SP1

MySQL Server 5.1

MySQL ODBC 5.1 Connector

MySQL ODBC 3.51 Connector

MS SQL Server 2008

SQL Server Native Client 10.0

--=20

Gianni

Gianni Turri wrote:

> Hi all!

>

> Creating a remote view connected to MySQL data take a very long time to complete,

> as if the code to create the view is actually causing a SELECT to be performed on the MySQL backend database.

>

> Sample code:

>

> create sql view "MyView" remote connection "MySqlConnection" as ;

> select * ;

> from MyTable ;

> where MyCode = ?vp_code

>

> The creation of the remote view takes a long time and the amount of time it takes

> seems to be proportional to the amount of data in the backend table.

>

> The same operations connecting to MS-SQL data are almost istantaneous.

>

> Anyway, once created, these views work as they should.

>

> Anyway SPT access to MySQL data is fast.

>

> All tests are done locally.

>

> Does this behavior sound familliar to anyone?

> Does anyone have any suggestions?

Yes, I remember having similar problems. Check the log on the server to see the

queries being sent. Because of multiple problems with remote views (and local views)

in VFP, I went with a 100% SPT solution. You can easily make SPT cursors updateable,

getting you everything a remote view gives you. See:

ftp://leafe.com/makeupdatable.prg

Paul

On Tue, 03 Feb 2009 09:17:14 -0800, Paul McNett <p@ulmcnett.com> wrote:

>Gianni Turri wrote:

>> Hi all!

>>=20

>> Creating a remote view connected to MySQL data take a very long time =

to complete,

>> as if the code to create the view is actually causing a SELECT to be =

performed on the MySQL backend database.

>>=20

>> Sample code:

>>=20

>> create sql view "MyView" remote connection "MySqlConnection" as ;

>> select * ;

>> from MyTable ;

>> where MyCode =3D ?vp_code

>>=20

>> The creation of the remote view takes a long time and the amount of =

time it takes

>> seems to be proportional to the amount of data in the backend table.

>>=20

>> The same operations connecting to MS-SQL data are almost istantaneous.

>>=20

>> Anyway, once created, these views work as they should.

>>=20

>> Anyway SPT access to MySQL data is fast.

>>=20

>> All tests are done locally.

>>=20

>> Does this behavior sound familliar to anyone?

>> Does anyone have any suggestions?

>

>Yes, I remember having similar problems. Check the log on the server to =

see the=20

>queries being sent. Because of multiple problems with remote views (and =

local views)=20

>in VFP, I went with a 100% SPT solution. You can easily make SPT cursors=

updateable,=20

>getting you everything a remote view gives you. See:

>

>ftp://leafe.com/makeupdatable.prg

Hi Paul,

thanks for your reply.

Would be my intention to use remote views in some places.

The problems with remote views you mentioned are only with MySQL or even =

with MS SQL?

Can you give me details about the problems you've encountered?

--=20

Gianni

Gianni Turri wrote:

> Hi Paul,

> thanks for your reply.

>

> Would be my intention to use remote views in some places.

>

> The problems with remote views you mentioned are only with MySQL or even

with MS SQL?

>

> Can you give me details about the problems you've encountered?

I'd steer clear of the remote views if you could. You could download the

Piecerate app I designed and posted awhile back that demonstrates how I

create n-tier designs with this approach. I know it was on Ed's forum,

but that's no longer available.

Ed -- do you still have those files anywhere (even though the forums are

disabled)?

Have you looked into using CursorAdapters?

On Feb 4, 2009, at 7:53 AM, MB Software Solutions General Account wrote:

> I'd steer clear of the remote views if you could. You could

> download the

> Piecerate app I designed and posted awhile back that demonstrates

> how I

> create n-tier designs with this approach. I know it was on Ed's

> forum,

> but that's no longer available.

>

> Ed -- do you still have those files anywhere (even though the forums

> are

> disabled)?

I'm not sure what you mean. Was it just a description in a post, or

did you actually post files?

-- Ed Leafe

Ed Leafe wrote:

> On Feb 4, 2009, at 7:53 AM, MB Software Solutions General Account wrote:

>

>> I'd steer clear of the remote views if you could. You could

>> download the

>> Piecerate app I designed and posted awhile back that demonstrates

>> how I

>> create n-tier designs with this approach. I know it was on Ed's

>> forum,

>> but that's no longer available.

>>

>> Ed -- do you still have those files anywhere (even though the forums

>> are

>> disabled)?

>

>

> I'm not sure what you mean. Was it just a description in a post, or

> did you actually post files?

Wow...sorry for the lame answer but it's been so long that I can't

recall if I posted a link to it, or if I had put the zip file there.

(Of course, if that latter wasn't possible, then that means I must have

it somewhere on my site for download.)

My memory is very foggy on it as it was about 2 years ago.

On Feb 4, 2009, at 10:10 AM, MB Software Solutions General Account

wrote:

> Wow...sorry for the lame answer but it's been so long that I can't

> recall if I posted a link to it, or if I had put the zip file there.

> (Of course, if that latter wasn't possible, then that means I must

> have

> it somewhere on my site for download.)

>

> My memory is very foggy on it as it was about 2 years ago.

If you remember something I can search for in the text, I still have

the underlying tables for the site, and can query them.

-- Ed Leafe

Gianni Turri wrote:

> Would be my intention to use remote views in some places.

>

> The problems with remote views you mentioned are only with MySQL or even with MS SQL?

The problems I encountered had to do with the way VFP worked... needing to compile

the query in the DBC etc.

> Can you give me details about the problems you've encountered?

Sorry, I can't remember the details. During my 'proof of concept' stage I eliminated

remote views as a possibility early on because of the problems I ran into.

Paul

Ed Leafe wrote:

> On Feb 4, 2009, at 10:10 AM, MB Software Solutions General Account

> wrote:

>

>> Wow...sorry for the lame answer but it's been so long that I can't

>> recall if I posted a link to it, or if I had put the zip file there.

>> (Of course, if that latter wasn't possible, then that means I must

>> have

>> it somewhere on my site for download.)

>>

>> My memory is very foggy on it as it was about 2 years ago.

>

>

> If you remember something I can search for in the text, I still have

> the underlying tables for the site, and can query them.

It was in the Using VFP with MySQL forum...it was called

PieceRate....that's about what I can remember. It may be on an older

laptop (not my current one) so that's why I was checking here first.