VFP, SQL and XML

[hjohnson]

I looking for some suggestion on the best way to approach updating large amounts of data from VFP into SQL.

The project involves VFP constantly querying VFP tables looking for newly added or modified records (Based on date/time stamp) and then updating that record in a SQL table with a similiar structures. Currently, when VFP finds are records that needs to be updated, I am using SQLEXEC() to update SQL. It works fine, with the expection that it's slow. What I was thinking of doing is selecting all the records that need to be updated in SQL into a cursor, save it as an XML file and have SQL import it.

If there is someone out there that is doing something similiar, I would love to hear some additional Ideas or suggestions. I'm a beginner with SQL and may need some coaching, but I'm eager to learn

Thanks in advance.

HJ

 

[ramani]

HI

Look in this.. "Performance Tuning Tips for Using VFP and SQL Server Together" topic using the link..

http://winnersoft.coolfreepages.com/vfp/vfpfaq.htm#utils

ramani
(Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[jimoo]

Executing a stored procedure on the server to handle insert, update, and deletes is usually faster.

Use the SQLEXEC() to execute the stored procedure and pass in parameters.

If you are looking at a lot of data, Read SQL Server Books-Online documentation on DTS.



Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[Mike Lewis]

HJ,

If you have a large number of records to update all at the same time, one option might be to use SQLPREPARE(). Essentialy, you 'prepare' the UPDATE statement by sending it once, then repeatedly SQLEXEC() it, using parameter substition to provide the actual values.

This is very much faster than sending the full UPDATE command every time, as the server would have to compile and optimise it multiple times.

You don't say which back end you are using. If it is MS SQL Server, another option would be to generate a text file containing the UPDATE statements, and sending these as a batch. Again, that will be faster than sending them individually.

That approach is similar to your idea of writing the data to XML and importing that into the server. That would probably also give you a good speed advantage (again, assuming you are using SQL Server or another back end that supports XML). It will need some experimenting to see which is the fastest approach.

If you want to follow up any of these suggestion, let me know. I can probably dig up a bit of sample code.


Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[hjohnson]

mike,

Thanks, I'd forgotten about the SQLPrepare, it might be what I need.

I am using MS-SQL as the backend. What I am trying to accomplish is essentially creating a Datawarehouse. The production tables are all VFP, but I've got this other operation that is constantaly looking for new records which are inserted, or changed records that are update on the SQL side. Each record is processed individually, I'm not sure how I could batch it. I also considered using XML, by selecting records that need to updated, creating a XML file and saving it where a SQL process could pick it up and process it, but I'm not familiar enough with SQL to know how to approach something quite like that. Additional Suggestions would be greatly appreaciated.

Hal J.

 

[Mike Lewis]

Hal,

I'd guess that you would do the batching on some sort of time interval. Every so many seconds / minutes / hours, you check to see which records have changed. Using XML is a promising route, because you can use VFP's XMLUPDATEGRAM() function to collect the changes.

You can then write that to a text file and send it to SQL Server, or maybe send it as a parameter to the relevant SQL function -- it's a while since I did that, and am not up to date with the details.

But you should also definitely consider SQLPREPARE(). In my experience, it is very efficient and not too difficult to program.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk