>>
>>I know this is going to sound really simplistic, but...
>>
>>I had a slow response problem when a PK on one of my tables was a concatenation of 2 fields. When I changed the PK to a single surrogate integer field, performance was as fast as expected. I also do not update the PK field when sending updates. For inserts, my PK is generated by a SP stored in the database.
>>
>>If the fields getting updated are involved in indexes, you are taking a good hit there as well. This is also, obviously, true of inserts.
>>
>>Is the DB VFP, SQL Server, Oracle? I am guessing VFP.
>
>Hi Mark,
>
>PK is OK, no problem there. There are indexes on the table (160Mb CDX). I'm looking at queuing the inserts (MTS style). The client already have a queue system in place for other tables.
>
>VFP back-end at the moment. Oracle being invested at the moment (they've had their fingers seriously burnt by consultants who cost thousands and did nothing). They need a quick solution in VFP.
Oracle for 100 users will be expensive. How does their price compare to SQL server?
Alternately, can you run tests on a copy of their data and only a sample of records? To test, I would do time tests on inserts and updates on the table as is. Then I would drop all indexes except PK, then test the time to do inserts and updates. Add 1 index back. Test. Repeat. I would use a loop to reiterate the update and insert process a number of times.
Did you say this was done through a view? If so, what update type are you using? You may want to try Key Field only so you are not comparing updated or modified fields.
Mark McCasland
Midlothian, TX USA