Here are some thoughts:
- Filters: Multiple Filters perform faster than a single filter.
- Range Formulas: Don't ask the obviously true question repeatedly.
- Find Replace vs Join.
Filters:
When working with large sets of data this is more apparent and the benefits can add up. Suppose you are looking at a National file and want to find all households with children that own their own home and live in a specific geography. This might look like:
Contains([CHILDREN: AGE 0-18 VERSION 3],"Y") AND
[HOMEOWNER: COMBINED HOMEOWNER-RENTER] == "H" AND
[FIPS ZIP CODE] == '49033'
You would actually be testing more conditions (in the US you would be testing 400+MM conditions) than if you first tested for Postal Code then filtered for Residency and/or Presence of Children separately. The Postal Code would net you a few thousand records. The subsequent filter/tests would get you down to the few records faster.
There are 169 households out of 139,709,868 records. With one filter the job runs in 3:09 minutes. With three filters the job runs in only 2:08 minutes.
Range Formulas:
I've seen this often:
IF
[LENGTH OF RESIDENCE] == 0 THEN 'Less than 1 year' ELSEIF
[LENGTH OF RESIDENCE] >= 1 AND
[LENGTH OF RESIDENCE] <= 5 THEN '1 to 5 years' ELSEIF
[LENGTH OF RESIDENCE] >= 6 AND
[LENGTH OF RESIDENCE] <= 10 THEN '6 to 10 years' ELSEIF
[LENGTH OF RESIDENCE] >= 11 AND
[LENGTH OF RESIDENCE] <= 15 THEN '11 to 15 years' ELSEIF
[LENGTH OF RESIDENCE] >= 16 AND
[LENGTH OF RESIDENCE] <= 20 THEN '16 to 20 years' ELSEIF
[LENGTH OF RESIDENCE] >= 21 AND
[LENGTH OF RESIDENCE] <= 25 THEN '21 to 25 years' ELSEIF
[LENGTH OF RESIDENCE] >= 26 AND
[LENGTH OF RESIDENCE] <= 30 THEN '26 to 30 years'
ELSE
'More than 30 years'
ENDIF
When processing the IF statement, you can shortcut the coding and run time with this logic:
IF
[LENGTH OF RESIDENCE] == 0 THEN 'Less than 1 year' ELSEIF
[LENGTH OF RESIDENCE] <= 5 THEN '1 to 5 years' ELSEIF
[LENGTH OF RESIDENCE] <= 10 THEN '6 to 10 years' ELSEIF
[LENGTH OF RESIDENCE] <= 15 THEN '11 to 15 years' ELSEIF
[LENGTH OF RESIDENCE] <= 20 THEN '16 to 20 years' ELSEIF
[LENGTH OF RESIDENCE] <= 25 THEN '21 to 25 years' ELSEIF
[LENGTH OF RESIDENCE] <= 30 THEN '26 to 30 years'
ELSE
'More than 30 years'
ENDIF
Once the first test is passed, you need not ask the lower range question again.
Timings for "Before" on 135,708,968 records are: 3:24 minutes versus 3:06 minutes. The code is easier to create this way, but you could actually improve the performance by knowing your data. If you know where your skew is in residency, you could code to asking that question first.
Join vs Find Replace:
Join is AMAZING if Left and Right inputs have the same record count and order. Suppose that you've used a select tool and done some functions to create new columns of data that you want to join back to the original data. You can use the join and use the "Join by Record Position" configuration (radio button). You can't get any faster than that. Your data is NOT sorted.
If you are joining a Large set of data to a small set of data (Large is large and small is < 32,000 rows) the Find Replace offers you the ability to put the small data into memory and avoid the expensive sort. Imagine with those 138 million records using a TILE tool and then looking up the eight (8) length descriptors from a text input tool. The JOIN version runs in 3:40 minutes and the FIND REPLACE version runs in 3:09 minutes. Adding to that difference, your data is now in LOR order so you might have to resort the data later.
FIND REPLACE also allows for case insensitive and partial matching. If you're ever applying descriptions or labels to values, this is the way that I'd recommend you doing that substitution.
Cheers,
Mark
Alteryx ACE & Top Community Contributor
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please
Subscribe to my youTube channel.
