topic Re: Table Index not used in Web Report Studio? in SAS Web Report Studio

Table Index not used in Web Report Studio?

Dominik4 — Mon, 12 Dec 2022 12:34:37 GMT

Hi,

we are using WRS 4.4 and I have a table with 11.400.000 observations and 568 columns.

I hoped to increase report speed when adding an index. So I created one in meta data with for example column 3, column 5 and column 7. The index physically exists.

The report shows round about 10 - 15 columns in a list, sums up a few columns and has a where clause with 5 filter conditions (off course they contain the 3 columns of the index).

At the end the report does not load faster. I already tried to preorder the data before writing the table but that did not help. In map studio it is not visible if he uses the index or not.

Any Ideas how to speed up with an index?

Re: Table Index not used in Web Report Studio?

Kurt_Bremser — Mon, 12 Dec 2022 13:55:05 GMT

An index will only benefit you if you extract a sufficiently small (think 10% or less) subset of your data. Otherwise, the overhead of reading the index file actually increases time.

Keep in mind that a SAS dataset is organized in pages, and that a whole page (128K) needs to be read anyway if a single observation from it is needed.

Re: Table Index not used in Web Report Studio?

LinusH — Mon, 12 Dec 2022 14:03:17 GMT

It's been awhile since I worked with WRS and Information maps.

The key is to somehow capture the queries that the information map generates towards the Pooled WS server.

Then take those queries and run it outside WRS, with

options msglevel=i;

As you might know it's important how the index is defined vs the query is written, e.g., the first column in the index must be part of the where clause.

If you wish to speed up the queries, you might want to consider moving the data to a SPDE libref for faster table scans, and slightly more advanced where clause optimizer.

Re: Table Index not used in Web Report Studio?

Kurt_Bremser — Mon, 12 Dec 2022 14:09:40 GMT

Those 568 columns look suspicious to me. In all my professional work, I had datasets with such numbers only for regression analysis, but never for reporting.

Consider stripping down or restructuring your dataset (if there's data in structure, see Maxim 19).

Re: Table Index not used in Web Report Studio?

Madelyn_SAS — Mon, 12 Dec 2022 14:32:06 GMT

There are some performance suggestions in the documentation:

https://go.documentation.sas.com/doc/en/bicdc/9.4/biwaag/n1ohgyo7p5a1ien1p13q9nurdysx.htm

Re: Table Index not used in Web Report Studio?

Dominik4 — Tue, 13 Dec 2022 07:24:54 GMT

@Kurt_Bremser

Thank you. Sorting Data and 3 (from over 500) columns in index should be much smaller that reading the data source.

Moreover you are right. Having 568 columns is rubbish and I'm already working on that.

@Madelyn_SAS

Thank you. I will read it.

@LinusH

renamed the data file to get an exception with the sql. Seems although I have 5 filters the sql does not contain a where clause. So this would be a reason why index is not working. Does it makes sense that he first load all data in a temp data set to filter afterwards??

Re: Table Index not used in Web Report Studio?

LinusH — Tue, 13 Dec 2022 08:40:17 GMT

Again, I'm a litle rusty.

Not sure what you mean by creating a temporary table? You don't refer to the query cache...?

In case yo can't get an indexed search, you want to restructure your data, as @Kurt_Bremser points out. If you have several reports with with not much overlapping information, you could consider splitting your data set in to multiple.

For where clause, I'm not sure how the information map acts if the filters is only defined in WRS (the documentation leaves a lot to wish for when comes to query tuning). If it works for your use case you could try to define filters in the information map, and have them prompted in the WRS report?

Re: Table Index not used in Web Report Studio?

Kurt_Bremser — Tue, 13 Dec 2022 13:57:12 GMT

Yes, the index file will be much smaller than the dataset file. But if the index doesnt't keep you from still reading most, if not all, of the dataset file itself, it only adds additional I/O. And if your "want" observations are scattered randomly across the dataset pages, this will happen even with a small subset.

If you also have the MDDB server at hand, consider creating and using a cube.

Re: Table Index not used in Web Report Studio?

LinusH — Tue, 13 Dec 2022 16:08:54 GMT

I think that OLAP Server is the product that should be considered, MDDB server is a SAS V8 legacy construct.
Having Infomaps and WRS - it's likely OLAP Server is licensed as well with the EBIS offering.

Re: Table Index not used in Web Report Studio?

Kurt_Bremser — Tue, 13 Dec 2022 16:11:30 GMT

You're right, it's OLAP. At my age, long-term memory tends to override short-term 😉

Re: Table Index not used in Web Report Studio?

Dominik4 — Wed, 14 Dec 2022 06:12:58 GMT

Thank you for your feedback.

We also use the olap feature but in this case it is prefered to have a relational table.

Splitting Data is the idea for the future. I search for a fast solution while we are working on that.

@LinusH

Based on your idea I created a chapter filter (hope it is the correct description, I dont have a English WRS) and now I can see that the SQL has an where clause. Unfortunately the where clause contains a strip command that prevents using the index.

WHERE (STRIP( ( table0.myColumn ) )) IN ( '1D' )

Any ideas how to avoid that strip command? I guess then I reached by goal and he uses the index

Re: Table Index not used in Web Report Studio?

Kurt_Bremser — Wed, 14 Dec 2022 06:48:51 GMT

Why not strip the variable when the dataset is created?

Re: Table Index not used in Web Report Studio?

Dominik4 — Wed, 14 Dec 2022 07:53:43 GMT

The data is stripped in dataset. The issue is that if the sql contains the strip command, the index is not used because he tries to transform again

Re: Table Index not used in Web Report Studio?

LinusH — Wed, 14 Dec 2022 07:56:24 GMT

Sorry, I think we've reached my WRS knowledge limit...
I suggest you open a track to SAS tech support to get som detailed insight on the "WRS engine".

Re: Table Index not used in Web Report Studio?

Kurt_Bremser — Wed, 14 Dec 2022 08:42:36 GMT

And I guess this is SQL created by the information map, over which you have no control. I also suggest to get in contact with SAS TS.

Re: Table Index not used in Web Report Studio?

Madelyn_SAS — Wed, 14 Dec 2022 14:20:48 GMT

If there is an appropriate index and the SQL optimizer determines that using the index will improve performance, the SQL optimizer uses the index without being told to do so. The optimizer is not 100% correct in every case. Although there are SQL commands that will force the use of an index, you do not have control over the PROC SQL that is automatically generated by the query layer used by Web Report Studio.

Re: Table Index not used in Web Report Studio?

Dominik4 — Thu, 15 Dec 2022 07:02:02 GMT

Thank you for all that feedback.

Its a pitty that I dont have control over the named strip function. Off course the SQL optimizer does not choose an index if the statement contains a strip function in the where condition.

But I found a some kind of workaround. Predefining the same Filter in Maps does not cause the strip function and so the index is working then.