Hi Scott,

I have faced the same problem with VFP - SQL Server. The problem I found was the Remote View with Table Buffering. As we know that Remote View is just a temp dbf resides in TEMP folder. It may have its own FPT and CDX.

VFP seeks the whole View for the pending updates while TABLEUPDATE() is issued with Table Buffering on. It updates the data very fast with small data. But as the number goes up, It slows down dramatically.

Solution I came to was closing the Remote View at regular interval and opening them again with NODATA clause. I know that It worked.

Hope this helps.

Bye
Jayesh



>
>I apologize, in advance, re the length of this post ...
>
>The lengthy dialogue is just meant to clarify the problem we're going through, currently. If anyone can help us, we would be most grateful.
>
>My associates and I are in the midst of a database conversion from an old fx2.6 file-services based app to two new MS SQL server apps. A significant 3rd party app written in C++ using MS SQL server ... and a less significant app written by our firm using VFP6 to accompany the main 3rd party app.
>
>We also wrote the conversion process, and since we were moving the data from fx2.6 to SQL server tables, we wrote the conversion in VFP6
>
>We have converted 1 of three sites now, and when we actually fully ran the conversion, the program gradually slowed down to a painfully slow pace.
>
>After the first site conversion (the smallest) we knew we needed to discover and reapir the problem as we would not have enough down-time on the larger sites in order to process them through... we are 10 days from those conversion dates.
>
>We tweaked a few things, but ok, here's the deal....
>
>The SQL server is beefy enough:
>
>-MS SQL 6.5 service pack 3 applied
>Pentium Pro 180, 256 megs RAM
>RAID level 5 disk sub-system, etc
>
>The workstation is a PII-266, 64 megs, Win98 using VFP6, service pack 2 applied.
>
>Two SQL tables have text fields and we are inserting some memo chunks into these text fields (small character strings really, perhaps 200-250 chars on average).
>
>Initially there is no problem when starting the conversion, the SQL tables are empty and the tableupdates go in quickly as expected.
>
>These tables grow to a approx siz of 70 to 100,000 recs by the end.
>As they grow the process slows down incredibly.
>
>Even after the conversion, our new VFP6 app takes a ridiculous amount of time to update a record with our new app... and yes, our indexes are in place, etc.
>
>Our debugging has seemed to have found that its the memo ( the text field) that is the problem. Once we have a "normal" filled table, it is the initial insert of the record that takes .. oh, let's say 1 minute, to complete.
>
>We have found that it is the initial insert of data into the text field that causes the problem. When VFP moves a new record from the remote view to the server with tableupdate() ... and we also tried a manual connection with sqlconnect() and sqlexec() .. we get the same very slow result .. when the memo data accompanies the tableupdate().
>
>BUT, only on the first insert of the memo. Once there is data in the SQL table's text field, any further updates to the record process quickly as expected.
>
>It seems that the problem is only occurring for us when a record, whether just being inserted, or it aleady exists in the server even... has no data at all in the text field .. it is NULL
>
>As a sidebar, the 3rd party c++ app, using the same odbc connection on the same computer, does not have this problem, only VFP6 does.
>
>When the problem occurs, there is heavy disk activity for the entire minute (at the SQL server) that it takes to process the tableupdate() at the SQL server.
>
>It "looks" like the SQL server is sequentially reading through the table ... as if calculating an offset for the text memo that it doesn't seem to be able to do via indexing, etc. ... or something to that effect.
>
>As confirmation of our problem, I changed the SQL table's field to have a default value of '' (literal empty, instead of NULL) and voila! the problem was corrected. This is a workaround for the conversion only, as we need to set the default back to NULL once the converison has finished.
>
>Unfortunately, we cannot keep this as the default as it conflicts with the 3rd party program software's use of the record. ...... and we are intending on inserting these records in our program for that program.
>
>Whew.
>
>Has anyone seen this problem?
>
>We've tried what tweaking that we might ... in the VFP connection for the remote view, but have been unable to fix the problem.
>
>We're stumped.
>
>If you can lend some assistance on this, it would be greatly appreciated.
>
>Thx, Scott Barker