BookmarkSubscribeRSS Feed
tejeshwar
Calcite | Level 5
Hi,

I have a SAS dataset which is 800MB in size with 15-20 variables which would be source to a SAS report on the SAS Web report studio. I created an index on this dataset and the file gets created with an extension .sas7bndx.

The index was created on 4 attributes (PORTFOLIO PRODUCT YEAR MONTH). These are the exact same attributes on which any user can filter data before viewing the actual report.

I tried testing the index on Base SAS by filtering the data on one of the attributes and resuts are below.

Results from Index Dataset - - - > 27 seconds
Results from Non - Index Dataset - - -> 42 seconds.

However, the time it takes to render a report after clicking on "View Report" remains exactly the same on the WRS. Do we need to register an index on the metadata repositary seperately or does the index gets registered automatically once the SAS dataset is registered. I looked at the properties of the dataset on the SAS Management console and it does shows that the dataset is indexed on 4 attributes.

Any help will be greatly appreciated. Thanks

Tej
7 REPLIES 7
AngelaHall
SAS Employee
Tej,
You might review the difference between running a test in Information Map Studio vs running the Web Report Studio report. If the web site is slower, than performance improvements should be considered on the web tier itself. There are post configuration tasks mentioned (such as 9.1.3: http://support.sas.com/documentation/configuration/biwaag.pdf) to increase java heap and stack size.

~ Angela
http://sas-bi.blogspot.com
Madelyn_SAS
SAS Super FREQ
Hello,

I also suggest that you review this SAS note for tips on generally improving performance.: http://support.sas.com/kb/39/065.html
tejeshwar
Calcite | Level 5
Thanks Anjela and BIC_USER_3 for posting your responses on potential solutions. Anjela, could you just a little bit explain your note "You might review the difference between running a test in Information Map Studio vs running the Web Report Studio report" I mean i can definately test by running it on WRS, how should I test on Information Map Studio. I am sorry if this sounds like a dumb qq, I am relatively new to BI tools.

Also, my question is that if 2 reports are sourcing 2 similiar data and the only difference between these 2 source datasets is that one is indexed and the other is not, shouldnt there be a visible difference in response time of these reports on WRS irrespective of how the web tier is setup?

Regards,
Tej
twocanbazza
Quartz | Level 8
You may also want to review the code generated by WRS, as it may be generated in a way that doesn't use the index, hence the same response times.
AngelaHall
SAS Employee
Tej,
There is a button in the menu bar of Information Map Studio that looks like a document icon - it takes you to a test tool to run the query directly from there. This eliminates the web component as a factor in your comparison between the index/non-index source.

If you continue to see the same performance between the two source tables directly in Information Map Studio, this could be an instance where the index is not providing much value. Check on papers such as http://support.sas.com/resources/papers/proceedings09/024-2009.pdf to review index needs/uses.

~ Angela
http://sas-bi.blogspot.com
tejeshwar
Calcite | Level 5
Thanks so much Anjela.
I tried testing the queries on the Information Map and again didnt see any visible difference in response time. I was hoping to look at the queries and try and figure out if the query was using the index. The source is indexed on 4 attributes and Year is one of them.Also since I created a simple index, the index itself will not have a name. Below are the queries

Query from Index Data Source

Proc SQL; Create Table %DATA% as
SELECT
SUM(table0.ACCTS) AS DIR_30 LABEL='Accts',
table0.CUSTOM_CHANNEL AS DIR_31 LABEL='Custom Channel' FORMAT=$20.,
table0.CLIENT_ID AS DIR_37 LABEL='Client Id' FORMAT=$20.,
table0.PRODUCT_TYPE AS DIR_38 LABEL='Product Type' FORMAT=$4.
FROM
ppaldata.ACQ_TEST_SAMPLE2_TEST1 table0
WHERE
table0.YEAR = '2009'
GROUP BY
2,
3,
4
;
quit;

Query from Non Index Data Source

Proc SQL; Create Table %DATA% as
SELECT
SUM(table0.ACCTS) AS DIR_2 LABEL='Accts',
table0.CUSTOM_CHANNEL AS DIR_3 LABEL='Custom Channel' FORMAT=$20.,
table0.CLIENT_ID AS DIR_5 LABEL='Client Id' FORMAT=$20.,
table0.PRODUCT_TYPE AS DIR_6 LABEL='Product Type' FORMAT=$4.
FROM
ppaldata.ACQ_TEST_SAMPLE2 table0
WHERE
table0.YEAR = '2009'
GROUP BY
2,
3,
4
;
quit;

Is there a way I can be sure that the query used the index?Meanwhile I will also go through the paper.
Again, thanks for the all the help.

Tej
Madelyn_SAS
SAS Super FREQ
You might be interested in this paper http://www2.sas.com/proceedings/sugi30/008-30.pdf

Note that it says the following:
"The primary use of an index is to optimize a WHERE expression."

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1048 views
  • 0 likes
  • 4 in conversation