外文翻译--在.NET上实现大量数据的客户操作 英文版.pdf_第1页
外文翻译--在.NET上实现大量数据的客户操作 英文版.pdf_第2页
外文翻译--在.NET上实现大量数据的客户操作 英文版.pdf_第3页
外文翻译--在.NET上实现大量数据的客户操作 英文版.pdf_第4页
全文预览已结束

下载本文档

版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领

文档简介

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

温馨提示

  • 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
  • 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
  • 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
  • 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
  • 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
  • 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
  • 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

评论

0/150

提交评论