Network performance typical?

[dunc0029]

I am just curious if this performance seems "typical"? I realize this is a very subjective question. I am doing some troubleshooting and I am fairly sure the network is our bottleneck. When we load our forms, we do several queries of the "select <fields> from <table> where <key>=<given value>" variety. This is a high selectivity (1-5 records) query. We do have the indexes for Rushmore to fully optimize. My current machine is 400 Mhz, 192 MB ram. Our data is on a network drive.

For a benchmark, I have a table called "member" with about 7000 records and 37 fields ( not a big table by any means ).

t=SECONDS()
SELECT * from member where mb_key_p= 1004779 INTO CURSOR c1
?SECONDS()-t

This is a primary key field ( so we are selecting 1 record ), but this alone takes about .4-.5 seconds on the network versus about .01-.03 seconds on my hard drive. In a real form, we have several of these kinds of queries, which results in a very noticeable delay in loading the data. For example, 10 of these queries would take 5 seconds. I guess my question is: do we have a very slow network, or would is that typical network speed? Our file server is not maintained by us as we are a subsidiary of a larger company, and therefore have little to no control over that side of things.

Any help would be much appreciated. Thanks!

Jason

 

[DSummZZZ]

...would is that typical network speed?

Hard to say. Besides hardware, a lot depends on installation options such as buffering, caching, etc.
What you need to remember here is that generally speaking, hard drive access speed is going to be a whole bunch faster that any network access speed.
Your results are still going to vary depending on how many users are trying to access the table or records at the same time.

For example, 10 of these queries would take 5 seconds.

Not necessarily. Granted, any time you run a queyr, unless you are able to use a specific index, the entire file is going to read, i.e., hauled across the network wire to your local cpu. But between file caching and rushmore, it's very likely that will only happen the first time. From there on out a buffered version of the file or set of records will most likely be used. That decreases query times immensely.


-Dave Summers-

Even more Fox stuff at:

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

 

[dunc0029]

Thanks for the response. Judging from your reaction, this doesn't seem incomprehensibly slow, even for small tables and small result sets. To clarify on my comment of 10 such queries taking 5 seconds, I meant 10 similar queries, all against different tables ( to get the children of several one to many relationships ). A member can have several applications, policies, invoices, credits, payroll deductions, etc. Currently, our application is designed to run these queries as the form is loading. In comparison, we have a 2.6 app that runs blazingly fast off the same file server with much larger datasets. There, we use the tables directly.

On another note, I ran a similar query against an Oracle database we populate from our data for our website through SQL pass-through in VFP. The performance was much better than accessing native VFP tables on our file server.

 

[jimoo]

It has been my experience that the application always executes faster residing on the desktop.

On the surface, it doesn't seem like it should, but it does. The disadvantage to running the app on the local pc is you need to copy the version out to every pc. However, there are application launch programs (see our FAQ section) that do this for you. The nice benefit to this, in addition to the increased speed, is that you don't need everyone out of the system to copy one silly change out, that may affect one person. Instead, copy the exe to the network and let the application launch program copy the new file down next time they log in.





Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[baltman]

You might consider having the server run the query and return only the results.

Have an instance of VFP running on the server scanning for a 'directions' file every 1/2 second or whatever (using a timer control). The 'directions' file can be a text file with the queries and a machine name that is generated when the form loads.

The server would grab the 'directions' text files when they appear, run the queries and deposit 'results' tables into a directory named the machine name.

The requsting machine would be scaning this directory and getting the files as they appear. Once the files are all retrieved/processed, you can delete them to avoid future conflicts.

This should cut you from 10 seconds to 2-3 depending on server availability.

Brian

 

[baltman]

On another note, if you're looking for only one record, why use SQL at all?

Use a SEEK() and scatter/gather to/from memvar.

Brian

 

[dunc0029]

All - thanks for the help. Baltman, we are trying to be backend independent, abstracting the database layer, that's the reason for the SQL. We will be moving our database for one application to Oracle, hopefully in the next year. This would allow us to make the future migration with minimal maintenance effort. In response to your prior post, I had a similar idea. I might experiment with that. The bad news is we don't have access to the current file server, but I can do some testing and maybe make the case for us to get our own server. I work for a subsidiary of BlueCross of MN, so we don't have much say in infrastructure. We are a subset of 60 or so employees in a company with 4000+ ...