How to improve the performance in SPDS using macro variables

[Bhoopesh]

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

like by using SPDSEV1T SPDSEV2T ...

[LinusH]

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?

[Bhoopesh]

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]

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.

[Bhoopesh]

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]

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.