BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
rara
Calcite | Level 5

HI There,

 

I am bit confused with the usage of SPDS & SPDE in sas.

As in my knowledge SPDE is the engine and the SPDS is the sas data server.

Just came across in some article about sas data types as SPDE format & SPDS format and just wonder if both are same or different..?

Kindly get me right information on the same.

1 ACCEPTED SOLUTION

Accepted Solutions
jklaverstijn
Rhodochrosite | Level 12

Hi rara,

 

Your knowledge is correct. Where both SPDS and SPDE allow for multi-threaded data access, SPDS employs the same concept in a far more advanced way. SPDS is a licensed product. Some would consider SPDE a "poor man's" SPDS.SPDS adds DBMS capabilities like partitioning based on varriable values, access control, locking, query optimization, backup&recovery etc. If tuned and indexed properly SPDS can deliver amazing query performance combined with solid loading speeds.

 

SPDS was originally engineered to overcome the 2G filesize limit that many old operating systems had, back in the Version 6 days. Although that is now behind us the concept of partitioning data is still valid for performance reasons. Many applications can benefit by the mere specification of the SPDE engine to the libname statement (whilst observing the gotchas and usage notes). 

 

Hope this helps.

 

More details from @PatrickCuba:

SPDE is licensed with Base SAS. It partitions a dataset into mini-datasets that sit on individual spindles of disk. When you query the dataset you still query one table but underneath the table has been split as just described. Data and indexes can be split into different physical partitions as well.

 

SPDS is SPDE but as a separate server with many more features catered to treating the data like a separate server complete with ACL and command line interface. Various SAS procedures have SPDS-specific options to it.

 

Example: PROC APPEND has a UNIQUESAVE=REP option, which is quite brilliant - you update data in the Base table by the same key and add new records that do not yet exist.

 

Another thing to remember is that the SQL Optimizer bahaves differently too, In SPDS you can do things like Star Join Optimization. This reduces the number of steps required to complete a query on very big tables. 

 

Also a cool thing to try on SPDS is clustering through PROD SPDO. This allows one to create a super dataset with a "Spine" column to select which subset (spde) dataset you want to reference. This column is called MINMAXVARLIST. A great example is splitting the subsets of the cluster by Month-Year so when you run a query by selecting a particular minmaxvar value the SQL optimizer will know which subset to query.

 

I've provided quite a few keywords here you can research on yourself Smiley Happy

 

View solution in original post

8 REPLIES 8
jklaverstijn
Rhodochrosite | Level 12

Hi rara,

 

Your knowledge is correct. Where both SPDS and SPDE allow for multi-threaded data access, SPDS employs the same concept in a far more advanced way. SPDS is a licensed product. Some would consider SPDE a "poor man's" SPDS.SPDS adds DBMS capabilities like partitioning based on varriable values, access control, locking, query optimization, backup&recovery etc. If tuned and indexed properly SPDS can deliver amazing query performance combined with solid loading speeds.

 

SPDS was originally engineered to overcome the 2G filesize limit that many old operating systems had, back in the Version 6 days. Although that is now behind us the concept of partitioning data is still valid for performance reasons. Many applications can benefit by the mere specification of the SPDE engine to the libname statement (whilst observing the gotchas and usage notes). 

 

Hope this helps.

 

More details from @PatrickCuba:

SPDE is licensed with Base SAS. It partitions a dataset into mini-datasets that sit on individual spindles of disk. When you query the dataset you still query one table but underneath the table has been split as just described. Data and indexes can be split into different physical partitions as well.

 

SPDS is SPDE but as a separate server with many more features catered to treating the data like a separate server complete with ACL and command line interface. Various SAS procedures have SPDS-specific options to it.

 

Example: PROC APPEND has a UNIQUESAVE=REP option, which is quite brilliant - you update data in the Base table by the same key and add new records that do not yet exist.

 

Another thing to remember is that the SQL Optimizer bahaves differently too, In SPDS you can do things like Star Join Optimization. This reduces the number of steps required to complete a query on very big tables. 

 

Also a cool thing to try on SPDS is clustering through PROD SPDO. This allows one to create a super dataset with a "Spine" column to select which subset (spde) dataset you want to reference. This column is called MINMAXVARLIST. A great example is splitting the subsets of the cluster by Month-Year so when you run a query by selecting a particular minmaxvar value the SQL optimizer will know which subset to query.

 

I've provided quite a few keywords here you can research on yourself Smiley Happy

 

JuanS_OCS
Amethyst | Level 16

Love your explanation @jklaverstijn! 🙂

 

 

jklaverstijn
Rhodochrosite | Level 12
Thanks Juan.
LinusH
Tourmaline | Level 20

Just want to add about the file formats, is that cross reading (SPDE reading SPDS and vice versa) is possible under some circumstances. I think that SPDE can read SPDS tables if no ACT's are applied.

 

Understand that you are curious (who isn't if you are a SAS nerd?), but do you have an issue/unresolved requirement as a basis for this question? It's easier to elaborate if you know why the question is asked.

Data never sleeps
ChrisHemedinger
Community Manager

Great explanation by @jklaverstijn.  And in case it's not obvious from the reply, the SPD engine (SPDE) is part of Base SAS, so you can experiment with this in your SAS programs with no additional setup.

 

libname db spde "c:\temp\spde";
data db.cars;
  set sashelp.cars;
run;

If you examine the folder with the data, you'll see some file types that are specific to the SPD architecture.

 

spd.png

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
PatrickCuba
Obsidian | Level 7

SPDE is licensed with Base SAS. It partitions a dataset into mini-datasets that sit on individual spindles of disk. When you query the dataset you still query one table but underneath the table has been split as just described. Data and indexes can be split into different physical partitions as well.

 

SPDS is SPDE but as a separate server with many more features catered to treating the data like a separate server complete with ACL and command line interface. Various SAS procedures have SPDS-specific options to it.

 

Example: PROC APPEND has a UNIQUESAVE=REP option, which is quite brilliant - you update data in the Base table by the same key and add new records that do not yet exist.

 

Another thing to remeber is that the SQL Optimizer bahaves differently too, In SPDS you can do things like Star Join Optimization. This reduces the number of steps required to complete a query on very big tables. 

 

Also a cool thing to try on SPDS is clustering through PROD SPDO. This allows one to create a super dataset with a "Spine" column to select which subset (spde) dataset you want to reference. This column is called MINMAXVARLIST. A great example is splitting the subsets of the cluster by Month-Year so when you run aquery byt selecting a particular minmaxvar value the SQL optimizer will know which subset to query.

 

I've provided quite a few keywords here you can research on yourself 🙂

MichelleHomes
Meteorite | Level 14

Heh @PatrickCuba - when I saw this post I thought of you and your SPDE and SPDS presentation you did at the Queensland SAS user group meeting a few years ago... http://www.sascommunity.org/wiki/2012Q3-QUEST Robot Happy

//Contact me to learn how Metacoda software can help keep your SAS platform secure - https://www.metacoda.com
PatrickCuba
Obsidian | Level 7
Ha ha @MichelleHomes
That's the one

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

CLI in SAS Viya

Learn how to install the SAS Viya CLI and a few commands you may find useful in this video by SAS’ Darrell Barton.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 8 replies
  • 5776 views
  • 11 likes
  • 7 in conversation