We have an application that needs to modify a large number of records and save it to the MSSQL database. It used to be a VFP database and clients notice that using SQL Server is much slower obviously.
I tested using UPDATE statements per row vs Cursor Adapter, but the performance is exactly the same. The bottle neck seems to be using the ODBC driver which is said to be slower than if you would work straight in SQL Server.
I was thinking about changing the approach and instead create a CSV file or even a DBF file, somehow send the file to the server, and let a stored procedure take the file and run the update statements on the server.
Is there an example or code ideas how that could be done? The stored procedures that I created so far are usually very common update or query statements, so I wonder how I could transport the file and make it available in a SP.
Christian Isberner
Software Consultant