Network bottleneck 2.6 DOS on XP?

[jabhawk2]

I have a 1.2G DBF that has to be queried daily and the SELECT is taking forever to run.

I move all temp and work pointers to local folders and got a performance gain but when I monitor the network interface I see that it is maxing out at 2.2M/sec.

I tested the connection my performing a simple OS level file copy of the same file and the interface shows a max at 7.5M/sec so I have plenty of bandwidth.

Are there any limitations within the DOS emulator in XP that would limit the bandwidth available to the session?
Anyone have a suggestion to improve performance without making network structure changes.

This table set is the highwater mark of the application. Any performance gains here would be very evident elsewhere.

Compaq P4 - 348Meg
100M network connection

<config.fp>
CATMAN=OFF
FILES=250
MEMLIMIT=90,8192
EMS=ON
MVARSIZ=64
MVCOUNT=2096
BLOCKSIZE=64

tmpfiles = x:\temp
sortwork = x:\temp
progwork = x:\temp
editwork = x:\temp

 

[cricket]

The FILES=250 statement has no value here. It should be at the end of c:\windows\system32\config.nt file
which is a WinXP file.

Delete TMPFILES=X:\temp

Investigate the SELECT statement as relationship may cause a very slow operation

You can test your DOS file speed on the network using a COPY command in the RUN window of XP

 

[dgrewe]

Is It a preset Static Query that never changes, or does it have parameters in it the user selects?

If it is a static query, can it be run by a nightly program to create a Semi-Daily-Temp table with indexes so the users can hit it instead of the query?


David W. Grewe Dave

 

[DSummZZZ]

I have to ask the obvious, do you have indexes on the fields you are querying? Are you abe to open the table exclusive?

You may be able to run a query on one field, for example a date field, to get a temp file for a days' work. You can then run another query on that temp file with all other matching criteria.
The idea behind this is having exclusive use of the temp file speeds things up immensely.


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[jabhawk2]

Ok,

I ran a DOS copy of the main table from the query and I get a 6.5M/sec transfer rate.

The queries are using single field indexes with only one non index field in the query, brctype. Last run took 16.3 minutes to complete.

BRC - create_dt - index
BRCITEM - ordernum - index

select a.brctype, a.status as ord_stat, b.status as item_stat ;
from brc a, brcitem b ;
into cursor q1;
where a.create_dt = pv_Rpt_Dt and ;
a.brctype = pv_Rpt_bt and ;
a.ordernum = b.ordernum

I am going to test using local files to compare processing times. I am also testing leaving out the non-indexed field in the initial query and using a sub-query to grab just what is needed.

Results to be posted.
Jon

 

[dgrewe]

Questions?
1. Table BRC - Is there an Index that starts with the field BrcType? If No Make one.
2. Table BRC - Is there an Index that starts with the field OrderNum? If No Make one.

Are you using IDX's or CDX's? CDX's would be better here.

I Assume pv_Rpt_xx are Memvars ?


David W. Grewe Dave

 

[jabhawk2]

Ok, the problem seems to be just raw query processing limits.

I ran the original query using local tables and it took 285 seconds.
I removed the non-index component and it took 255 seconds.
I split the query into seperate queries and it took 244 seconds to get the first result.

Either way the problem seems to be the amount of data and 2.6's handling of the query processing. I can get some gain by limiting the query to index fields only but only a few percent.

Guess I will have to rewrite this to run batch on the server and build an intermediate table nightly. The local procesing gain is 75%.

Jon

 

[cricket]

Are you running Hyper Threading (HT) on your computer?
If so, try switching it off in the BIOS.