SAS9 Business Intelligence Blog

Lundi 11 septembre 2006

SAS9 Performance Test / SPDE vs BASE SAS!

EN : I'm currently working on SAS9 Architecture and I would like to share some results about the base SAS performance. The purpose was : what advantage could give us SPDE ? What are the index options ?

SAS 9.1 comes with SPDE (Scalable Performance Data Engine = partitioned sas files on multiple FS) wich allows to a SAS Process to scale up on the same box (just changing libnames). In my case, I did some test on fact table of 28 Million rows on a 8 proc unix box limiting SAS to 8 threads and partition = 128 Mb. Have a look also on the unix top screen shot at the end of the article to see SPDE in action ;)

Here are the results :

	Target	Create a table with indexes

PGM id	step	comment	index	target format	# of record	realtime	CPU time
010	1	Copy a table from SAS8 to SAS9 / data step / set statement	post	SPDE	28 687 145,00	07:36,0	06:56,7
	2	index with proc dataset (order & order line)	post	SPDE	28 687 145,00	02:00,4	05:09,1
						09:36,4	12:05,8

011	1	Copy a table from SAS8 to SAS9 / data step / set statement	on the fly	SPDE	28 687 145,00	09:35,4	0:11:18
		index on the fly with index table option (order & order line)

013	1	Copy a table from SAS8 to SAS9 / data step / set statement	on the fly	V9 BASE	28 687 145,00	15:33,5	0:10:41
		index on the fly with index table option (order & order line)		V9 BASE

						Accelerator	38,37%
							:


	Target	Merge 2 indexed tables and maintain indexes

PGM id	step	comment	index	target format	# of record	realtime	CPU time
030	1	merge only	post	V9 BASE	11 904 271,00	03:16,2	0:03:02
	2	index with proc datasets		V9 BASE		00:48,0	0:01:03
						04:04,2	04:05,2

PGM id	step	comment	index	target format	# of record	realtime	CPU time
031	1	merge with index on the fly	on the fly	V9 BASE	11 904 271,00	03:46,8	0:04:02

PGM id	step	comment	index	target format	# of record	realtime	CPU time
032	1	merge with index on the fly	on the fly	SPDE	11 904 271,00	02:27,0	0:04:16

						Accelerator	35,20%

You could see that SPDE processing saves 38% on a data ... set ... run; statements, and SPDE is more efficient on "on the fly index". Those results are confirmed by a merge test (target 2).

I will give other figures soon about Proc OLAP vs Proc MDDB.

FR : Je travaille actuellement sur l'architecture SAS9 et je voulais partager avec vous quelques résultats de SAS9 autour de la performance apportée par SPDE (module SAS/BASE !). La question est : Quel avantage pour SPDE ? Quelles options d'index ?

SAS9.1 viens avec SPDE (Scalable Performance Data Engine = les tables SAS sont explosées sous forme de n partitions sur n disques), ce qui permet à un process SAS d'escalader les processeurs sur un même serveur, juste en changeant un libname.

Dans mon cas, j'ai réalisés des test avec une table de 28 milion de record sur un serveur unix 8 proc, en se limitant à 8 thread par process, avec des partitions de 128 Mb.

Jetez un oeil sur le screenshot du top unix à la fin de l'artcile pour voir SPDE en action ;)

Vous pouvez constater dans la table ci dessus que SPDE me permet de gagner 38% sur un process d'alim. classique (data ... set ... run), et que SPDE est plus efficace sur un index à la volée si l'on parle d'une table ex nihilo. Ces résultats sont confirmés par le test numéro 2 (merge entre 2 tables avec Indexs).

J'èspère bientôt pouvoir vous donner des résultat autour de la Proc OLAP vs Proc MDDB !

SPDE in Action / SPDE en Action !

par Renaud Harduin publié dans : SAS9
ajouter un commentaire commentaires (0) créer un trackback recommander

Jeudi 29 décembre 2005

Fews Word on JSF & my code

Just after having posted the previous article I got some mail asking me for small detail about the way of connecting and in addition about JSF from SAS programmer.

In that sample, I just connected a Java Method "string btConnect_action() {...}" into my jsp BackBean, meaning that if I create a JSP named login.jsp, JSCreator will create for me a backbean named login.java wich extends AbstractPageBean. The method will be "linked" for me by Creator customizing JSF tag like :

___________

<h:commandButton action="#{login.btConnect_action}" binding="#{login.btConnect}" id="btConnect"
style="left: 432px; top: 240px; position: absolute" value="Connect"/>

Into that method, I connect to my SAS Metadata Server (I don't chek user ... because I'm just prototyping, but I'll take time to go back on that topic):

___________

Try {

uib.setIOMI( workspace.makeOMRConnection(serverName,serverPort,serverUser,serverPass));
...

}

If I get anykind of Exception, I catch it and send for a message visualisation page, or the next page returning the appropriate value :

___________

....

catch (MdException e)
          {
             String msg = e.getSASMessageSeverity() + " / " + e.getSASMessage()
                                + " / " + e.getLocalizedMessage();
             getErrorManagementBean().setMessage(msg);
             return "notlogged";
          } catch (Exception e)
          {
             String msg = e.getLocalizedMessage();
             this.getErrorManagementBean().setMessage(msg);
             return "notlogged";
          }

            return "logged";

The message is loaded into a bean for my convinience. Then, this makes the navigation flow works...

If the "page" returns a "logged", the JSF process it and transfer the user to "CubeSelector.jsp". If the message is "notlogges", the engine tranfers us to messageViewer.jsp that displays the messageBean contents.

This is very comfortable for the navigation flow management, gives more flexibility for java dev avoiding some very long stream of If () else if ()... So I let you discover. I'll post other story about those experiment ;)

par Renaud Harduin publié dans : SAS9
ajouter un commentaire commentaires (0) créer un trackback recommander

Mercredi 28 décembre 2005

JSF with SAS9 ;) ... and SAS9 Code Snip

Since few weeks, we re-started to work on SAS9 implementation. In order to prototype efficently, I searched for a JSF IDE. I was used to have net beans, and I found in free download Sun Java Creator Studio :

http://developers.sun.com/prodtech/devtools/free/

What was my surpise, to develop with a UI with the same functionalities as VS (more or less;) ) :

For example double clicking on connect this builds for you all the necessary call back in order to implement your code into your BackBean, with full access to your Session & Application Jbeans (getter and setter are written for you):

_______________________________________

public String btConnect_action() {
this.collectUserInfo();

      String serverName = this.getUserInfoBean().getServer();
      String serverPort = this.getUserInfoBean().getPort();
      String serverUser = this.getUserInfoBean().getUsername();
      String serverPass = this.getUserInfoBean().getPassword();

      MetadataWorkspace workspace = com.sas.metadata.MetadataWorkspace.getWorkspace();
      try
      {
        sas9explorer.UserInfoBean uib = this.getUserInfoBean();
        uib.setWorkspace(workspace);
        uib.setIOMI( workspace.makeOMRConnection(serverName,serverPort,serverUser,serverPass));
        this.setBean("UserInfoBean", uib);
      }
      catch (MdException e)
      {
         String msg = e.getSASMessageSeverity() + " / " + e.getSASMessage()
                            + " / " + e.getLocalizedMessage();
         this.getErrorManagementBean().setMessage(msg);
         return "NotLogged";
      } catch (Exception e)
      {
           String msg = e.getLocalizedMessage();
         this.getErrorManagementBean().setMessage(msg);
         return "NotLogged";
      }
        return "Logged";
    }

_______________________________________

Moreever you can design explitcitly your navigation flow :

Great !

About JSF and ASP 2.0 comparison ....

par Harduin publié dans : SAS9
ajouter un commentaire commentaires (0) créer un trackback recommander

The Business Intelligence Blog

Texte libre

Calendrier

Liens

Recommander

W3C

SAS9 Performance Test / SPDE vs BASE SAS!

Fews Word on JSF & my code

JSF with SAS9 ;) ... and SAS9 Code Snip

Recherche

Articles récents

Catégories