UsingMicrosoftSQLServerEfficientlyonNetXiaYang(夏陽)(DepartmentofComputerScienceandTechnology,CUMT,Xuzhou221008)AbstractHowtouseMicrosoftSQL(structuredquerylanguage)Serverefficientlyonnetisanalyzed.AndthekeytechnologyabouthowtouseADOtomanageSQLserverdatabasesandtheirdevicesandhowtoremotelytriggerofftheSQLserverdatabaseonnetforimprovingqueryspeedaremainlydiscussedinthispaper.Keywords:SQLServer；ADO；ASP；database1IntroduceMicroSQLServerisincommonuseasdatabasemanagementsystemoncurrentsmallnet2work.Applicationsusedtothiskindofnetworkdatabaseareprogressive.Keyaspectofdatabaseap2plicationdesignishowtheapplicationcodeinteractswiththedatabase.Someapplicationstreatthedatabasesimplyasastorageplaceforrecords.Theapplicationitselfperformsmostoftheoperationsonthedata,suchasfiltering,counting,ormatchingrecords.Otherapplicationstreatthedatabaseasadatamanagementengine,performingallofthesedataoperationsattheserver.Thefirststyleofdatabaseaccessiscommoninprogramswrittentouseanindexedsequentialaccessmethod(ISAM)database.Thesecondstyleofdatabaseaccessismoreappropriateforaprograminteractingwitharelationaldatabase.MicroSQLServerisjustthiskindofdatabase.2AboutSQLStructuredquerylanguage(SQL)isthelan2guageofMicrosoftSQLServer.Itmakessensethatapplicationdeveloperslearnto“speak”thislanguagefluentlyiftheywanttheirapplicationstocommuni2cateeffectivelywiththeserver.EffectiveuseofSQLcanminimizetheamountofdatathatmustbereadfromandwrittentodiskdevicesattheserver.Simultaneously,effectiveuseofSQLcanminimizetheamountofdatashippedtoandfromSQLServeracrossthenetwork.SavingdiskIOandnetworkIOarethemostimportantfactorsforimprovingapplicationperformance.OneofthecapabilitiesofSQLisitsabilitytofilterdataattheserversothatonlytheminimumdatarequiredisreturnedtotheclient.Usingthesefacilitiesminimizesexpensivenetworktrafficbe2tweentheserverandclient.ThismeansthatWHEREclausesmustberestrictiveenoughtogetonlythedatathatisrequiredbytheapplication.Itisalwaysmoreefficienttofilterdataattheserverthantosendittotheclientandfilteritintheapplication.Thisalsoappliestocolumnsrequestedfromtheserver.AnapplicationthatissuesaSE2LECT3FROM.statementrequirestheservertoreturnallcolumndatatotheclient,whetherornottheclientapplicationhasboundthesecolumnsforuseinprogramvariables.Selectingonlythenec2essarycolumnsbynamewillavoidunnecessarynet2worktraffic.ItwillalsomakeyourapplicationmoreReceived6September2000Dec.2000JournalofChinaUniversityofMining&TechnologyVol.10No.21994-2006ChinaAcademicJournalElectronicPublishingHouse.Allrightsreserved.robustintheeventoftabledefinitionchanges,be2causenewlyaddedcolumnswontbereturnedtotheclientapplication.BeyondtheSQLsyntaxitself,performanceal2sodependsonhowyourapplicationrequestsaresultsetfromtheserver.InanapplicationusingODBC,the“howisdeterminedbythestatementoptionssetpriortoexecutingaSELECT.Whenyouleavethestatementoptionsatdefaultvalues,SQLServersendstheresultsetwiththemostefficientway.SQLServerassumesthatyourapplicationwillfetchalltherowsfromadefaultresultsetimmediately.Therefore,yourapplicationmustbufferanyrowsthatarenotusedimmediatelybutmaybeneededlater.Thisbufferingrequirementmakesitespeciallyimportantforyoutospecify(byusingSQL)onlythedatayouneed.Itmayseemeconomicaltorequestadefaultre2sultsetandfetchrowsonlyasyourapplicationuserneedsthem,butthisisfalseeconomy.Unfetchedrowsfromadefaultresultsetcantieupyourcon2nectionwiththeserver,blockingotherworkinthesametransaction.Stillworse,unfetchedrowsfromadefaultresultsetcancauseSQLServertoholdlocksattheserver,possiblypreventingotherusersfromupdating.Thishiddenproblemmaynotshowupinsmall2scaletesting,butitcanappearlaterwhentheapplicationisrunning.Thelessonhereissimple2immediatelyfetchallrowsfromadefaultre2sultset.Someapplicationscannotbufferallthedatatheyrequestfromtheserver.Forexample,anap2plicationthatqueriesalargetableandallowstheus2ertospecifytheselectioncriteriamayreturnnorowsormillionsofrows.Theuserisunlikelytowanttoseemillionsofrows.Instead,theuserismorelikelytore2executethequerywithnarrowerselectioncriteria.Inthiscase,fetchingandbuffer2ingmillionsofrowsonlytohavethemthrownawaybytheuserwouldbeawasteoftimeandresources.Forapplicationslikethese,SQLServeroffersservercursorsthatallowanapplicationtofetchasmallsubsetorblockofrowsfromanarbitrarilylargeresultset.Iftheuserwantstoseeotherrecordsfromthesameresultset,aservercursoral2lowstheapplicationtofetchanyotherblockofrowsfromtheresultset,includingthenextnrows,thepreviousnrows,ornrowsstartingatacertainrownumberintheresultset.SQLServerdoestheworktofulfilleachblockfetchrequestonlyasneeded,andSQLServerdoesnotnormallyholdlocksbe2tweenblockfetchesonservercursors.Servercur2sorsalsoallowanapplicationtodoapositionedup2dateordeleteofafetchedrowwithouthavingtofigureoutthesourcetableandprimarykeyoftherow.Iftherowdatachangesbetweenthetimeitisfetchedandthetimetheupdateisrequested,SQLServerdetectstheproblemandpreventsalostup2date.Allofthesefeaturesofservercursorscomeatacost.IfalltheresultsfromagivenSELECTstate2mentaregoingtobeusedinyourapplication,aservercursorisalwaysgoingtobemoreexpensivethanadefaultresultset.Adefaultresultsetalwaysrequirescommunicationbetweenclientandserver.Moreover,sometypesofservercursors(thosede2claredasdynamic)arerestrictedtousinguniquein2dexesonly,whileothertypes(keysetandstaticcursors)makeheavyuseoftemporarystorageattheserver.Forthesereasons,onlyuseservercursorswhereyourapplicationneedstheirfeatures.3OneMethodofIncreasingQuerySpeed3.1DesignideaSQLEnterpriseManagertoolprovidedbySQLServercanbeusedforcreatingandoperatingdatabase.ButitisbestthingforuserthattheyareabletoremotelytriggeroffthemanagementdatabasedesignedbySQLServer,whichspeciallymanagedatabasesandtheirdevicesneededbyappli2cations.Inordertoconnectdatabase,youshouldchoicetheoneofthedatabaseaccessinterfaces.Al2thoughtherearemanyinterfacesthatcanbeselect2edonnet,asthesuccessorofbothRDOandDAO,thenewestdatabaseaccessinterfaceADOmaybethebestchoice,becauseADOmakesitpossibletorealizedatabaseapplicationbasedonbrowser.ToremotelytriggeroffSQLServerforcreat2ingtemporaryviewandtableontheexisteddatabaseanditsdevicecanstorethepreviousresults181XiaYangUsingMicrosoftSQLServerEfficientlyonNet1994-2006ChinaAcademicJournalElectronicPublishingHouse.Allrightsreserved.fornextquerying.Itshrinkstherangeofqueryaswellasenhancesqueryspeed.Themethodisnotcomplicated.AfterconfiguredtheODBC,youcanusetheserverobjectofASPtocreateconnection,then,operatethedatabasefromADO.Thedetailedmethodisasfollows:1)togenerateexecutablesentencestrSQLcreate=“CREATEVIEWdbo.“&session(“viewname”)&“1ASSELECTTqueryView3.FROMTqueryViewwhere”&textfield；2)toconnectexisteddatabaseSetobjPagingConn=Server.CreateObject(“ADODB.Connection”)objPagingConn.Open“Tlogin”,“sa”；3)toexecuteapplicationandcreatetemporaryviewobjPagingRS.Opensession(“querystring”),objPagingConn,3,1.3.2CurrentproblemandthemethodtosolvetheproblemItiswellknownthattheremustexistadatabasedevicewithresidualplacebeforecreatingadatabasebyusingCREATEDATABASEsen2tence.OtherwisewehavetouseDISKINITsen2tencetocreateanewdevice.Howeverthesentenceincludesmanyessentialparameters.Wecouldhard2lybesureoftheparametersvaluewithoutusingthemanagementtoolsofSQL.TaketheDISKINKsentenceasanexample,itscompletesyntaxisasfollows:DISKINITNAME=logicalname,PHYSNAME=physicalname,VDEVNO=virtualdevicenumber,SIZE=numberof2Kblocks,VSTART=virtualaddressTheNAMEandSIZEareeasilyfetched.However,thephysicalnamePHYSNAMEandthevirtualdevicenumberVDEVNOaredifficulttodealwith.Theformerrequiresthewholepathnameofphysicalfilewithinaserver；thelatterneedstolo2cateanumberbetween1and255whichisnotoccu2piedbyotherdevices.Whileinwritingthedatabasemanagementprogram,itisunexpectedwhereSQLServerhasbeeninstalledandwhichdevicenumbershavebeenoccupied.EventhoughusingtheSQLEnterpriseManagerofSQLSever,however,wemustalsoinputmanyparametersthatarenotoftenused.ToremotelytriggerofftheSQLServeronnetwillbemorecomplex.Inordertosolvetheproblem,weputforwardthefollowingscheme.1)CreatingsentenceparametersfordeviceTheDISKINITsentenceisthesentenceforcreatingdevice.Inordertosimplifytheproblemmentionedabove,wemaysetadevicefilethathasthesamenamewiththedatabaseandsaveitinthesubdirectorywhichhassavedmasterdevice.Thenameofthedatabasehasalreadyfixedwhentheap2plicationwasdesigned.ThesubdirectoryofmasterdevicecouldbeinquiredfromthesystemtableSYS2DEVICES.Thusthephysicalnameparameterofthedevicefileisfixed.Theproblemofvirtualdevicenumberisprettycomplex,becausethereisnosuchafieldcalled“vir2tualdevicenumber”inthesystemtableSYSDE2VICES.Thereforewehavetotryanotherway.Afteranalyzingthesystem-storedprocedureSP2HELPDEVICEoftheSQLSever,wefoundthatvirtualdevicenumberwashiddenintheLOWfieldofthesystemtableSYSDEVICES.ByusinganothersystemtableSPT2VALUES,wecanfindthevirtualdevicenumberofeachdevice.ThereforeifwecanlocateinacirclewhetheracertaindevicenumberisintheSYSDEVICESornot,wemayfindthevirtualdevicenumberthatcouldbeused.Asforthesizeofthedatabasedevice,wehadbettermakeitalittlebigger,orletuserssetit.2)CreatingsentenceparametersfordatabaseThesentenceforcreatingdatabaseisasfol2lows:CREATEDATABASEdatabasenameONDEFAULTdatabasedevice=size,databasedevice=size.LOGONdatabasedevice=size.,databasedevice=size.FORLOADMostoftheaboveparametersareoptional.Weonlyneedtodecidethedevicenameanddatabasesize.Howeverthedatabasename,thedevicename281JournalofChinaUniversityofMining&TechnologyVol.10No.21994-2006ChinaAcademicJournalElectronicPublishingHouse.Allrightsreserved.andthesizehavealreadyfixedwhencreatingthedevice.Hencethereisnoproblemabouttheparam2etersinthissentence.3.3Frequentlyusedfunctionsinmanagingdatabaseanditsdevice1)TopickupthecurrentworkingdatabaseThemanagementtaskisusuallycompletedinthemasterdatabase.Thereforewehadbettersavethecurrentworkingdatabasebeforethetaskisexe2cuted.Inthiswaywemayswitchbackconvenientlyafterfinishingthetask.PublicFunctionSQL2GetCurrentDatabaseName(CnAsADODB.Connec2tion)AsStringDimsSQLAsStringDimRSAsNewADODB.RecordsetOnErrorGoToerrSQLGetCurrent2DatabaseNamesSQL=“selectCurrentDB=DBNAME()”RS.OpensSQL,CnSQLGetCurrentDatabaseName=Trim(RS!CurrentDB)RS.CloseExitFunctionerrSQLGetCurrentDatabaseName:SQLGetCurrentDatabaseName=“”EndFunction2)TojudgewhetheradatabasedeviceexistsornotPublicFunctionSQLExistDeviceName(CnAsADODB.Connection,sDevNameAsString)AsBoolean(Tojudgetheexistenceofadevicebyitsname.Ifexisted,return“1”；else,return“0”.)DimsSQLAsStringDimRSAsNewADODB.RecordsetDimbTmpAsBooleanOnErrorGoToerrSQLExistDeviceNamesSQL=“selectCntDev=count(*)frommaster.dbo.sysdeviceswherename=“&sDev2Name&”RS.OpensSQL,CnIfRS!CntDev=0ThenbTmp=FalseElsebTmp=TrueRS.CloseSQLExistDeviceName=bTmpExitFunctionerrSQLExistDeviceName:SQLExistDeviceName=FalseEndFunction3)Tojudgewhetheravirtualdevicenumberisoccupiedornot:SQLExistDeviceNumber.4)Togetthesmallestunoccupiedvirtualde2vicenumber:SQLGetUnusedDeviceNumber.5)Toge