MySQL slower than DBF ?

This is a multi-part message in MIME format.

------=_NextPart_000_0024_01C5AB22.C746A260
Content-Type: text/plain;
charset="windows-874"
Content-Transfer-Encoding: quoted-printable

Hi,

A customer having 300 invoices per day, 10 users windows NT network.

Using VFP with dbf/cdx, add/replace one invoice takes less than 3secs.

Using VB/MySQL, insert/update one invoice takes 20secs.

Isn't MySQL should be faster than DBF/CDX ?

Is anyone experience the same ? or did I miss out something ?

Regards
Hoe
------=_NextPart_000_0024_01C5AB22.C746A260
Content-Type: text/html;
charset="windows-874"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dwindows-874">
<META content=3D"MSHTML 6.00.2900.2180" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>A customer having 300 invoices per day, =
10 users=20
windows NT network.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Using VFP with dbf/cdx,=20
add/replace one invoice takes less than 3secs.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Using VB/MySQL, insert/update one =
invoice=20
takes 20secs.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Isn't MySQL should be faster =
than DBF/CDX=20
?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Is anyone experience the same ? or did =
I miss out=20
something ?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Regards</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Hoe</FONT> </DIV></BODY></HTML>

------=_NextPart_000_0024_01C5AB22.C746A260--

man-wai
Sat Aug 27 04:45:03 CDT 2005

> Using VFP with dbf/cdx, add/replace one invoice takes less than 3secs.
> Using VB/MySQL, insert/update one invoice takes 20secs.

VFP is local disk access, MySQL via network or localhost?
MySQL should be quite fast, but don't underrate the speed of Foxpro's
Rushmore.

--
.~. Might, Courage, Vision. http://www.linux-sxs.org
/ v \
/( _ )\ Linux 2.4.31
^ ^ 5:44pm up 3 days 21:21 load average: 1.07 1.23 1.23

Anders
Sat Aug 27 04:42:58 CDT 2005

You should take as look at the ODBC log, for one thing.
-A

Den 05-08-27 10.17, i artikeln 4310218e$1_2@news.tm.net.my, skrev "tnhoe"
<tnhoe@pc.jaring.my>:

> Hi,
>
> A customer having 300 invoices per day, 10 users windows NT network.
>
> Using VFP with dbf/cdx, add/replace one invoice takes less than 3secs.
>
> Using VB/MySQL, insert/update one invoice takes 20secs.
>
> Isn't MySQL should be faster than DBF/CDX ?
>
> Is anyone experience the same ? or did I miss out something ?
>
> Regards
> Hoe

tnhoe
Sat Aug 27 08:24:05 CDT 2005

"man-wai chang" ,

> VFP is local disk access, MySQL via network or localhost?

Both under LAN

Regards
Hoe

Dan
Sat Aug 27 13:17:16 CDT 2005

There are many reasons for using a server-based back end database. Speed is
NOT one of them.

Dan

tnhoe wrote:
> Hi,
>
> A customer having 300 invoices per day, 10 users windows NT network.
>
> Using VFP with dbf/cdx, add/replace one invoice takes less than 3secs.
>
> Using VB/MySQL, insert/update one invoice takes 20secs.
>
> Isn't MySQL should be faster than DBF/CDX ?
>
> Is anyone experience the same ? or did I miss out something ?
>
> Regards
> Hoe

Ook
Sun Aug 28 01:21:28 CDT 2005

If the VFP tables are located on a server, and your MySQL database is also
located on a server, and a DBF update takes less then 3 seconds and a MySQL
update takes 20 seconds, then there is probalby something wrong with your
MySQL database setup or the way your VB code is updating the MySQL table.
I've written VFP apps using MySQL, and it's lightning fast. In many cases I
found it to be twice as fast as VFP when doing properly optimized database
actions. All other things being equal, there is no way the same transaction
would run in 3 seconds with VFP and 20 seconds with MySQL. The VB code is
not efficient, or the MySQL table does not have the indexes setup properly.

"tnhoe" <tnhoe@pc.jaring.my> wrote in message
news:4310697c$1_1@news.tm.net.my...
> "man-wai chang" ,
>
>> VFP is local disk access, MySQL via network or localhost?
>
> Both under LAN
>
> Regards
> Hoe
>

swdev2
Mon Aug 29 05:50:37 CDT 2005

Yo Yo Yo Hoe -
typically things are QUITE SLOW with VB and a database -
so do yer speed test again with
vfp WITH mysql instead .
THEN you can have a real speed comparison.
stumped ?
stuck ?
doen't understand ?
go visit http://groups.yahoo.com/group/vfpmyql

wei - can u come in to sing sometime in sept? we gonna have a singfox
meeting
see http://groups.yahoo.com/group/singfox

are you selling UBS in MY ? we do some marketing for them in Singapore - you
should , at least, hit me up with an email.

and
fwiw - no - mysql is not necessarily fast or as fast or even faster than a
native vfp solution.
there are many factors - if you'd like - why not dump the schema , post it
to here, and let us study it,
along with 2 of the vb functions for record insert and record update ?

only from studying that stuff, really, could I actually tell you wtf is
causing a slow down .
but do remember, ah ? VB and Data will be slower than VFP - thats the way
MicroSoft set it up, alas.

and - do - please go findmy email addy before complaining you don't know it.
--
===================
William Sanders / EFG VFP / mySql / MS-SQL
www.efgroup.net/vfpwebhosting
www.dotnetconversions.com
"tnhoe" <tnhoe@pc.jaring.my> wrote in message
news:4310218e$1_2@news.tm.net.my...
Hi,

A customer having 300 invoices per day, 10 users windows NT network.

Using VFP with dbf/cdx, add/replace one invoice takes less than 3secs.

Using VB/MySQL, insert/update one invoice takes 20secs.

Isn't MySQL should be faster than DBF/CDX ?

Is anyone experience the same ? or did I miss out something ?

Regards
Hoe

Ook
Mon Aug 29 09:55:22 CDT 2005

I worked on a system where we were doing complex queries against a large
table (1.8GB or so) that returned several hundred records. MySQL was twice
as fast as VFP, even though the queries were fully Rushmore optimized. In a
case like this, using a server-based back end for performance purposes was a
very good reason. VFP has to transfer chunks of index to the local
workstation before it can figure out what records it wants. If you have ever
observed network utilization when this is happening, you will realize that
VFP is not very efficient when it comes to network i/o and index processing.
It transfers chunks of the CDX one tag at a time, processes it, gets the
next chunk of CDX, processes it, etc. It's lightning fast on a local
workstation, but when it has to do this over a network, and if the
indexes/tables are large and the query is complex and the result set is
large, VFP is a pig. MySQL does the same thing on the server box, consuming
no network bandwidth, and is therefore much faster.

Before the VFP fanatics start to flame me, please note that VFP's
performance hit only occurred with complex queries against very large
tables. We found this to be consistently duplicatable.

And in defense of VFP, VFP will cache index and data on the local
workstation, making subsequent queries using the same or very similar
selection criteria to be very fast because it doesn't have to retrieve index
data over the network. MySQL won't do this.

"Dan Freeman" <spam@microsoft.com> wrote in message
news:OI4fPOzqFHA.2076@TK2MSFTNGP14.phx.gbl...
> There are many reasons for using a server-based back end database. Speed
> is
> NOT one of them.
>
> Dan
>
> tnhoe wrote:
>> Hi,
>>
>> A customer having 300 invoices per day, 10 users windows NT network.
>>
>> Using VFP with dbf/cdx, add/replace one invoice takes less than 3secs.
>>
>> Using VB/MySQL, insert/update one invoice takes 20secs.
>>
>> Isn't MySQL should be faster than DBF/CDX ?
>>
>> Is anyone experience the same ? or did I miss out something ?
>>
>> Regards
>> Hoe
>
>

Cy
Mon Aug 29 20:15:53 CDT 2005

Ook wrote:
> I worked on a system where we were doing complex queries against a large
> table (1.8GB or so) that returned several hundred records. MySQL was twice
> as fast as VFP, even though the queries were fully Rushmore optimized. In a
> case like this, using a server-based back end for performance purposes was a
> very good reason. VFP has to transfer chunks of index to the local
> workstation before it can figure out what records it wants. If you have ever
> observed network utilization when this is happening, you will realize that
> VFP is not very efficient when it comes to network i/o and index processing.
> It transfers chunks of the CDX one tag at a time, processes it, gets the
> next chunk of CDX, processes it, etc. It's lightning fast on a local
> workstation, but when it has to do this over a network, and if the
> indexes/tables are large and the query is complex and the result set is
> large, VFP is a pig. MySQL does the same thing on the server box, consuming
> no network bandwidth, and is therefore much faster.
>
> Before the VFP fanatics start to flame me, please note that VFP's
> performance hit only occurred with complex queries against very large
> tables. We found this to be consistently duplicatable.
>
> And in defense of VFP, VFP will cache index and data on the local
> workstation, making subsequent queries using the same or very similar
> selection criteria to be very fast because it doesn't have to retrieve index
> data over the network. MySQL won't do this.
>
>
> "Dan Freeman" <spam@microsoft.com> wrote in message
> news:OI4fPOzqFHA.2076@TK2MSFTNGP14.phx.gbl...
>
>>There are many reasons for using a server-based back end database. Speed
>>is
>>NOT one of them.
>>
>>Dan
>>
>>tnhoe wrote:
>>
>>>Hi,
>>>
>>>A customer having 300 invoices per day, 10 users windows NT network.
>>>
>>>Using VFP with dbf/cdx, add/replace one invoice takes less than 3secs.
>>>
>>>Using VB/MySQL, insert/update one invoice takes 20secs.
>>>
>>>Isn't MySQL should be faster than DBF/CDX ?
>>>
>>>Is anyone experience the same ? or did I miss out something ?
>>>
>>>Regards
>>>Hoe
>>
>>
>
>
What I tend to find is what you mentioned. When I have to deal with
code that must recursively go back and resolve coded values for display
VFP always wins because it caches the data. When doing the same thing
with a server based database I get the same overhead for each of 1000
recursive calls as for one, with VFP the first one gets the overhead and
all the rest happen almost instantly. Most of our deployments are not
done using terminal services and in doing so we try to remove the
network from the equation. This can get incredible performance, even
with very large tables and comples queries. In addition, I can many
times do a large complex query far faster with VFP by doing more than
one query and only doing 1 or 2 joins at a time. I can actually
outperform a server product that way. Of course your milage may vary,
and to be truthful I have nothing against server databases, but if I
need to do repeated recursive stuff, I pull it down into a cursor in VFP
and work with that locally, it usually turns out to be faster that way.

--
Cy Welch
Senior Programmer
MetSYS Inc
http://www.metsysinc.com

Ook
Tue Aug 30 09:40:13 CDT 2005

This is where VFP shows it's true power. You can suck in 1000 or however may
records to a local cursor, and do amazing things with it that dot net can't
even dream of doing. There is nothing in the world that can crunch local
datasets like VFP can.

> and to be truthful I have nothing against server databases, but if I need
> to do repeated recursive stuff, I pull it down into a cursor in VFP and
> work with that locally, it usually turns out to be faster that way.
>
> --
> Cy Welch
> Senior Programmer
> MetSYS Inc
> http://www.metsysinc.com

Bob
Thu Sep 01 07:18:02 CDT 2005

My tests have found the same..
Bob Lee

"Ook" <no usenet spam at zoot al dot com> wrote in message
news:eUaptpKrFHA.3596@TK2MSFTNGP15.phx.gbl...
>I worked on a system where we were doing complex queries against a large
>table (1.8GB or so) that returned several hundred records. MySQL was twice
>as fast as VFP, even though the queries were fully Rushmore optimized. In
>a case like this, using a server-based back end for performance purposes
>was a very good reason. VFP has to transfer chunks of index to the local
>workstation before it can figure out what records it wants. If you have
>ever observed network utilization when this is happening, you will realize
>that VFP is not very efficient when it comes to network i/o and index
>processing. It transfers chunks of the CDX one tag at a time, processes it,
>gets the next chunk of CDX, processes it, etc. It's lightning fast on a
>local workstation, but when it has to do this over a network, and if the
>indexes/tables are large and the query is complex and the result set is
>large, VFP is a pig. MySQL does the same thing on the server box, consuming
>no network bandwidth, and is therefore much faster.
>
> Before the VFP fanatics start to flame me, please note that VFP's
> performance hit only occurred with complex queries against very large
> tables. We found this to be consistently duplicatable.
>
> And in defense of VFP, VFP will cache index and data on the local
> workstation, making subsequent queries using the same or very similar
> selection criteria to be very fast because it doesn't have to retrieve
> index data over the network. MySQL won't do this.
>
>
> "Dan Freeman" <spam@microsoft.com> wrote in message
> news:OI4fPOzqFHA.2076@TK2MSFTNGP14.phx.gbl...
>> There are many reasons for using a server-based back end database. Speed
>> is
>> NOT one of them.
>>
>> Dan
>>
>> tnhoe wrote:
>>> Hi,
>>>
>>> A customer having 300 invoices per day, 10 users windows NT network.
>>>
>>> Using VFP with dbf/cdx, add/replace one invoice takes less than 3secs.
>>>
>>> Using VB/MySQL, insert/update one invoice takes 20secs.
>>>
>>> Isn't MySQL should be faster than DBF/CDX ?
>>>
>>> Is anyone experience the same ? or did I miss out something ?
>>>
>>> Regards
>>> Hoe
>>
>>
>
>

MySQL slower than DBF ?

Re: MySQL slower than DBF ? by man-wai

SV: MySQL slower than DBF ? by Anders

Re: MySQL slower than DBF ? by tnhoe

Re: MySQL slower than DBF ? by Dan

Re: MySQL slower than DBF ? by Ook

Re: MySQL slower than DBF ? by swdev2

Re: MySQL slower than DBF ? by Ook

Re: MySQL slower than DBF ? by Cy

Re: MySQL slower than DBF ? by Ook

Re: MySQL slower than DBF ? by Bob