>From a workstation, when no other workstation has the two tables in question
the query takes 2.5 seconds
After another workstation opens the tables and relates them to display the
information in form, the query takes 45 to 60 seconds.
Locally the query takes 0.7 seconds.
Table1.dbf - 33,232 KB
Table1.cdx - 16,438 KB
Table2.dbf - 10,808 KB
Table2.fpt - 205,424 KB
Table2.cdx - 3,284 KB
---
In testing this, I have setup in VM's 2 Windows 7 workstations, and 1
Windows 8. Win7-2 is hosting on a shared drive. Win7-1 is doing the query.
Win8 is just opening and relating the tables.
The time to get the results in the VM's is
SMB2 - ON
Single User - 1.937
Multi user - 13.454
SMB2 - OFF
Single user - 1.422
Multi user - 11.813
Using the Resource Monitor I see 63 Mbps when multi users have the table. I
see > 300 Mbps when single user.
Does anyone have thoughts on what options I need to look at to help the
query speed up?
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/000b01d01b16$b23088a0$169199e0$@powerchurch.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Tracy,
How about Reprocess and/or Multilocks? The system with the two tables related might be doing temporary locks when moving between records. ??
Dan
----------------------------------------
> From: tracy@powerchurch.com
> To: profoxtech@leafe.com
> Subject: Network Slow Query
> Date: Thu, 18 Dec 2014 18:02:27 -0500
>
>>From a workstation, when no other workstation has the two tables in question
> the query takes 2.5 seconds
> After another workstation opens the tables and relates them to display the
> information in form, the query takes 45 to 60 seconds.
>
> Locally the query takes 0.7 seconds.
>
> Table1.dbf - 33,232 KB
> Table1.cdx - 16,438 KB
>
> Table2.dbf - 10,808 KB
> Table2.fpt - 205,424 KB
> Table2.cdx - 3,284 KB
>
> ---
>
> In testing this, I have setup in VM's 2 Windows 7 workstations, and 1
> Windows 8. Win7-2 is hosting on a shared drive. Win7-1 is doing the query.
> Win8 is just opening and relating the tables.
>
> The time to get the results in the VM's is
> SMB2 - ON
> Single User - 1.937
> Multi user - 13.454
> SMB2 - OFF
> Single user - 1.422
> Multi user - 11.813
>
> Using the Resource Monitor I see 63 Mbps when multi users have the table. I
> see> 300 Mbps when single user.
>
> Does anyone have thoughts on what options I need to look at to help the
> query speed up?
>
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/BAY169-W94144FA01B97A28961F0A88A6A0@phx.gbl
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
I will follow this subject with keen interest
On Fri, Dec 19, 2014 at 12:15 PM, Dan Covill <dan.covill@outlook.com> wrote:
>
> Tracy,
>
> How about Reprocess and/or Multilocks? The system with the two tables
> related might be doing temporary locks when moving between records. ??
>
> Dan
>
> ----------------------------------------
> > From: tracy@powerchurch.com
> > To: profoxtech@leafe.com
> > Subject: Network Slow Query
> > Date: Thu, 18 Dec 2014 18:02:27 -0500
> >
> >>From a workstation, when no other workstation has the two tables in
> question
> > the query takes 2.5 seconds
> > After another workstation opens the tables and relates them to display
> the
> > information in form, the query takes 45 to 60 seconds.
> >
> > Locally the query takes 0.7 seconds.
> >
> > Table1.dbf - 33,232 KB
> > Table1.cdx - 16,438 KB
> >
> > Table2.dbf - 10,808 KB
> > Table2.fpt - 205,424 KB
> > Table2.cdx - 3,284 KB
> >
> > ---
> >
> > In testing this, I have setup in VM's 2 Windows 7 workstations, and 1
> > Windows 8. Win7-2 is hosting on a shared drive. Win7-1 is doing the
> query.
> > Win8 is just opening and relating the tables.
> >
> > The time to get the results in the VM's is
> > SMB2 - ON
> > Single User - 1.937
> > Multi user - 13.454
> > SMB2 - OFF
> > Single user - 1.422
> > Multi user - 11.813
> >
> > Using the Resource Monitor I see 63 Mbps when multi users have the
> table. I
> > see> 300 Mbps when single user.
> >
> > Does anyone have thoughts on what options I need to look at to help the
> > query speed up?
> >
>
>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAG1nNy_QeZ0FsDB6--zFqqR7gSkJnKi5F_JAZYTX934cHgQmHg@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Ensure Enterprise hotfix rollup is applied to Win 7 machines. Also turn
off interrupt moderation on NICs if the driver offers the setting. I
assume indexes are in order?
--
Alan Bourke
alanpbourke (at) fastmail (dot) fm
On Thu, 18 Dec 2014, at 11:02 PM, Tracy Pearson wrote:
> >From a workstation, when no other workstation has the two tables in question
> the query takes 2.5 seconds
> After another workstation opens the tables and relates them to display
> the
> information in form, the query takes 45 to 60 seconds.
>
> Locally the query takes 0.7 seconds.
>
> Table1.dbf - 33,232 KB
> Table1.cdx - 16,438 KB
>
> Table2.dbf - 10,808 KB
> Table2.fpt - 205,424 KB
> Table2.cdx - 3,284 KB
>
> ---
>
> In testing this, I have setup in VM's 2 Windows 7 workstations, and 1
> Windows 8. Win7-2 is hosting on a shared drive. Win7-1 is doing the
> query.
> Win8 is just opening and relating the tables.
>
> The time to get the results in the VM's is
> SMB2 - ON
> Single User - 1.937
> Multi user - 13.454
> SMB2 - OFF
> Single user - 1.422
> Multi user - 11.813
>
> Using the Resource Monitor I see 63 Mbps when multi users have the table.
> I
> see > 300 Mbps when single user.
>
> Does anyone have thoughts on what options I need to look at to help the
> query speed up?
>
>
>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/1418945878.437376.204645621.08A7E922@webmail.messagingengine.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Dan,
The tables are open with multilock and opportunistic table locking. The tables are related and not in a form in my testing.
On December 18, 2014 6:15:48 PM EST, Dan Covill <dan.covill@outlook.com> wrote:
>Tracy,
>
>How about Reprocess and/or Multilocks? The system with the two tables
>related might be doing temporary locks when moving between records. ??
>
>Dan
>
>----------------------------------------
>> From: tracy@powerchurch.com
>> To: profoxtech@leafe.com
>> Subject: Network Slow Query
>> Date: Thu, 18 Dec 2014 18:02:27 -0500
>>
>>>From a workstation, when no other workstation has the two tables in
>question
>> the query takes 2.5 seconds
>> After another workstation opens the tables and relates them to
>display the
>> information in form, the query takes 45 to 60 seconds.
>>
>> Locally the query takes 0.7 seconds.
>>
>> Table1.dbf - 33,232 KB
>> Table1.cdx - 16,438 KB
>>
>> Table2.dbf - 10,808 KB
>> Table2.fpt - 205,424 KB
>> Table2.cdx - 3,284 KB
>>
>> ---
>>
>> In testing this, I have setup in VM's 2 Windows 7 workstations, and 1
>> Windows 8. Win7-2 is hosting on a shared drive. Win7-1 is doing the
>query.
>> Win8 is just opening and relating the tables.
>>
>> The time to get the results in the VM's is
>> SMB2 - ON
>> Single User - 1.937
>> Multi user - 13.454
>> SMB2 - OFF
>> Single user - 1.422
>> Multi user - 11.813
>>
>> Using the Resource Monitor I see 63 Mbps when multi users have the
>table. I
>> see> 300 Mbps when single user.
>>
>> Does anyone have thoughts on what options I need to look at to help
>the
>> query speed up?
>>
>
>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/55471606-800A-4493-90AB-18733D8E59E1@powerchurch.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
>
> The tables are open with multilock and opportunistic table locking.
>
You mean optimistic table locking, right? Because opportunistic locking is
what likely makes the first query fast. But that is an SMB thing, not a VFP
feature.
Network access speed is largely driven by two factors: What data do I need
to read at all, and how fast can I get data across the network. For the
network speed the bandwidth is the least important attribute. More
important are latency and package throughput.
When only one client opens a remote file, the server and the client
negotiate who is allowed to maintain the read and write caches. In most
cases this will be the client. Effectively this means that the file only
needs to be transferred once and it can be done very efficiently in large
blocks, since all the repeated small block access happens on the client.
When a second client joins the party, the first client is being told to
send back the write cache and discard all client side caches. All further
caching is done on the server. This results in the second opening of a file
to be even slower than further requests, because the server has to wait for
the client to respond. It can also result in data loss, if the first client
fails to respond.
The second part is package throughput. VFP will basically read every record
individually. There are optimization when repeatedly continuous records are
requested, but in many cases it's down to one record per read request. The
number of packets varies wildly between the systems. On the same network
I've seen 250 packets/sec form a Windows 7 client onto a Windows 2008 R2
server and 1250 packets/sec from a Windows 8.1 client onto a Windows 2012
server. But that number is still very slow compared to the roughly 30,000
records/sec that a local drive provided.
Process Monitor from Sysinternals is a good way to see this type of
effects. When you filter on your application, you should see a huge list of
read requests each the size of a record. There's a time column on the left.
In my cases the times where 4 ms for Windows 7 and 0.8 ms for Windows 8.1
--
Christof
--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAL4QJhiiX-tswRSc5-MBNB6vUQ2UoJr2GZmSP9=gdG272aCshg@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Christof Wollenhaupt wrote on 2014-12-19:
>>
>> The tables are open with multilock and opportunistic table locking.
>>
>>
> You mean optimistic table locking, right? Because opportunistic locking
is
> what likely makes the first query fast. But that is an SMB thing, not a
VFP
> feature.
>
> Network access speed is largely driven by two factors: What data do I
need
> to read at all, and how fast can I get data across the network. For the
> network speed the bandwidth is the least important attribute. More
> important are latency and package throughput.
>
> When only one client opens a remote file, the server and the client
> negotiate who is allowed to maintain the read and write caches. In most
> cases this will be the client. Effectively this means that the file only
> needs to be transferred once and it can be done very efficiently in large
> blocks, since all the repeated small block access happens on the client.
>
> When a second client joins the party, the first client is being told to
> send back the write cache and discard all client side caches. All further
> caching is done on the server. This results in the second opening of a
file
> to be even slower than further requests, because the server has to wait
for
> the client to respond. It can also result in data loss, if the first
client
> fails to respond.
>
> The second part is package throughput. VFP will basically read every
record
> individually. There are optimization when repeatedly continuous records
are
> requested, but in many cases it's down to one record per read request.
The
> number of packets varies wildly between the systems. On the same network
> I've seen 250 packets/sec form a Windows 7 client onto a Windows 2008 R2
> server and 1250 packets/sec from a Windows 8.1 client onto a Windows 2012
> server. But that number is still very slow compared to the roughly 30,000
> records/sec that a local drive provided.
>
> Process Monitor from Sysinternals is a good way to see this type of
> effects. When you filter on your application, you should see a huge list
of
> read requests each the size of a record. There's a time column on the
left.
> In my cases the times where 4 ms for Windows 7 and 0.8 ms for Windows 8.1
Christof,
Thank you for the thorough explanation. I am seeing exactly this.
The nice thing I am seeing is the server remains fast even when multiple
clients have opened the table.
I'll be writing a webapi to speed up some of the common requests.
Thank you,
Tracy
Tracy Pearson
PowerChurch Software
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/000001d01b9e$6d0fd040$472f70c0$@powerchurch.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Hi,
It's good idea to return only required data between server and clients. To
speed up vfp tables data access from a windows server to clients (windows
and web), what should one use ?
A) (1) COM+ OR (2) Web service OR (3) 3rd party tools like WebConnect /
ActiveVFP / Foxweb
B) Data format ? XML or Delimited text ?
TIA
Naeem
-----Original Message-----
From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Christof
Wollenhaupt
Sent: Friday, December 19, 2014 8:36 AM
To: profox@leafe.com
Subject: Re: Network Slow Query
Network access speed is largely driven by two factors: What data do I need
to read at all, and how fast can I get data across the network. For the
network speed the bandwidth is the least important attribute. More important
are latency and package throughput.
--
Christof
--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
If your queries are correctly optimised then there is no reason that
just accessing the tables in VFP normally would not be fast, even with
millions of rows. Occasionally network infrastructure problems get in
the way, but that is no reason to start faffing with other methods. The
only Tim you should use web services and serialization like XML is when
accessing VFP data across the internet.
--
Alan Bourke
alanpbourke (at) fastmail (dot) fm
On Tue, 23 Dec 2014, at 10:27 PM, Naeem Afzal wrote:
> Hi,
>
> It's good idea to return only required data between server and clients.
> To
> speed up vfp tables data access from a windows server to clients (windows
> and web), what should one use ?
>
> A) (1) COM+ OR (2) Web service OR (3) 3rd party tools like
> WebConnect /
> ActiveVFP / Foxweb
>
> B) Data format ? XML or Delimited text ?
>
> TIA
> Naeem
>
>
> -----Original Message-----
> From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Christof
> Wollenhaupt
> Sent: Friday, December 19, 2014 8:36 AM
> To: profox@leafe.com
> Subject: Re: Network Slow Query
>
>
> Network access speed is largely driven by two factors: What data do I
> need
> to read at all, and how fast can I get data across the network. For the
> network speed the bandwidth is the least important attribute. More
> important
> are latency and package throughput.
>
> --
> Christof
>
>
> --- StripMime Report -- processed MIME parts ---
> multipart/alternative
> text/plain (text body -- kept)
> text/html
> ---
>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/1419432446.3277882.206461829.1A46F464@webmail.messagingengine.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Alan Bourke wrote on 2014-12-24:
> If your queries are correctly optimised then there is no reason that
> just accessing the tables in VFP normally would not be fast, even with
> millions of rows. Occasionally network infrastructure problems get in
> the way, but that is no reason to start faffing with other methods. The
> only Tim you should use web services and serialization like XML is when
> accessing VFP data across the internet.
Alan,
In my particular case, this is an in house support tool. It is doing a
search across two memo fields for user entered words.
I have made changes which have reduced the searches to a few seconds instead
of minutes. The forms that would open these tables have been changed to only
open them when retrieving data, then later opened when writing data. The
search form also will not leave the tables open. When two techs initiate a
search, it will get slow again.
A web service will eliminate the slow speed caused by the network Christof
explained perfectly.
Tracy Pearson
PowerChurch Software
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/000001d01f91$90883930$b198ab90$@powerchurch.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.