Architecting, installing and maintaining your SAS environment

What is the difference between SPDS & SPDE ?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

What is the difference between SPDS & SPDE ?

[ Edited ]

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.


Accepted Solutions
Solution
‎02-24-2017 07:56 AM
Super Contributor
Posts: 436

Re: What is the difference between SPDS & SPDE ?

[ Edited ]

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


All Replies
Solution
‎02-24-2017 07:56 AM
Super Contributor
Posts: 436

Re: What is the difference between SPDS & SPDE ?

[ Edited ]

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

 

Trusted Advisor
Posts: 1,307

Re: What is the difference between SPDS & SPDE ?

Posted in reply to jklaverstijn

Love your explanation @jklaverstijn! Smiley Happy

 

 

Super Contributor
Posts: 436

Re: What is the difference between SPDS & SPDE ?

Posted in reply to JuanS_OCS
Thanks Juan.
Super User
Posts: 5,424

Re: What is the difference between SPDS & SPDE ?

Posted in reply to jklaverstijn

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
Community Manager
Posts: 2,952

Re: What is the difference between SPDS & SPDE ?

Posted in reply to jklaverstijn

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

Occasional Contributor
Posts: 14

Re: What is the difference between SPDS & SPDE ?

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 Smiley Happy

Trusted Advisor
Posts: 1,319

Re: What is the difference between SPDS & SPDE ?

Posted in reply to PatrickCuba

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

Occasional Contributor
Posts: 14

Re: What is the difference between SPDS & SPDE ?

Ha ha @MichelleHomes
That's the one
☑ This topic is solved.

Need further help from the community? Please ask a new question.

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