General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
I also noticed that the VFP performance was pathetic. I think it is because each VFPOLEDB write is in it's own "transaction" where is has to allocate disk space, do error checking, etc. DTS just wouldn't cut it with OLEDB, I didn't try the ODBC driver though.
What I ended up doing, which I really did not want to do, was create a mini VFP .exe with the VFPCOM utility. Then I used the RStoCursor method in it to get a VFP cursor, which I then copied to my local DBC, then I did a query on the InfoSchema.Columns table in MSSQL for what the column names were really supposed to be and renamed each VFP column properly.
So what I was doing with ADO and the OLEDB driver used to take about 40 minutes for the million-row table, and about 90 minutes for the 3.5-million-row table. Now it takes 4 minutes on the small one and 15 minutes on the big one.
My knowledge of ADO is not all that great - I have yet to see an advanced book out there with detailed howto's and explanations - but it seems like I should have been able to do this with the driver. When pulling data down, it always took around 30 seconds to get the million-row ADO recordset from MSSQL, and then 39.5 minutes to save them - all in what amounted to 90mb of data.
>Are you trying to do it thru a DTS package? I did some testing recently and the performance of a SQL-to-VFP transfer was pathetic. The other way 'round - VFP-to-SQL - is excellent.
>
>Believe it or not, I got significantly better results using a VFP remote view to suck the data out, then write it to a local table. For a 100K record table duration was measured in seconds, not minutes.
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only