Charlie, I echo your sentiments. I got bitten by a similar problem with not as many records but a very very complicated "join" between tables and I found the time to be in excess of 2 hours to finish the processing. A quick change in the same vein as yours and I got it down down to about 25 seconds. Since then I've steered away from views completely
I guess that some of my "distaste" of views is completely based around some bad bad experiences of them, especially when the views in question change on a regular basis, (at least until I created "personal DBC's" to hold them in), so I prefer to stick with what I know - and trust best. To each his own as they say! Despite Andy's LOVE of them somehow they just don't seem to be "real programming!!!" and you know what they say about "old habits". Maybe I should revisit them after about 5 years absence! Dave Crozier The secret to staying young is to live honestly, eat slowly, and to lie about your age -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Charlie Coleman Sent: 04 August 2006 19:36 To: [email protected] Subject: VFP Views (ugh) large data sets, replacing data Well, just got bit by something. Yes, it was because I used the "MS-standard" approach. Views in this case. I usually avoid Views like the plague, so I'm not an expert on them. And I wasn't expecting what was happening. For others of you out there that work with them a lot, you may have already known this. So apologies in advance if this is 'old hat.' So, anyway, in this system we load a VFP database with records from a 'sent in' VFP database. And, in addition, we load a SQL DB with the same data. The SQL DB is updated by using Views. For the most part, the updating is Appends (a pass-through DELETE takes care of removing anything necessary). Recently, the SQL DB was decided to be partitioned. It was struggling with the 100million rows placed in it. So, as a result, we had to add 2 more fields to the table(s) in the SQL DB. These fields of information do not exist in the VFP (nor filed) data. They are solely there for the purpose of supporting the DB partitioning (basically, the partitioning takes the 1 big table and splits it into multiple smaller tables, using specific key/id info so that an overall SQL DB View can be created that looks like a single table to people connecting to the DB - again, sorry if you already knew that). So, the process was set up to be something like: 1) open the View with - NODATA clause - table buffering mode 2) append from the filed data 3) scan through the View and update all the records (e.g. fill in 1 or 2 additional fields) 4) invoke the TABLEUPDATE() We found out that step 3) was taking over 6 hours (it never did complete). Doing some detailed logging, the first 1,000 records would take less than a second to update. But by the time you got to records 100,001 to 101,000 it was taking about 20 seconds (that is 20 seconds per 1,000 records). That progression continued - i.e. if we would have let it continue, it probably would have gotten to where it would take over a minute to update 1,000 records. So we changed the process to: 1) open the View with - NODATA clause - table buffering mode 2) create a temporary VFP cursor from the sent in data, and make holding place fields as needed (SELECT *, SPACE(20) AS special_field1, 0000 AS spec_2...) 3) SCAN through the VFP cursor and make the necessary replacements in the special fields 4) append data from the VFP cursor into the View (the special fields match with the SQL View) 5) invoke the TABLEUPDATE() function In this approach, step 3) took less than 10 seconds. Yep, 10 seconds compared to 6+ hours. (VFP's local data engine to the rescue once again! <g>). This is all just FYI. No need to respond .... "... you should be doing DTS...", or "...why not create XML..." or "...SQL Server rocks, you are just too dumb to use it right..." and so on. I just wanted to give a heads up in the event others are working with Views under similar circumstances. PS. I wonder if MySQL, PostgreSQL, FireBird... Views would exhibit the same problem. Maybe I'll check that out someday..... Have a great weekend all. -Charlie [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
