外文翻译--在.NET上实现大量数据的客户操作 英文版.pdf

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.2©1994-2006ChinaAcademicJournalElectronicPublishingHouse.Allrightsreserved.http:/www.cnki.netrobustintheeventoftabledefinitionchanges,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.ToremotelytriggeroffSQLServerforcreat2ingtemporaryviewandtableontheexisteddatabaseanditsdevicecanstorethepreviousresults181XiaYangUsingMicrosoftSQLServerEfficientlyonNet©1994-2006ChinaAcademicJournalElectronicPublishingHouse.Allrightsreserved.http:/www.cnki.netfornextquerying.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:CREATEDATABASEdatabasenameONDEFAULTßdatabasedevice=size,databasedevice=size.LOGONdatabasedevice=size.,databasedevice=size.FORLOADMostoftheaboveparametersareoptional.Weonlyneedtodecidethedevicenameanddatabasesize.Howeverthedatabasename,thedevicename281JournalofChinaUniversityofMining&TechnologyVol.10No.2©1994-2006ChinaAcademicJournalElectronicPublishingHouse.Allrightsreserved.http:/www.cnki.netandthesizehavealreadyfixedwhencreatingthedevice.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)TogettheDATAsubdirectorypathinSQLServersetupdirectory:SQLGetDataPath.6)Tocreateanewdevice:SQLCreateDevice.7)Tocreateanewdatabase:SQLCreate2Database65.8)Togetthedetailedinformationofdatabasedevice:SQLGetDeviceInfo.9)Toextendthesizeofdatabasedevice:SQL2ExpandDevice.10)Tojudgewhetheradatabaseexistsornot:SQLExistDatabase.11)Todeleteadatabase:SQLDropDatabase.12)Todeleteadatabasedevice:SQLDropDe2vice.13)TogettheversioninformationofSQLSever:SQLGetVersionString.4ConclusionSomedevelopingtechniquesforSQLServerarediscussedinthispaper.Thekindofproblemsshouldbefurtherresearchedinfuture.Inbrief,anapplicationthatiswelldesignedforSQLServer:1)UsesSQLeffectively.2)Minimizesnetworkroundtripstotheserverduringatransaction.3)Usesstoredprocedures.4)Incorporatestheessentialindexingandcon2figurationstepsforSQLServer.Thesecharacteristicsdonthavetobeappliedwithanall2or2nothingapproach.Theycanbeincor2poratedintoanapplicationovertime.References1美RonSoukup.MicrosoftSQLServer技术内幕M.姜英,彭梓东,孔得志,等译1北京:清华大学出版社,1999.1382156,5332593.2汪晓平,吴勇强,张宏林,等1ASP网络开发技术M1北京:人民邮电出版社,2000.1872229.381XiaYangUsingMicrosoftSQLServerEfficientlyonNet©1994-2006ChinaAcademicJournalElectronicPublishingHouse.Allrightsreserved.http:/www.cnki.net