BookmarkSubscribeRSS Feed
Bhoopesh
Calcite | Level 5

How to improve the performance in SPDS using macro variables..?

like by using SPDSEV1T SPDSEV2T ...

5 REPLIES 5
LinusH
Tourmaline | Level 20

These macro variables are described in the documentation.

The documentation may be a little hard to really under stand.

Is there anything specific you wonder about?

What kind if processing/query do you wish to improve?

Data never sleeps
Bhoopesh
Calcite | Level 5

Hi Linush,

We have a couple of tables in which there is a index on particular column.

How to improve the Where clause on these tables... There are certain macrovariables in SPDS that can help in improving the performance.

So need to improve the Where clause and compression and IO activit....?

LinusH
Tourmaline | Level 20

First, do you queries use index during where clause evaluation? Use spdswdeb=yes to get a planner summary in the log.

SPDSEV1 is used when an index strategy is used , see above (which I believe is your goal).

The way you can affect performance using this variable is to avoid sorting on the result set, by setting it to 2, you will maximize performance. If it will give any effect depends on the size of the result set, and whether the source table has an explicit sort flag on it.

Data never sleeps
Bhoopesh
Calcite | Level 5

Hi Linush,

Thanks for your suggestions. I will use this spdssev1 option and will set it to 2.

I have one more problem, which might be helpful from your side.

Hi,

I have a table that should be partioned as 256M.

But since in spdsserv.parm file MINPARTSIZE is defined as 512M.

While creating a new dataset, I m using macrovariable %let spdssize=256;

But still partinoning is done as 512M.  I am not sure why it is happening in SPDS....

But when i am giving the upper limit %let spdssize=1048. It is overwriting the MINPARTSIZE....

Why the partition size is not reduced to 256M....Any suggestions

Sample Code:

%let spdssize=256;

data test(partitionsize=256);

set abc;

run;

Above code is working fine but partitioning is done at 512M rather than 256...????? I dn knw why

LinusH
Tourmaline | Level 20

The MIN part of suggests that this setting is for a lower boundary for the partsize. It means that user can chose any partsize they like, as long it's not smaller than MINPARTSIZE.

If you want to allow small values, you need to adjust the spdsserv.parm file.

Data never sleeps

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 890 views
  • 5 likes
  • 2 in conversation