SerialCoder


I am working with FP tables from an old warehouse managment system and the SLOOOW performance is killing me. Not being a FP person, I have some general questions.

I have several access databases that have linked tables to FP. These are particularly slow. The server that contains the FP tables has a 100MB Nic on a 10/100 switch. If I changed that out, would my calls be faster

When running updates on large recordsets (1000+ records), are the updates happening client side and being feed back to the table

I have IIS on the FP server, could I set the table directory as virtual root and let the server do the work. This seems like it would speed things up.

Any other tips

Regards,

Dave Tulk




Re: Need better performance with FP Tables

Aleniko


Dave;

Going Gigabit would certainly help, but I suspect your problems are on the software side.
How exactly do you 'run an update'






Re: Need better performance with FP Tables

SerialCoder

Some of the updates are done by linking the tables in access and running queries. I am running one of them from a VB program with ADO. A few from access modules using ADO. Even the simplest of SQL calls seems dog slow.






Re: Need better performance with FP Tables

CetinBasoz

You're using with Access and complaining FP to be slowSmile How do you reason it's FP that's slow and not Access link

For what it's worth in Foxpro we don't call 1000+ recordsets as large. They're small very small and even not worth to do the milliseconds level timing when working with them. If you want a real timing as an example (AMD x2 - 4600 2Gb RAM):

Querying 120000+ records from SQL server, pivoting them on VFP side and showing the result takes 0.7 seconds (local). Same operation done totally in SQL server takes 0.3 seconds less. Same on a network takes around 2 seconds. I don't think you could call it slow.

Even from an ASP page with ADO calls it's fast, I really wonder what do you mean by slow. Probably you have faulty network hardware.





Re: Need better performance with FP Tables

SerialCoder

I dont suppose I meant that FP itself was slow. It's more of a "Way things are" being slow. I set up IIS and ran some tests making the ADO calls through ASP and the results were astonishing. I didnt bother with any real testing but my guess is that it's happening at about 1/100th of the time that the Access/linked tables method.

To give an examble of slow, I had a recordset of 4300 records that needed 1 field updated.

Select client, udf3, name from tablename where client='xyz' and name like '%WALMART%';

I would open the recordset and run a loop. If the record meet some simple conditions, I would update UDF3 with some text. It seemed real simple. I ran it with access / linked tables through a module and it was averaging 1 record per second. The same procedure using IIS server side, did 850 records in under 2 seconds.

Anyway, thanks for the help.

Dave





Re: Need better performance with FP Tables

CetinBasoz

Tadaaa. So it was the way you handle updating not foxproSmile Try that with something like linking to another Access table (ADO) or SQL server. You wouldn't get better results.

My advice, lock it for batch updates and UpdateBatch after the loop. Or send a single update SQL command with your expression. ie:

oCon.Execute( "update myTable set myAmount = myAmount * 1.1 where myDateField > {^2006/12/31}" )

I had some other posts in VB,Access threads for similar problems, check them if you can find. It's not a problem of VFP, happens with any backends if you try to do updates one by one.





Re: Need better performance with FP Tables

Claude Fox

FWIW, if you want the best possible performance from asp/web/iis and vfp, use vfp mtdlls and expect a 10X or more perf increase over ADO. Here's an old article that compared ADO and VFP mtdlls:

----------------------------------------------------------------------
ADO = ADSLOW (original link: http://www.foxdevsd.org/ftp/Newsletters/Newsletter_2000_06.txt)
by Harold Chattaway
from "The LA Fox Developer"

ADO/ASP vs VFP COM Component Benchmark

These benchmarks should prove useful when comparing the relative
speed differences between manipulating data in ASP/ADO and using a
VFP COM component that is instantiated from an ASP page. The
datasource for both the ADO/ASP and VFP COM component was a SQL
Server 6.5 table. There were NO DBF files utilized for these tests.

The data table has 1026 records with 21 fields for a record length
of 116.

The first table below uses the same technique for both ADO/ASP and
the VFP MTDLL. A string is concatenated together that represents
the complete HTML table. As you will see, the ADO/ASP combination
performs very poorly. This method in ASP is not a viable option.

The second table shows the times for ADO/ASP when the RESPONSE
object is used to send each piece of the HTML table directly to
the output stream. In this test, ADO/ASP performs much better.
String concatenation is not the preferred technique.

This table shows the times for ADO/ASP and a VFP MTDLL to perform
the same task. They are both taking a table with x number of
records and converting them into an HTML table. The cursor that is
used by VFP is a VFP View onto the same SQL Server table that is
used by ADO/ASP. These times do not include the time to retrieve
the data from SQL Server. This is the elapsed time between the time
the cursor processing loop is entered and the time it is left. It
is concatenating together a string that represents the complete
HTML table...

Table 1:

 # records  Time ADO/ASP  Time VFP MTDLL  Speed Increase
      50      <1        .05       -
     100      1        .09       5.88x
     150      3        .18      16.6x
     200      4        .22      18.8x
     300      14        .34      41.1x
     400      29        .46      62.9x
     1029    timed out     1.11       -


As you can see, the times slow down drastically as the record
count goes up for ADO RS. This is a very non-linear
relationship.

The third column in the above above table shows a much more
linear relationship with regard to VFP's performance.

On 400 records, VFP is 62 times faster then the same operation
in ADO/ASP using string concatentation.


This next table shows the times for ADO/ASP to produce an HTML
Table from a ADO RS using the RESPONSE object. These times are much
better then in the above test, but still much slower than the VFP
MTDLL approach.

Table 2:

  # records  Time ADO/ASP  Time VFP MTDLL  Speed Increase
      50      .393       .05       7.66
     100      .756       .09       8.4
     150     1.081       .18       6
     200     1.455       .22       6.61
     300     2.185       .34       6.4
     400     2.851       .46       6.18
     1029     7.361      1.11       6.61



The next test was taking an ADO recordset from ASP and passing it
to the VFP COM component. The receiving function then converts it
into a VFP cursor using VFPCOM.DLL. It then processes the cursor as
in the above test. This was done as a practical matter to see if an
existing ASP app that uses ADO recordsets could benefit from this
approach. The COM component was able to be plugged into the page
utilizing an existing ADO RS. Passing this RS to VFP and allowing
VFP to manipulate it, proved to be a trivial task. This allows for
retaining logic in the ASP page and utilizing VFP for performing
the data processing steps.

Table 3:

    # of records          Time in seconds
   1026 record ADO recordset         1.11
   to VFP COM MTDLL from ASP Page

   Total time from ASP side to pass      3.47
   and receive back result including
   conversion of RS to VFP cursor



An important point to be taken from this is that this type of data
processing can be done around 6 - 7 times faster using VFP. If if
the data is originally in an ADO Recordset, it can still be this
much faster to pass the recordset object to VFP and have the VFP
program handle it from there. The biggest reason for this is the
overhead involved in making COM calls using ADO. To loop through
a 100 record table with 10 fields and produce an html table,
requires 1200 COM Calls! Each row requires 10 COM calls to access
the field * 100 rows. Plus there is a COM call to do a MOVENEXT at
the bottom of the loop and a check at the top of the loop for EOF.
That is 200 more COM Calls. In VFP, there are no COM calls at all
to do the same processing. There is just the initial call to the
VFP function from the ASP page.

Virtually any web-site could take advantage of the VFP speed
improvements regardless of the data source. VFP provides this
improvement even for non-dbf file formats!

Harold Chattaway
www.webdataserver.com