For years my software has run quickly on a local drive but when there are
large files on network drives, it slows down terribly when moving from
record to record.

I am going to approach our IT department about, but before I do, I need to
know what questions to ask.

My software is in a large hospital, but at this point it runs stand alone
(not netowkred) using Paradox 10 Runtime. I think they use Novel network
What are the factors which will slow down performance when a database runs
on a network drive?
I don't know where to start. I would like to get information, bring it back
to this forum and ask for help in sorting this out. Has anyone else run into
this problem?

Thanks in advance

Craig

Craig wrote:
There are all sorts of things that can make a fileserver-based system run
unexpectedly-poorly in a networked environment. The first thing to check
is that your network-related configuration parameters are correct. Then,
ask the IT people to help you monitor the actual disk I/O traffic that is
being generated by this application .. the locks that it is holding and
so-forth.

Also consider exactly how your program is doing what it does. On a local
drive with sharing off, there's a lot that you can get away with because so
much information winds up sitting in local memory caches. You can troll
through a table sequentially, for instance. In a networked environment you
can't get away with that.

The IT Department is a good place to start: ask them to help you understand
what this application is doing as seen by their network. Do not defend
either the application nor yourself, for neither of these are in-question.

----
ChimneySweep(R): Fast(!) table repair at a click of the mouse!
http://www.sundialservices.com

Craig,

I have gone through this (and still am!), so I understand. I've resorted
to running the WireShark network monitor (freeware that used to be Ethereal).
It shows you all of the network traffic. You'd be amazed at how much data
gets shipped back & forth for the seemingly simplest task.

Since you can narrow down where it is really slow, you can start a WireShark
trace just before jumping to the next record, then stop the trace. This
will show all the network usage - could be thousands of records (or hundreds
of thousands!). If you page down the trace, look for any large time gaps,
meaning there's a lag somewhere. Whatever is the last instruction is probably
causing the delay.

In WireShark's Statistics menu, the Protocol Heirarchy will show you how
much SMB traffic (peer-to-peer file sharing) has happened. You can also
see a summary of each I/O type, on Statistics | Service Response Time | SMB.


WireShark doesn't do a very good job helping you narrow down which file is
causing all the I/O, though. I have to export the list to Excel, and tweak
it there. I can send instructions if you're interested.


Outside of that, here are my guesses on what you might need to change in
your application to speed it up:

1. You might be constantly reopening tcursors. Make the tcursor variable
global to the form or library, and use this code instead of just tc.open(..):
ok = tc.isAssigned()
if not ok then
ok = tc.open("myTableName.db")
endif

2. Block sizes on your tables are too large. When you open a table, you
get 8 pages of data. If you use 32k or 16k block sizes, that can add up
to lots of wasted bandwidth.

3. You have lots of files in the ":Work:" folder. This causes long seek
times searching for the right file.

4. You open forms, reports, libraries with just the filename and no extension.
This can also cause long seek times. Use this logic instead:
myform = "ABC123"
if not f.open(myForm + ".fdl",winStyleDefault+winStyleHidden) then
errorClear()
if not f.open(myForm + ".fsl",winStyleDefault+winStyleHidden) then
errorShow()
return
endif
endif

5. You have obsolete printers & drive mappings. This can greatly slow down
a networked application, as Paradox is constantly trying to resolve all printers
& drives. Make sure every printer is 'Ready'.


Let me know if you need any help interpreting the WireShark output. It is
very powerful, but pretty cryptic.

HTH,
Jim Moseley

Hi Craig,

Unfortunately this is the nature of file based databases. The larger the
size of your data tables, the slower your app becomes on a network.
Having said that, the way your app is written can either put off the
inevitable for a while or lead you to it faster. For instance, if your
app uses a lot of queries on network tables you are going to find it
slowing down sooner rather than later. If you can use secondary indexes
with set range to get the views of the data you need, things will happen
a lot faster. There are multiple things like that that can help or
hinder you depending on how you chose to approach it. How large are the
data tables on the network?

However even in the best of circumstances, the app will get
progressively slower as you get more data. If you are going to use this
app for any length of time and will keep adding a lot of data, the
answer is to convert the back end to a CS database. I've been there and
done that. I spent the last year converting a traditional Paradox app to
one which uses Paradox for the GUI and MS SQL server 2K for the
database. Everything runs off stored procedures on the server. The
difference is absolutely stunning. Things that used to take 30-45
seconds are now instantaneous. The improvement in speed is really
unbelievable. It is rock solid also. I know that's not what you wanted
to hear, but that has been my experience.

Regards,
RoyF

Craig wrote:

Craig,

One immediate way to speed this up is to use Terminal Services, where everything
runs on the server. I've got non-TS clients that can have 10,000 rows take
forever, and TS clients that have 100,000 rows popup instantly.

HTH,
Jim Moseley

In my experience, file-size is not a de-facto indicator of poor performance,
and Roy's use of the word "terribly" is to me an indicator that the
application might be suffering from an as-yet undiagnosed anomaly. The
WireShark recommendation is an excellent one.

In a networked environment, full-table-scans are death, and maintained
indexes are absolutely, 100% crucial. "Surprises," such as table-lookups
and other file-access that you just might not be aware of, are unpleasant
surprises.


----
ChimneySweep(R): Fast(!) table repair at a click of the mouse!
http://www.sundialservices.com

Craig,

When you run on NOVELL, make sure that OPLOCK LEVEL II is turned OFF.
I had many corruptions of my Databases when this was set to ON.
This is the same thing a Microsoft Oportunistic Locking. For some reason,
NOVELL make this TRUE/ON in Novel 5 or 6.

Also, is the Hospital a "flat network" or is it in a VLAN? This makes a
huge difference.
Also, how busy is the NOVELL server? Are they clustered? This is easy for
your Network Admin to determine.

Oscar....

Don't forget to check on what antivirus software is running. Some
impose an incredible hit on network performance.

The system drag imposed by some AV programs (like Norton) can be
mitigated by adding certain files to their 'exclusion' list:
Pdoxusrs.net, Pdoxusrs.lck, Paradox.lck, etc. Other AV programs don't
even let you do that and IMHO should be dumped. I tested out a few on
our network and settled on Eset's NOD32, which seems to be less of a
resource hog and interrupts fewer system processes.

Erik

Craig wrote:

Well, lots of folks here have fairly large apps/databases running across
LANs and WANs. I would start by looking at. For record to record
navigation, the file size shouldn't matter all that much. I'd take a look
at the code that fires on record navigation events.

Also, when you say "network" I suspect you have something a little more
involved than a simple LAN. Two things will rapidly kill network
performance for shared file access - bandwidth and latency. Chances are,
the only control you have over this involves the physical location of the
server in regard to the clients - you want to make sure that there are very
few hops between them, preferably none.

You can get a sense of this by pinging a large packet from one of the
clients to the server and also by running a traceroute between one of the
clients and the server. The ping will tell you latency and the tracert will
tell you how many hops.

You want to work with the IT folks to minimize latency and hops, which may
be as simple as plugging the server into a different local LAN segment. The
benefit goes both ways -- if you can minimize the amount of your traffic
"pollutes" WAN circuits, everybody wins.

Whatever progress you make there, you also want to look at your code. You
want to minimize queries and unindexed filtering, particularly in record nav
operations.

Ping:

ping -l 65500 <server DNS name>

Traceroute:

tracert <server DNS name>

--
Larry DiGiovanni

Ignore that. Someone here broke my concentration.

--
Larry DiGiovanni