Frequently Asked Questions about SAS® Indexes

《Frequently Asked Questions about SAS® Indexes》由会员上传分享，免费在线阅读，更多相关内容在学术论文-天天文库。

SUGI30ApplicationsDevelopmentPaper008-30®FrequentlyAskedQuestionsaboutSASIndexesBillyClifford,SASInstitute,Inc.,Cary,NCABSTRACTIndexesintheBASEenginehavebeenpartofBaseSASsinceSAS6.Overtheyearstheuseandmis-useofindexeshasgeneratedanumberofquestionsfromusers.TheanswerstothesequestionsmighthelpyouunderstandmoreaboutindexesandhowtheyinteractwithotherSAScomponents.ThispaperbeginswithanoverviewofindextechnologyusedbytheBASEengineandhowSASusestheseindexes.Then,asetofFrequentlyAskedQuestions(FAQs)collectedfromcustomers,in-houseusers,andTechSupportisanswered,alongwiththerationalefortheanswer.WHATISANINDEX?Anindexisanauxiliarydatastructurethatprovidesfastaccesstoobjectsbyvalue.Allthedataintheindexisredundantandisderivedfromthedataset.It’stheorganizationofthisredundantdatathatprovidestheperformancebenefit.Withoutanindex,anyoperationthatsubsetsthedata,suchasaWHEREexpression,mustsearcheveryobservationinthedatasetsequentially,lookingformatchingcriteria.Theuseofanindexcansignificantlyreducethetimetolocatethedesiredsubsetofobservations,especiallyifthesubsetissmallcomparedtotheentiredataset.Youcancreateanindexforoneormorevariablesofthedataset.Ifmultiplevariablesarespecified,thevaluesofeachvariableareconcatenatedintheorderspecified,toformtheindexedvalue.Anindexthathasonevariableiscalledasimpleindex.Anindexthathasmultiplevariablesiscalledacompositeindex.Unlessspecificallymentioned,thispaperdoesnotdistinguishbetweenasimpleindexandacompositeindex.Youcancreatemultipleindexesforthesamedataset.Allindexesforagivendatasetarestoredinasinglefilethatisassociatedwiththedataset.Whenthedatasetisupdatedbyaddingobservations,deletingobservations,orchangingvariablevalues,theindexesareautomaticallyupdated.HOWISANINDEXSTRUCTURED?Thevaluesforanindexedvariablearestoredinaninvertedtreestructure,whichissimilartothediagramshowninFigure1.Eachleafnodecontainsasetofvalue/RIDpairsthatareorderedbyvalue.TheRID,orRecordIdentifier,isusedtolocatetheobservationondiskthatcontainsthevalue.Allleafnodes,asaunit,formanorderedsetofvalue/RIDpairsforallvaluesoftheindexedvariable.Thefirstvalueintheleft-mostleafnodeisthelowestvalue.Thelastvalueintheright-mostleafnodeisthehighestvalue.Eachnon-leafnodecontainsasetofvalue/NIDpairsthatareorderedbyvalue.TheNID,orNodeIdentifier,isusedtolocatethechildnodeatthenextlowerlevel.Thevalueisthehighestvaluethat’scontainedinthechildnodethatispointedtobyNID.Allnon-leafnodes,asaunit,formamultileveldirectorythatisusedtolocatethevalue/RIDpairequaltoorgreaterthantherequestedvalue.Thesearchforarequestedvaluebeginsattherootnodeandmovesdownthroughonenodeateachleveluntiltheleafnodethatshouldcontainthevalueisfound.Iftherequestedvalueisnotfound,thenthevalueisnotintheindex.Iftherequestedvalueisfound,itsRIDcanbeusedtoreadtheobservationthatcontainstherequestedvalue.Subsequentvalue/RIDpairsthataregreaterthantherequestedvaluearefoundbyreadingtheremainingvalue/RIDpairsinthenode,andfollowingthematchingNIDtotheleafnodesthatcontainthehighervalues.Anindextreeisbalancedwhenallleafnodesarethesamedistancefromtheroot.Thisattributeofanindexisimportantbecauseitprovidesauniformcostforaccessinganynode.Updatingthedatasetcancausethetreetobecomeunbalanced.Addingvaluescancausepartsofthetreetoexpand.Deletingvaluescancausepartsofthetreetoshrink.Theindexcodedynamicallyprunesundesirablegrowthasupdatesaremade,sotheindextreeisalwaysmaintainedinabalancedstate.1 SUGI30ApplicationsDevelopmentLevel2Level1Level1Level1Level0Level0Level0Level0Level0Level0Level0Figure1.ExampleofanInvertedTreeStructureforIndexValuesHOWDOESTHEBASEENGINEUSEANINDEX?TheprimaryuseofanindexistooptimizeaWHEREexpression.Asequentialscanofthedatasetcanbeveryexpensive.Usinganindextolocateasmallsubsetoftheobservationscanprovidesubstantialperformancebenefits.WhenitreceivesaWHEREexpression,theBASEengineautomaticallylooksforthebestindextouse.Therearesomedatasetoptionsthatyoucanusetooverridetheselectionthat’smadebytheBASEengine.DetailsarediscussedintheFAQsectionofthispaper.Becausetheobservationsthatareretrievedbyanindexarereturnedinsortedorder,anystatementthatusesBYprocessingcanuseanindextoobtainthenecessaryorderwhenthedatasetisnotsortedbytheBYvariable.Referentialintegrityconstraintsuseindexes.WhenaPrimaryKeyisdefined,theBASEenginecreatesauniqueindextoensurethattherearenoduplicates.WhenaForeignKeyisdefined,anon-uniqueindexiscreatedtoenablefastlookupoftheForeignKeyvalues.WhenyoudefineaUNIQUEintegrityconstraint,auniqueindexisdefinedforthatvariable.UNDERSTANDINGINDEXINGANDSORTINGQ1:Whyisthedatasortedbeforetheindexisbuilt?A1:Theindexisstoredondiskinfixed-sizepages.EachnodeinFigure1isapage.Ifthedataissorted,thecodethatcreatestheindexdoesnothavetosearchforthecorrectplacetostorethenewvalue,whichconsumesextraI/OandCPUresources.Thenewvaluewillalwaysbestoredphysicallyadjacenttothelastvaluethatwasstored.Whenanindexisbuiltwithsorteddata,eachpagecanbecompletelyfilledbeforegoingtothenextpage.Thinkofthisasbuildingthetreesequentially,fromlowestvaluetohighestvalue.Ifanindexisbuiltwithunsorteddata,itmightbelargerthananindexthatisbuiltwithsorteddatabecauseallthepagesmightnotbefull.Sortingthedatatakesextratime,butgenerallyresultsinamorecompactindexthatbuildsfaster.Q2:Howaresortassertionandindexesrelated?A2:Therearetwokindsofsortassertions:weakandstrong.Astrongassertionisset(thatis,storedinthedataset)bytheSORTprocedure.Aweakassertioncanbesetviaadatasetoptioniftheuserknowsthatthedataissorted.SASsoftwarecanquerythesortassertionandavoidsortingifthedataisalreadysortedasdesired.2 SUGI30ApplicationsDevelopmentPROCSORTwillskipthesortifthedatasethasastrongassertionthatthedataiscorrectlysorted.IndexcreationandindexupdatingforPROCAPPENDwillskipthesortifthereisaweakorstrongassertion.Iftheassertionisweak,theindexcodechecksthedatavaluestomakesurethattheyareinthecorrectorder.Updatingadatasetthathasasortassertionwillcanceltheassertion.However,whenusingPROCAPPEND,ifthebasedatasetandtheinputdatasethavethesamesortassertionandthenewvaluesarelargerthantheoldvalues,thesortassertiononthebasedatasetispreserved.Q3:Isthereanybenefittoindexingadatasetthatisalreadysorted?A3:Yesandno.Thebenefitdependsonyourqueries.WhenyouuseaWHEREexpression,theanswerisprobably.Withoutanindex,asequentialscanoftheentiredatasetmustbeperformed.BeginningwithSAS8,astrongsortassertionmightterminatethefullsequentialscanearlyifthereisnosuitableindex.Forexample,iftheWHEREexpressioniswherex=1;andthedatasetissortedbyx,whentheengineencountersthefirstobservationthatcontainsx>1,itterminatesthescan.IfyouareonlyperformingBYprocessing,thensortingthedatasetontheBYvariablewillperformbetterthancreatinganindexontheBYvariable.Notethatindexcreationisfasterifthedatasetissortedontheto-be-indexedvariable.Q4:Whydoessortinginplacedestroymyindex?A4:UsingPROCSORTtosortin-placewillphysicallyre-arrangetheobservationsinthedataset.RememberthattheindexcontainsRIDsthatpointtothelocationofobservationsinthedataset.Whentheobservationsarephysicallymoved,theRIDsintheindexbecomeinvalid,andalldeletedobservationsareeliminatedfromthenewlysorteddataset.Anyindexesthatremainaftersortingin-placewouldbeunusablebecausethedatasetwouldnolongermatchtheindex.IndexesarenotpreservedbyPROCSORT.Additionally,thestructureofasorteddatasetcanbechangedbyusingaWHEREexpression,theOBS=option,theDROP=option,ortheKEEP=option.Anyoftheseelementscanmodifythecontentsofthesorteddatasetand,therefore,invalidateanyindexes.SimilarreasonsrequirethatindexesberebuiltafterusingPROCCOPY.CREATINGANDMAINTAININGINDEXESANDINDEXEDDATASETSQ5:Howmuchextradiskspacewillanindextakeup?A5:SASTechnicalSupporthasaDATAstepprogramthatwillprovideanestimateoftheamountofdiskspacethatisusedbyanindex.AcopyoftheprogramisincludedintheAppendix.Thisprogramwaswrittenwhentheonly64-bitplatformwasTru64underUNIX.InSASreleasesafterVersion6,ifyouareusinga64-bitplatform,specifyALPHAasthehost.YoucanalsogetacopyofthisprogramfromtheTechnicalSupportWebsite:http://support.sas.com/techsup/sample/utilities.htmlQ6:Whenisitnecessarytore-createanindexforadatasetthathasobservationsadded,deleted,andmodified?A6:Thereisnoneedtore-createtheindextorestorethetreetoanoptimumconfigurationafterheavyupdating.Theindexcodeautomaticallybalancesthetreeasupdatesaremade.Q7:CanIcreateanindexindescendingorder?A7:No.Rememberthattheprimarypurposeofanindexisfastlookupofagivenvalueorrangeofvalues.SASindexesarestoredinascendingorderbydefault.Returningtheindexedvaluesinascendingsortorderisasideeffectofusinganindex.Therehasnotbeensufficientuserfeedbacktoindicatethatanoptionisneededtoenablestoringthevaluesineitherascendingordescendingorder.3 SUGI30ApplicationsDevelopmentQ8:IstheindexcompressedwhenIspecifyCOMPRESS=YES?A8:No.Thereissubstantialoverheadassociatedwithcompressingafile.Giventhatthedatasetisgenerallymuchlargerthantheindex,compressingtheindexwouldnotprovideenoughdisksavingstojustifythecost.Q9:IstheindexencryptedwhenIspecifyENCRYPT=YES?A9:Yes.Encryptingadatafileisasecuritymeasure.Ifthedatainthedatasetneedssecurity,thenthedataintheindexdoestoo.Forexample,avariablethatcontainssalaryinformationmightbeindexed.Ifanindexisencrypted,youcannotdeterminethenameofthepersonwhohasaspecificsalarybecausetheobservationsthatarereferencedbytheindexareencrypted.Iftheindexisnotencrypted,youcanseeallthevaluesforSALARY,whichmightenableyoutoinferconnectionsbetweenspecificsalaryvaluesandspecificemployees.Q10:HowdoIindexaview?A10:Youcannotcreateanindexforaview.Anindexcanonlybecreatedforaphysicaldatafile.Aviewrepresentsavirtualdatafile.Therearenoobservationsinaview.Theviewcontainsinstructionsformaterializingtheobservationsfromphysicalfilesorprograms.Indexescanbecreatedforthephysicalfilesthatarereferencedbytheview,butyoucannotcreateanindexfortheviewitself.Q11:HowcanIstoretheindexinaseparatefolderfromthedata?A11:SASprovidesnostatementsthatenableyoutoseparatethedatasetfromtheindexfile.OnsomehostssuchasUNIX,youcancreatealinkinthesamedirectoryasthedatasettotheindexfilethatisinanotherdirectory.Youwouldhavetomovetheindexfileandmakethelinkmanuallyaftercreatingthedatasetandtheindex.Q12:WhenItrytoopenadataset,Igetamessagethatthedatasetiscorrupted.IcanseethatSASistryingtorebuildmyindex.Unfortunately,Idonothaveenoughdiskspacetorebuildtheindex.HowcanIdeletetheindexthatisassociatedwiththisdatasetwithoutfirstrebuildingtheindex?A12:Unfortunately,atthistime,youcannotdeletetheindexwithoutrebuildingit.Therepaircodefirstdeletestheindexfile,thenrepairsthedataset.Iftherewereindexes,theindexesarethenrebuiltasthelaststep.Actually,thereisanundocumenteddebugoptionthatwillallowadamageddatasettobeopenedfornormalread-onlyprocessing.Withthisoption,youcanusePROCCOPYortheDATAsteptomakeacopyofthedatasetwithouttheindexfile.SASTechnicalSupportcanprovidethisoptionifyoureallyneedit.Abettersolutiontothissituationisbeingdeveloped.AnewvaluefortheDLDMGACTIONoptionwillallowthedatasettoberepairedwithouttheindexes.Themetadatathatdescribestheindexeswillremaininthedataset,andyouwillbeabletoreconstructtheindexesatamoreappropriatetime.Indexmetadatacanalsobedeletedifyoudonotwanttoreconstructtheindexes.CONSIDERATIONSWHENUSINGPROCAPPENDWITHINDEXEDDATASETSQ13:WhydoIhavetodropandre-createindexeswhenIusePROCAPPEND?A13:Weoftenrecommendeddroppingandre-creatingtheindexesinSAS6,however,manyimprovementstoindexcreation,especiallywhenusingPROCAPPEND,wereimplementedinlaterreleasesofSAS.Ifdroppingandre-creatingtheindexeswasinheritedfromaversion6SASapplication,youcanprobablyremovethatpartofthecodeinSASprogramsthatrunlaterreleasesofSAS.InSAS6,aseachobservationisaddedtothedataset,allrelevantindexesareupdated.Ifthevaluesthatarebeinginsertedintotheindexarenotsorted,theindexupdatestakelonger.InSASreleasesafterSAS6,bydefault,observationsareappendedtothedatasetastheyaresenttotheenginefromPROCAPPEND(astheyareinSAS6),buttheindexupdatesaredelayed.Theindexesarenotupdateduntilalltheobservationsareappended.Duringindexcreation,thenewdatatobeaddedtotheindexesissortedbeforebeingaddedtotheindexes.Ifallthenewindexvaluesarehigherthanthevaluesthatarecurrentlyintheindex,thentheindexcreationcodeisinvokedtoappendthenewvaluestotheindex.4 SUGI30ApplicationsDevelopmentEvenifthenewvaluesarenothigherthantheoldvalues,itismoreefficienttosortthenewvaluesbecauseallthenewvaluesareappliedsequentially,whichpreventstheneedtore-readthesameindexpage.InnewerversionsofSAS,youcanusethedatasetoptionAPPENDVER=V6toreverttotheearlierbehavior.Q14:SometimeswhenIrunPROCAPPEND,addingoneobservationtakes1second.Idothesamethingagain,addjustoneobservation,andittakes100seconds.Why?A14:Ifyourdatasethasindexes,themostlikelyanswerisacentilesrefresh.Centilesarerefreshedatgivenintervalswhenthedatasetisclosed.Whencentilesarerefreshed,theentireindexisreadtoupdatethecentilevalues.Inthisexample,thesecondPROCAPPENDmighthavecrossedthethresholdforrefreshingcentiles.TheUPDATECENTILES=statementinPROCDATASETScanbeusedtochangethedefaultthresholdforrefreshingtheindexcentiles.Centiles(alsocalledcumulativepercentiles)are21valuesthatarestoredintheindexmetadatathatcontainthedistributioninformationfortheindexedvalues.Centilesaremarkersforthe5percentpoints.Forexample,centile0istheminimumvalue,centile5indicatesthat5%ofthevaluesarebelowthisvalue,centile10indicatesthat10%ofthevaluesarebelowthisvalue,andsoforthuptocentile100,whichisthemaximumvalue.CentilesareusedtoestimatethenumberofobservationsthatwillbeselectedbytheWHEREexpression.Q15:IhaveanapplicationthatusesPROCAPPENDtoadddatatoanindexedbasedatasetthathastheUNIQUEattributeonsomeindexes,anditroutinelyrejects40%ormoreoftheincomingobservationsasduplicates.That'sOKbecauseit'spartofthedesign.Inoticedthat,inSASreleasesafterSAS6,mybasedatasetisgrowingalotfasterthanitusedto.PROCCONTENTSshowsthatthedatasethasalotofdeletedrecords.CanIpreventthis?A15:AspartofthePROCAPPENDperformanceenhancementinSASreleasesafterSAS6,allobservationsareunconditionallyaddedtothedataset.Thentheindexesareupdated.Anewlyaddedobservationthathasaduplicatevalueforauniqueindexisrejectedbytheindexupdateprocess,andtheobservationisdeleted.Buttheobservation(likealldeletedobservations)remainsinthedatasetandcontinuestotakeupdiskspace.TheeasiestwaytopreventthisduplicationistousetheAPPENDVER=V6optioninPROCAPPEND.ThisoptioncausesSAStousetheSAS6algorithmtoupdatetheindexatthesametimethattheobservationisaddedtothedataset,andpreventsduplicateobservationsfrombeingaddedtothedataset.However,theremightbeaperformancepenaltyforusingtheSAS6methodofappending.MOREABOUTHOWSASUSESINDEXESQ16:WhenIaccessadatasetthat’sstoredonaUNIXdiskfrommyPC,WHEREexpressionsdonotseemtotakeadvantageoftheindexes.A16:TheCrossEnvironmentDataAccess(CEDA)featureenablesyoutotransparentlyaccessdataacrossheterogeneousplatformsforreading.ThecodeforCEDAdoesnotsupportupdatesanddoesnotsupportindexes.AddingCEDAsupportforread-onlysolvedtheheterogeneousdataaccessproblemformostusers.Userfeedbackwilldetermineifsupportforupdateandindexeswillbeaddedinthefuture.Q17:Dointegrityconstraintsuseindexes?A17:Yes,someconstraintsuseindexes.AuniqueindexisneededforaUNIQUEconstraintorforaPRIMARYKEYconstraint.Anon-uniqueindexisneededforaFOREIGNKEYconstraint.Ifnosuitableindexexistswhentheintegrityconstraintisdefined,theBASEenginewillcreateanindexforyou.Ifyouhavealreadycreatedasuitableindex,thentheintegrityconstraintcreationcodewillnotcreateanotherone.CHECKconstraintssuchasx<1000donotuseorrequireanindex.Q18:Isitpossibletotelltheenginewhichindexshouldbeused?A18:Yes.TheengineattemptstomaketherightdecisionaboutwhetheritismoreefficienttoperformasequentialscanofthedatasetortouseanindexforprocessingaWHEREexpression.Theenginemakestherightdecisionmostofthetime.Whenitdoesnot,thedatasetoptionsIDXNAME=andIDXWHERE=,whichareprovidedinSASreleasesafterSAS6,giveyoucontroloverwhichindexwillbeused.5 SUGI30ApplicationsDevelopmentTheoptionIDXWHERE=tellsSAStousethebestindex(IDXWHERE=YES)ornottouseanindex(IDXWHERE=NO)whenprocessingaWHEREexpression.NotethatthisoptioncannotbeusedtocontroltheuseofanindexforBYprocessing.IDXWHERE=YESisthedefault.UsetheoptionIDXNAME=index-nametotellSAStouseaparticularindex.NotethatSASdisregardsyourrequestifuseoftheindexwillproduceincorrectresults.AnexamplewouldbetryingtouseaNOMISSindexforaWHEREexpressionthatcanselectmissingvalues.Q19:TheWHEREexpressionselectsalltheobservationsbuttheindexisstillused,eventhoughyou'dexpectsequentialprocessingtobeused.Noticethatthedatasetisjustonepage.A19:Forverysmalldatasetssuchasthis,theWHEREexpressioncostingalgorithmisnotespeciallyaccurate.Usingtheindexresultsinalmostthesamespeedasasequentialscan.Forsuchasmalldataset,creatinganindexisnotlikelytoprovideaperformancebenefit.Q20:Whydidn'tSASusemyindex?A20:Thisisoneofthemostdifficultquestionstoansweraboutindexesbecausetheanswerdependssoheavilyonthedata.Theengineknowshowmanypagesofdataareinthedataset;thisistheamountofdatatobereadduringasequentialpass.That’stheeasypart.Thentheenginemustestimatehowmanypagesofdata(really,howmanyI/Os)willbeneededtousetheindex.Theindexcostiscomparedtothesequentialcost,andtheenginechoosesthecheapestmethod.Theengineusesseveralcalculationstoestimatetheindexcost.First,itestimatesthenumberofobservationsqualifiedbytheindexbyusingthe21numbersintheCentilesarray.Theresultingestimateisaccuratetowithin5%.Next,theindexisusedtoestimatethe“sortedness”oftheobservations.TheengineexaminestheRIDsthatwouldbeencounteredonthefirstindexpageduringtheindexscanandcalculatesthenumberofdifferentdatapagesthatarerepresented.Thisnumbercanrangefrom1(ifthedatasetissortedontheindexedvalue)tothenumberofRIDs(iftheindexedvaluesarewidelyscatteredthroughoutthedataset).AnI/OcostperRIDiscomputedfromthisnumberandthenumberofRIDsonanindexpage.Theresultisadecimalnumberthatislessthanorequalto1.Smallerisbetter(lesscostly).Finally,theI/OcostperRIDismultipliedbytheestimatednumberofqualifiedobservationstogetthenumberofdatapagesthatwillbereadiftheindexisused.Comparingthisnumberwiththesequentialcostdeterminestheaccessmethod.Becausethesecalculationsrelyonestimatesratherthanexactnumbers,andbecausethecostofsequentialversusrandomI/Ocanvaryfromhost-to-host,thisalgorithmmightnotalwayspickthefastestaccessmethod.UsingtheoptionMSGLEVEL=IcanprovidehelpfulhintsastowhyanindexwasnotusedforoptimizationofyourWHEREexpression.Hereareexamplesofhintsthatyoumightseeinthelog.INFO:IndexInotused.Sortingintoindexordermayhelp.INFO:IndexInotused.Increasingbufnoto3mayhelp.Q21:Whydoobservationsprintinadifferentorderwhenanindexisused?A21:Withoutanindex,theorderofobservationsisthephysicalorderinwhichtheyarestoredonthedisk.However,withanindex,theobservationsarereturnedintheorderthattheobservationswerefoundintheindex,thatis,sortedontheindexedvariable.ThesortedorderofindexedvariablesiswhyyoucanuseanindexinsteadofPROCSORTtoprovidethenecessaryorderforBYprocessing.Ifyourapplicationisexpectingtoreceivetheobservationsinphysicalorder,youcanusetheoptionIDXWHERE=NOtotelltheBASEenginenottouseanindex.Q22:Whycan'tanindexbeusedifthereisanORintheWHEREexpression?A22:TheBASEengineislimitedtousingonlyoneindextooptimizeaWHEREexpression.ThislimitationmeansthattheselectedindexmustsupplyeveryobservationthatsatisfiestheWHEREexpression.IftheindexsuppliesadditionalobservationsthatdonotsatisfytheWHEREexpression,theywillbefilteredoutlaterbycodethatappliesthefullWHEREexpressiontoeachobservation.6 SUGI30ApplicationsDevelopmentAnORoperationconstructstheselectedobservationsbyincludingobservationsfromtwodifferentWHEREconditions.TooptimizeaWHEREexpressionwithanORoperationwouldrequiretheuseoftwoindexes,oneindexforeachoftheconditions.SomeWHEREexpressionsthatcontainanORoperatorcanbetransformedbySASintoanINoperatorbeforesendingittotheengine.Forexample,wherefirstname='John'and(lastname='Smith'orlastname='Jones');istransformedbySASintowherefirstname='John'andlastnamein('Smith','Jones');Inthiscase,theengineneverseestheORandcanuseanindexoneitherFIRSTNAMEorLASTNAMEforoptimization.Q23:MyindexwascreatedwiththeNOMISSattribute.Sometimesitisusedforoptimization,andsometimesitisn’t.A23:ThisbehaviormightberelatedtothespecificsofyourWHEREexpression.ANOMISSindexdoesnotstoreanyinformationaboutobservationsthatcontainamissingvaluefortheindexedvariable.IfyourWHEREexpressioncanselectmissingvalues,thentheindexwillnotbeused.Q24:WhyisusinganindexforBYprocessingslowerthanPROCSORT?A24:UsinganindexforBYprocessingisn’tnecessarilyslowerthanusingPROCSORT.Performancedependsonyourdataandhowyouareusingit.IfyouareapplyingaWHEREexpressionandaBYstatementthatselectasmallsubsetofyourdata,thenusinganindexmightbemuchfaster.However,ifyourapplicationrunsrepeatedly,youwillincurtheoverheadofusingtheindexeachtimetheapplicationruns.Ifyousortthedatafirst,thenyouhavethisoverheadonlyonce.Q25:AmessagewasissuedthatadatasetwasnotsortedproperlyforBYprocessingalthoughacompositeindexshouldhavebeenselected.Howcantheindexnotbeinsortedorder?TheBYclausewas"byC",andtherewasacompositeindexonthevariablesIandC.A25:IfacompositeindexwasavailableinwhichthefirstvariablethatwasdefinedwasthevariablethatwasusedintheBYstatement,thenthatindexisselectedtoassistinprocessing.However,iftheBYvariableisthesecondvariableinacompositeindex,thenthatindexwouldnotbeorderedbytheBYvariableandcannothelp.YoucanusetheoptionMSGLEVEL=I;togiveyouamessageinthelogtofindoutwhichindexSASuses.Themessagewillbesimilartothefollowing:INFO:IndexXselectedforBYclauseprocessing.CONCLUSIONAnindexcanbeapowerfulperformanceenhancementtoyourapplication.However,thereareavarietyofsituationsthatneedtobeconsideredbeforeyoumakeindexesapermanentpartofyourapplication.Aswithmostperformance-tuningchoices,itisbesttodrivethemaroundtheblockafewtimesbeforeyoumakethefinaldecision.7 SUGI30ApplicationsDevelopmentAPPENDIX—SASJOBTOESTIMATETHESIZEOFANINDEX/*-------------------------------------------------------------------*//*Copyright(c)1996bySASInstitute,Inc.Austin,Texas.*//*NAME:index_est*//*AUTHOR:BillyClifford*//*DATE:15Jun99*//*SUPPORT:saswdc-BillyClifford*//*PURPOSE:estimatethenumberofpagesrequiredforanindex*//*UPDATES:*//*27Jul99wdccorrectVAXALPHAsize.Shouldbe44,not40.*//**//*NOTES:*//*1.ThisprogramisforV7/V8SASindexfiles.*//*2.Thealgorithmexpectsallvaluestobeintegers.Thustheneed*//*tousetheroundfunction.*//*3.Thisprogramisbaseduponthealgorithmpublishedinapaper*//*writtenbyClifford,etal,forSUGI14-UsingnewSAS*//*DatabaseFeaturesandOptions.*//*4.Thisprogramestimatesthesizeofoneindex.Ifyour*//*applicationhasmultipleindexes,youmustruntheprogram*//*once(specifyingthecharacteristicsofeachindex)foreach*//*index.*//*5.Toestimatethesizeofyourparticularindex,changethe*//*valuesbelowinthe"User-suppliedvalues"sectionandrun*//*theprogram.*//*-------------------------------------------------------------------*/data_null_;lengthhost$5;/*-------------------------------------------------------------------*//*User-suppliedvalues*//*-------------------------------------------------------------------*/psize=6000;/*pagesizeofindexfile*/vsize=32;/*totalnumberofbytesinthevaluetobeindexed.*//*Foracompositeindex,thisisthesumofthe*//*constituentvariablesizes.*/uval=3000;/*numberofuniquevalues*/nrec=20000;/*totalnumberofrecords(ie,observations)*//*NOTE:ifuvalandnrecareequal,thisisassumed*//*tobeauniqueindex.*/host="HPUX";/*nameofhost:MVS,CMS,HPUX,SUN,PC,AIX,*//*ALPHA,VMS,MIPS,MAC*//*-------------------------------------------------------------------*//*-------------------------------------------------------------------*//*Program-generatedvalues*//*-------------------------------------------------------------------*//*lpages-numberofleaf(bottom)indexpages*//*upages-numberofupperlevel(non-leaf)indexpages*//*maxent-maximumnumberofentriesonanupper-levelpage*//*totpgs-totalnumberofindexpages*//*bytes-totalnumberofpagesconvertedtobytes*//*offset-sizeofoffset*//*levels-numberoflevelsintheindextree*//*entry-thespaceneededtostoreasingleindexedvalueand*//*allofitsRIDs(RecordIDs).Forauniqueindex*//*thereisoneRIDperindexedvalue.Foranon-unique*//*indextherewillbeunval/nrecRIDs.*//*noperpg-numberofentriesperleafpage*//*cont-continuationleafpages(fornon-uniqueindex)*//*-------------------------------------------------------------------*//*-------------------------------------------------------------------*//*Host-specificvalues*//*-------------------------------------------------------------------*/8 SUGI30ApplicationsDevelopment/*shrtsize-sizeof(short)*//*header-sizeof(structIDXPAGES)*//*structIDXPAGES*//*{*//*uint32_tpageid;*//*longspare1;*//*longnextpage;*//*longidxid;*//*shortflags;*//*shortctr;*//*shortnbrentry;*//*shortfree;*//*charlevel;*//*charspare2[3];*//*};*//*longsize-sizeof(long)*//*rsize-sizeof(structBASE_RID).Thisisthesizeofthe*//*pointer(RID)totherecordinthedatasetstoredin*//*theindex.*//*-------------------------------------------------------------------*/if(hosteq"ALPHA")thendo;/*VAXALPHAhost*/shrtsize=2;header=44;longsize=8;rsize=12;end;elsedo;/*allotherhosts*/shrtsize=2;header=28;longsize=4;rsize=8;end;if(uvaleqnrec)thendo;/*Uniqueindex.Nooffsetforauniqueindex.*/offset=0;end;elsedo;/*Non-uniqueindex.Assumeeachvalueoccursnrec/uvaltimes.*//*Sotherewillbenrec/uvalRIDsandonevalue.*/offset=shrtsize;rsize=round(((nrec/uval)*rsize),1);end;entry=vsize+(offset*2)+rsize;noperpg=round(((psize-header)/entry),1);if(noperpglt1)thendo;/*----------------------------------------------------------------*//*Anentireentrywillnotfitonapage.Thisispossibleonly*//*fornon-uniqueindexes.Rememberthat'entry'isthesizeof*//*theindexedvalueplusallofitsRIDs.Herewecalculatecont,*//*thenumberofcontinuationpagesneededfortheextraRIDs.*//*----------------------------------------------------------------*/noperpg=1;cont=round((entry/(psize-header)),1);end;elsecont=0;lpages=round(uval/noperpg,1);/*numberofleafpages*/if((lpages*noperpg)neuval)thenlpages=lpages+1;lolev=lpages;lpages=lpages+cont;9 SUGI30ApplicationsDevelopmentmaxent=round(((psize-header)/(offset+vsize+longsize)),1);/*-----------------------------------------------------------*//*Simulateindexcreationandcountthenumberofleafand*//*non-leafpages.*//*-----------------------------------------------------------*/upages=0;levels=1;dowhile(lolev>1);curlev=round(((lolev+maxent-1)/maxent),1);upages=upages+curlev;lolev=curlev;levels=levels+1;end;totpgs=lpages+upages;bytes=totpgs*psize;/*-----------------------------------------------------------*//*Allvalueshavebeencalculated.Createthereport.*//*-----------------------------------------------------------*/put"================================================================";put"";put"Indexcharacteristics:";put"HostPlatform="host;put"PageSize(bytes)="psize;put"IndexValueSize(bytes)="vsize;put"UniqueValues="uval;put"TotalNumberofValues="nrec;put"NumberofIndexLevels="levels;put"";put"EstimatedstoragerequirementsforaV7orV8index:";put"NumberofUpperLevelPages="upages8.;put"NumberofLeafPages="lpages8.;put"TotalNumberofIndexPages="totpgs8."or"bytescomma14."bytes";put"";put"Note:theaboveestimatedoesnotincludestoragefortheindex";put"directory(usuallyonepage)orthehostheaderpage.";put"";put"Estimationofindexsizecomplete.";put"";put"================================================================";run;10 SUGI30ApplicationsDevelopmentREFERENCESSASInstituteInc.2004."UnderstandingSASIndexes,"SASLanguageReference:Concepts,SASOnlineDoc®9.1.3.CD-ROM.Cary,NC:SASInstitute.Availablehttp://support.sas.com/onlinedoc/913/getDoc/en/lrcon.hlp/a000440261.htm.ACKNOWLEDGMENTSArtJensenAlecFernandezGaryFranklinJimCraigDeannaWarnerJasonEpsteinCharleyMullinCONTACTINFORMATIONYourcommentsandquestionsarevaluedandencouraged.Contacttheauthor:BillyCliffordSASInstituteInc.11920WilsonParkeAvenueAustin,Texas78720512-258-5171x3254Billy.Clifford@sas.comSASandallotherSASInstituteInc.productorservicenamesareregisteredtrademarksortrademarksofSASInstituteInc.intheUSAandothercountries.®indicatesUSAregistration.Otherbrandandproductnamesaretrademarksoftheirrespectivecompanies.11