With the Wicket Java Web framework where a lot of magic happens behind the scenes, and almost no XML needs to be configured. Most of it is done with Java. It has a lot of pretty cool features. I just came across this one.

In the init setup of your class that extends WebApplication, you can “mount” your pages at paths, like this:

@Override
protected void init() {
	super.init();
	mountBookmarkablePage("/search", SearchPage.class);
}

So when you goto http://somewhere.com/somecontext/search, it gives you the SearchPage class.

You can also specify a HomePage index, like this:

public Class getHomePage() {
	return IndexPage.class;
}

Then when you goto http://somewhere.com/somecontext/, it gives you the IndexPage class.

The cool subtle thing here is what happens when you do them both; i.e. you mount SearchPage to /search, and you override getHomePage to return SearchPage.class. Do this, and when you goto http://somewhere.com/somecontext/, it will redirect to http://somewhere.com/somecontext/search!

Everyone should learn the difference between Union and Union All. Knowing it will make you a better programmer, and it’s fairly trivial to understand.

SELECT * FROM apples
UNION
SELECT * FROM oranges

When you know for a fact that there will never be any common rows between the apples table and the oranges table, this query will be slightly faster with at low cardinality, and incredibly faster at high cardinality by using “UNION ALL”

SELECT * FROM apples
UNION ALL
SELECT * FROM oranges

The difference between the two queries is this: UNION ALL will simply concatenate the two queries together into the resultset. Just using UNION will concatenate, but then remove duplicates (do a distinct sort). Leaving out this second step can vastly reduce the time it takes for your query to run.

In SAS DI Studio 3.4 (and I imagine in future versions), the prepackaged code for the SCD Type-2 Loader works like this: Does the dataset exist? If not, create an empty dataset with structure and indexes as defined from metadata. Then detect differences between it and the source dataset and the target dataset, expire any observations that are modified or deleted by setting their valid-to-date to now, and append any modified or new observations with a valid-from-date. The expire bit is done in-place with a data step modify statement, and the append is done with PROC APPEND. I assume this is done to reduce the amount of locking necessary on the dimension dataset. Because new observations are appended, the dataset never actually gets sorted by the business key, so this could lead to exponential growth over time on the expire bit; every time the transform wants to change a single observation’s valid-to-date, it scans the entire table. And it doesn’t help that compression is off by default.

In the instance below, we had an uncompressed dataset with a modest 220,000 rows. The dataset had a variable “description” defined as a 4000-length string which was usually but not always null. Most steps of the SCD Loader run in a few seconds, but the following is usually the bottleneck. On a run one night to populate a new variable, this data step ran in a little over 9 hours:

     data PRESTAGE.LOAD_SW_ENTITY_RELEASE_X_HOST;
        modify PRESTAGE.LOAD_SW_ENTITY_RELEASE_X_HOST
           work.etls_close
            (rename = (ETLS_KEY = SW_ENTITY_REL_X_HOST_ID
           ETLS_FROMDATE = VALID_FROM_DTTM))
            updatemode=nomissingcheck;
        by SW_ENTITY_REL_X_HOST_ID VALID_FROM_DTTM;
        VALID_TO_DTTM = ETLS_CLSDATE;
        if %sysrc(_SOK) eq _iorc_ then
           replace;
        _iorc_ = 0;
        _error_ = 0;
     run;

NOTE: There were 1 observations read from the data set PRESTAGE.LOAD_SW_ENTITY_RELEASE_X_HOST.
NOTE: The data set PRESTAGE.LOAD_SW_ENTITY_RELEASE_X_HOST has been updated.  There were 45924 observations rewritten, 0
     observations added and 0 observations deleted.
NOTE: There were 45924 observations read from the data set WORK.ETLS_CLOSE.
NOTE: DATA statement used (Total process time):
     real time           9:06:00.46
     cpu time            9:05:58.43

Turning compression on reduced the size of this dataset from 1.1 gigs to 4.5 megs, mostly from compressing the 4000-char string that was usually empty; with compression off, an X-length string always takes up X bytes because it’s faster to seek to a certain observation if all observations are the same size. Additionally, in metadata an index was defined on the two variables used above in the BY statement. I ran this PROC DATASETS statement to create the index by hand (the Loader would create them if the table didn’t exist, but it assumes indexes exist if the table exists).

proc datasets library=prestage nolist;
 modify load_sw_entity_release_x_host;
 index create ndx=(sw_entity_rel_x_host_id valid_from_dttm) / unique;
quit;

With this metadata in place, the SCD Loader uses the index in the datastep and generates the following datastep instead:

     data PRESTAGE.LOAD_SW_ENTITY_RELEASE_X_HOST;
        set work.etls_close(rename = (ETLS_KEY = SW_ENTITY_REL_X_HOST_ID
                                      ETLS_FROMDATE = VALID_FROM_DTTM));
        modify PRESTAGE.LOAD_SW_ENTITY_RELEASE_X_HOST
            key=ndx / unique;
        VALID_TO_DTTM = ETLS_CLSDATE;
        if %sysrc(_SOK) eq _iorc_ then
           replace;
        _iorc_ = 0;
        _error_ = 0;
     run;

By using the index, the runtime was reduced to under two seconds.

NOTE: There were 45924 observations read from the data set WORK.ETLS_CLOSE.
NOTE: The data set PRESTAGE.LOAD_SW_ENTITY_RELEASE_X_HOST has been updated.  There were 45924 observations rewritten, 0
     observations added and 0 observations deleted.
NOTE: DATA statement used (Total process time):
     real time           1.41 seconds
     cpu time            1.21 seconds

In SAS 9.1.3 and prior, Options list (such as FMTSEARCH for libraries with format catalogs, and SASAUTOS for paths that contain macros shared across jobs) were annoying to work with. If you have nested code that wants to add a library or a path the list, doing so like this could potentially clobber statements executed outside of the nested block:

OPTIONS FMTSEARCH=(mylib.formats);

To really be safe, you want to add your library to the list. Doing this wasn’t very intuitive. I had always done this like this:

%let FMTSEARCH = %substr(%sysfunc(getoption(fmtsearch) ) ,2);
OPTIONS FMTSEARCH = (mylib.formats &FMTSEARCH;

But in 9.2, there are two new System Options, INSERT= and APPEND=. This is now as simple as

OPTIONS INSERT=(FMTSEARCH=mylib.formats);