Table Viewer - Lazy Loading/Pagination

[danila-grobov]

We work with moderately large datasets ~6m rows.

Currently I can't even view the dataset, it just shows up as empty.

I suspect that this is due to it loading the whole thing at once.

I would like to contribute, and try to solve this issue.

I like the implementation in Enterprise Guide so I will try to implement something similar, where you scroll and it loads as you go.

[danila-grobov]

Forgot to mention, I'm talking about SAS 9.4 here. The issue is probably not very relevant for the modern SAS.

I took a closer look at the issue, and it's not related to pagination. The pagination (lazy loading) is already implemented, it's just very slow for datasets that have a lot of columns. It takes like 30 seconds to get 100 rows and then it's 30 seconds each time it loads a bit more.

I believe that this is due to the way data is being transported. The selected rows in the dataset are just printed out in the log and that is being interpreted on the client. And since pretty print is being used, that's a lot of rows.

If I turn off the pretty print it speeds up, it takes around 5 seconds, which is a lot better. However there is another problem that comes with that, now we are hitting the limit of line length in SAS logs. Since it's not pretty printed, everything is just dumped into a single line. This results in the data being cut off.

All of this seems like trying to solve the issue with the wrong tools. There is the SAS IOM data provider which is specifically made for this kind of data retrieval. There is even an adapter for local files, which would make it possible to read sas7bdat files locally. I believe it just comes bundled with SAS client tools? Not sure what's then policy with dependencies like that in this extension.

I will try to work on a POC and create a PR to migrate to that instead.

What do you guys think?

[scnwwu]

Looks similar to #1233

I've tried the data provider approach but it crashes Powershell.
#1132 (comment)

[danila-grobov]

Oh, cool, thank you for the context!

I will keep in mind the crashing issue. Haven't encountered it yet.

[rwat1]

This might be the nail in the coffin if this isn't fixed. I will tell my wife to also learn R and quit SAS for good.

[DmitryMK]

@danila-grobov I agree with you, transferring data in SAS log does not look right. I'm interested if you succeed with it. I have created VDE Dataset Viewer and would love to add a functionality to connect to a SAS session to see datasets in the working library. Thanks @scnwwu for the link with the development code. I will try to look into it as well.

If I turn off the pretty print it speeds up, it takes around 5 seconds, which is a lot better. However there is another problem that comes with that, now we are hitting the limit of line length in SAS logs. Since it's not pretty printed, everything is just dumped into a single line. This results in the data being cut off.

I have used gzip compression and later converted it to BASE64, which made it significantly faster. It is system specific and may not work in your environment (the code below is for SAS running on Linux). Mainly the update is simple, by changing ItcLibraryAdapter.ts

      * Get a temporary name;
      %let tempName = %sysfunc(pathname(work))/dataVSCode&sysJobId.;

      filename out zip "&tempName." compression=9 gzip;
      proc json nokeys out=out trimblanks; export work.${tempTable}; run;

      x "cat &tempName. | base64 -w255 > &tempName.b64";

      * Read contents to the log;
      %put <TABLEDATA>;
      %put <Count>&COUNT</Count>;
      data _null_;
          infile "&tempName.b64";
          input;
          put _infile_;
      run;
      %put </TABLEDATA>;

      * Remove all temp files;
      filename out clear;
      x "rm -f &tempName. &tempName.b64";

And then reading the data as gzip:

    // Remove newlines
    output = output.replace("\n", "");
    // Data is in form of BASE64 gzip file. We need to decode it first;
    const gzippedData = Buffer.from(output, "base64");

    const inflatedData = zlib.gunzipSync(gzippedData).toString("utf8");

    const rows = inflatedData.slice(inflatedData.indexOf("{"));

I do not think a PR makes sense, because it is OS-specific solution.

[danila-grobov]

Hi, I'm still working on it, but it looks promising. I'm not experiencing the crashing problems.

I'm at the point where I can see and browse the dataset. But for some reason when I turn on SAS formats, so that it applies formats defined in SAS, everything becomes way slower, instead of 1.5 seconds it goes down to 7.

I mean it's still better than it was, but I would like to squeeze out as much as possible 😅

[danila-grobov]

@scnwwu I've created a PR, would be great if you could take a look!