Hi All.

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