Hi Ted:
Sorry, it's 3 million (3000K) rows in the source. Here's a
concrete example. Suppose we want to report financial statements for a
number of accounts & account owners in a table that looks like:
Owner Saving Series.......................... Checking series........
Invest series...................
My__Acct Savings0190 Savings0290 ... Savings0906 Check0190 Check0290 ...
Invest0190 Invest0290 Invest0906
WifeAcct Savings0190 Savings0290 ... Savings0906 Check0190 Check0290 ...
Invest0190 Invest0290 Invest0906
Kid_Acct Savings0190 Savings0290 ... Savings0906 Check0190 Check0290 ...
Invest0190 Invest0290 Invest0906
Dog_Acct Savings0190 Savings0290 ... Savings0906 Check0190 Check0290 ...
Invest0190 Invest0290 Invest0906
In other words, end of month Savings, Checking, Investments and whatever
from Jan 1990 to the present for a large number of accounts and owners
We've got source data that look like
Acct Type Date Total
My__Acct Savings 0190 $5.00
My__Acct Check 0190 $4.00
My__Acct Invest 0190 $0.20
WifeAcct Savings 0190 $15.00
WifeAcct Check 0190 $-5.00
WifeAcct Invest 0190 $0.50
My__Acct Savings 0290 $5.00
My__Acct Check 0290 $4.00
My__Acct Invest 0290 $0.20
WifeAcct Savings 0290 $15.00
WifeAcct Check 0290 $-5.00
WifeAcct Invest 0290 $0.50
...etc
Hope this is clearer. I realize that we're not actually cross tabing in
that the target columns are already defined, but the fill-in-the data
part of a cross-tab chore seems the same.
Everything's on the same disk & the client will not be able to alter
these resources (target machines are laptops).
I'm not ready to drop the tags yet since set relation ... is
outperforming scan <source> seek <Source.key> in target. Currently, we
start with the earliest date eg 0190, infer a col name (Savings0190),
set relation to acct# and replace all ... For date = ... Then, advance
the date until we're done with savings, move on to Checking etc...
Seems like there should be something faster, but darned if I can find
it. I'll give table buffering a try, but since my task manager's page
file usage history graph isn't showing much activity I figured the
process is pretty much buffered on the windows level anyway.
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Ted Roche
Sent: Monday, September 11, 2006 4:53 PM
To: [EMAIL PROTECTED]
Subject: Re: Cross-tab like performance help, please
On 9/11/06, Lew Schwartz <[EMAIL PROTECTED]> wrote:
I spent a while trying to follow your data diagram, and I'm still not
sure I get it. A concrete example with a couple of rows might make
sense. That said, I don't think you have a true cross-tab, or I'd
recommend the built-in one or Val Matison's killer replacement.
>
> The problem is to make this process take place a quickly as possible.
> We're talking 300K rows of data and an upload time of 1.5 - 3 hours.
That does seem slow. Are the source and target both local, and both on
the same disk? Is there room for this amount of data with lots of room
for temp files and swap?
When you say "upload" you mean the time for the process to complete?
That is really slow, if I understand what you're doing.
> So
> far, code which iterates through the target 1 column at a time with a
> replace all... performs better than sql updates
SQL is efficient for standalone queries, but needs to be tuned for DP
applications. It's more likely xBase commands will out-perform. Have you
indexes on your target files? Drop them.
> and *much* better than
> code which constructs an entire (or partial) row of data and updates
> the entire row in 1 shot. (I don't understand this last result).
That does seem non-intuitive. Typically, the costliest operation in a
data transform like this is I/O to disk. Are you buffering? Consider it,
with a flush (TableUpdate()) at a programmatic interval (after every row
is filled, every 10, 100, 1000, etc.) to see if there's an effect.
--
Ted Roche
Ted Roche & Associates, LLC
http://www.tedroche.com
[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.
