Information générale
Forum:
Microsoft SQL Server
Catégorie:
Stored procedures, Triggers, UDFs
Versions des environnements
SQL Server:
SQL Server 2012
>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.
As the CA sends one statement for each changed cursor row and just adds a few hook templates into the action I'd be surprized if the CA was faster or MUCH slower.
>
>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.
I'd utilze the working CA approach to build a script of insert/update statements if there is nothing in your approach like parent-child PK's in need to be set during the .CursorUpdate (even that could be built, but be much harder to debug). If the slowness comes from wire connection (which is doubtful and could be tested comparing to LAN) you get one big update script to move across and perhaps call it as a batch in 1 transaction.
Be certain you think this approach through if you expect optimistic locking to be working during that operation - it is more suited to single master user updates!
Read up on batch updates in SQL server in advance...
Précédent
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement