VFP 9.0 SQL worthless under a non client server network?

VFP 9.0 SQL worthless under a non client server network?

• vka

  Is your LAN using Gigabit switches Do you use Cat6 cables
  
  

• preed

  I think I can somewhat meaningfully contribute to discussion. I am in the process of transferring my vast database from xBase (actually, Visual dBase) to VFP. I started it 5 months ago, it turned out to involve a lot of stages and a couple of parts are still unfinished because a lot of coding is also involved. However, I was able to compare the speed of execution of searching indexed tables which are essentially identical now by a process that is supposed to collect (match) a dozen records according to a craterion out of tables with an average size of 10,000 records.

  The comparison shocked me. VFP is at least 100 times faster and perhaps much more. I could not make a numeric comparison because I was measuring time in dBase with a mechanical stopwatch but a similar operation in VFP took less than 10 msec I think. Thus my ActiveX control had to be reset, it still did not show anything and I gave up on it. There are other concerns.

  My first thought was: how did MS make it so fast But then I put it differently: how did xBase designers managed to make it so slow

  
  
  

• Nethol

  Aleniko,

  This is a hardware issue not a VFP issue. You are seeing purely the difference in I/O bandwidth between your local hard drive and the network.

  A SQL select is going to have to pull a fair amount of CDX data across the wire before it starts to pull the data rows (assuming you have a Rushmore optimizeable query).

  
  
  

• nyquist58

  You can check Rushmore optimization with the function SYS(3054).
  
  Tamar
  
  

• JS26

  Craig;
  
  Agreed. Obviously these are factors, but I don't see how I can use xbase commands to fetch the single record in no time where SQL takes 10 seconds.
  
  I have a feeling this has something to do with rushmore. My index is on left(design,6)+size_cat+left(status,1) and my sql command is exactly the same (I THINK I am using a rushmore optimized query).
  
  I'm lost.
  
  Aleniko.
  
  

• skweaz

  Hi Aleniko,

  Something Dave didn't specifically mention, if your tables are on a network and the network is slow, then the network is slow whether you're using SQL or Useing a table. In order to use Xbase commands you'll still need to bring indexes and data over the network.

  You can, however, increase speed with proper indexing. It's often better to not have indexes on fields with only a few discrete values such as Deleted() or Sex. Here's a good discussion of indexing: http://fox.wikis.com/wc.dll Wiki~UnderstandingRushmore .

  VFP is not a "client-server" database in the sense that SQL Server is. In other words, the work is not done on the server no matter how you access the data, it's done on the client. Using ODBC, ADO, etc. to access your tables adds another layer of complexity (slows things down).

  
  
  

• Mungos

  David;
  
  I agree that hardware has something to do with the performance issue, but please read my response to Cindy.
  
  thanks.
  
  

• WtBck

  Alex;
  
  No - I'm using cat-5s and no gigabit. But read my prev response. I know I can increase speed with a different lan topology but the issue is a software issue.
  
  Thanks.
  
  

• Earl Bonovich

  Aleniko,

  VFP is taking 10 seconds to retrieve a result set that contains 1 record across your network. This is fundamentally different than an Xbase seek the record pointer to a row in the table. VFP SQL engine must pull a lot of CDX and then DBF packets through the wire, this takes orders of magnitude more time than it takes to get the same data from a local hard drive. There is no way around the basic physics of the bandwidth difference.

  Network apps are never as fast as a local app.

  
  
  

• Aran

  To clearly demonstrate the problem:
  
  table ruginv has an index tag jj2 on left(design,6)+size_cat+left(status,1)
  
  I run the following 2 scenarios. The first is a SQL select which takes apx 10 seconds to come up with a single record. The second is an xbase approach which brings up that single record instantaneously.
  Is this a rushmore issue THERE MUST BE A WAY TO OVERCOME THIS PROBLEM!
  
  mdes = "SU-114"
  msize = "810 "
  
  * Sql approach
  sele * from ruginv;
  where left(design,6) = mdes and size_cat = msize and LEFT(status,1) = "C";
  into cursor stk
  SELECT stk
  brow
  
  *Xbase approach
  USE ruginv ORDER jj2
  SEEK mdes+msize+"C"
  COPY TO jjstk REST WHILE LEFT(design,6) = mdes .AND. size_cat = msize .AND. LEFT(status,1) = "C"
  USE jjstk
  brow
  
  
  

• _Michael Fischer_

  There are several things that can cause network-based data to be slower than local.

  - Network bandwidth

  - Size of tables

  - Rushmore (make sure you have an index that EXACTLY matches what is on the left side of the equal sign)

  - EXCLUSIVE/SHARED use of tables

  - etc.

  
  
  

• BenziZbit

  David;
  
  I failed to mention that I have tried the rushmore optimized expression as well, and there is not much difference. It would still take about 10 seconds to find a single record.
  
  Thx.
  
  

• Pdoh

  Aleniko,

  This is a not rushmore optimizeable based on the index you posted:

  sele * from ruginv;
  where left(design,6) = mdes and size_cat = msize and LEFT(status,1) = "C";
  into cursor stk

  This would be

  sele * from ruginv;
  where left(design,6) + size_cat + LEFT(status,1) = mdes + size_cat + "C";
  into cursor stk

  
  
  

• bojingo

  David;
  
  In the code example I have demonstrated that using Xbase, I am not just seeking to a record, but am also creating a new table (Although not a cursor) with the resulting query.
  
  I don't see why VFP should do anything more than that Why the query (If indeed it is a smart engine) Can't know how to utilize an index All SQL needs to do is to seek to that record and fetch the records from that point to the point where the index is no longer matching the scope...
  
  I would hate to have a positive answer to the subject of this thread... If indeed it takes SQL 10 seconds to do something I can do using xbase in no time (And the result is exactly the same - I am creating a table with the set of records that I am doing the query for), then indeed SQL would be useless under a network topology.
  
  Having said that, I still think the problem is somewhere in the way I do the query. I can't believe foxpro and rushmore can be so inefficient in retrieving data over the network.
  
  Thanks.
  
  
• VFP 9.0 SQL worthless under a non client server network?