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
_______________________________________________
Post Messages to: ProFox@leafe.com
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.
