Thanks for your reply. We are going to host the data on a SQL server in NYC, then use SQL Server replication to get the data out to LA. There were just too many updates & adds (bytes/second-wise) to push the data across the slow connection. We can issue massive SQL-Passthrough commands here in NYC, and they execute VERY quickly. Commands like:
INSERT INTO x VALUES (y, z, etc) INSERT INTO q VALUES (a, b, c, d, etc.) INSERT INTO widget VALUES ('Fireball', 'Disintegrate', 'Blaze')Thanks for your help. It made us rethink our strategy.
- George
>>The SQL-Server DATA WAREHOUSE is in LA.
>>Our VFP System is in NYC.
>>Changes made in our VFP system need to be put into the SQL Warehouse.
>>That's it.
>>
>>They now want us to do the inserts, but only after checking if the records
do not exist in SQL server (otherwise update the SQL records). Also, they want us to delete records in SQL when they are deleted in VFP.
>>
>>Performance is crappy. They have set up access to the SQL Server in LA via a VPN. We are seeing multi-second update times for single record updates. Our tables are very large. Some are pushing 1.8 GB, with 16 Million+ records. We are updating 300,000+ records on some workdays, and might add 50,000-100,000 records per day. And these numbers are assuming that we don't ever skip a night. :)
>
>I've added the phrase 'do not' above in bold - is this correct?
>
>I'm assuming that you are using an ODBC connection across the VPN from VFP to SQL Server. If this is the case there might not be much scope for improving performance using the current connection technique. Are you using SQL Pass Through, Remote Views or (SQL Server based) Stored Procedures?
>
>If you are using SQL Pass through or Stored Procedures then SQL Server is the bottle neck. If you are using remote views these MUST be parameterized and should be opened using the NODATA clause.
>
>Do you close and reopen the connection from VFP to SQL Server for each transaction, or are you keeping it open.
>
>Is there any chance you can place a VFP exe on the server in LA? If yes then I would recommend batch processing your updates in thirty minute (or less frequent) lots. Create three sets of files one each for Updates, Inserts and Deletes. The Inserts would still need to be checked to see if an Update is actually required.
