会员注册 | 登录 | 微信快捷登录 支付宝快捷登录 QQ登录 微博登录 | 帮助中心 人人文库renrendoc.com美如初恋!
站内搜索 百度文库

热门搜索: 直缝焊接机 矿井提升机 循环球式转向器图纸 机器人手爪发展史 管道机器人dwg 动平衡试验台设计

   首页 人人文库网 > 资源分类 > PDF文档下载

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

  • 资源星级:
  • 资源大小:141.68KB   全文页数:4页
  • 资源格式: PDF        下载权限:注册会员/VIP会员
您还没有登陆,请先登录。登陆后即可下载此文档。
  合作网站登录: 微信快捷登录 支付宝快捷登录   QQ登录   微博登录
友情提示
2:本站资源不支持迅雷下载,请使用浏览器直接下载(不支持QQ浏览器)
3:本站资源下载后的文档和图纸-无水印,预览文档经过压缩,下载后原文更清晰   

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

UsingMicrosoftSQLServerEfficientlyonNetXiaYang夏阳DepartmentofComputerScienceandTechnology,CUMT,Xuzhou221008AbstractHowtouseMicrosoftSQLstructuredquerylanguageServerefficientlyonnetisanalyzed.AndthekeytechnologyabouthowtouseADOtomanageSQLserverdatabasesandtheirdevicesandhowtoremotelytriggerofftheSQLserverdatabaseonnetforimprovingqueryspeedaremainlydiscussedinthispaper.KeywordsSQLServerADOASPdatabase1IntroduceMicroSQLServerisincommonuseasdatabasemanagementsystemoncurrentsmallnet2work.Applicationsusedtothiskindofnetworkdatabaseareprogressive.Keyaspectofdatabaseap2plicationdesignishowtheapplicationcodeinteractswiththedatabase.Someapplicationstreatthedatabasesimplyasastorageplaceforrecords.Theapplicationitselfperformsmostoftheoperationsonthedata,suchasfiltering,counting,ormatchingrecords.Otherapplicationstreatthedatabaseasadatamanagementengine,performingallofthesedataoperationsattheserver.ThefirststyleofdatabaseaccessiscommoninprogramswrittentouseanindexedsequentialaccessmethodISAMdatabase.Thesecondstyleofdatabaseaccessismoreappropriateforaprograminteractingwitharelationaldatabase.MicroSQLServerisjustthiskindofdatabase.2AboutSQLStructuredquerylanguageSQListhelan2guageofMicrosoftSQLServer.Itmakessensethatapplicationdeveloperslearntospeakthislanguagefluentlyiftheywanttheirapplicationstocommuni2cateeffectivelywiththeserver.EffectiveuseofSQLcanminimizetheamountofdatathatmustbereadfromandwrittentodiskdevicesattheserver.Simultaneously,effectiveuseofSQLcanminimizetheamountofdatashippedtoandfromSQLServeracrossthenetwork.SavingdiskIOandnetworkIOarethemostimportantfactorsforimprovingapplicationperformance.OneofthecapabilitiesofSQLisitsabilitytofilterdataattheserversothatonlytheminimumdatarequiredisreturnedtotheclient.Usingthesefacilitiesminimizesexpensivenetworktrafficbe2tweentheserverandclient.ThismeansthatWHEREclausesmustberestrictiveenoughtogetonlythedatathatisrequiredbytheapplication.Itisalwaysmoreefficienttofilterdataattheserverthantosendittotheclientandfilteritintheapplication.Thisalsoappliestocolumnsrequestedfromtheserver.AnapplicationthatissuesaSE2LECT3FROM...statementrequirestheservertoreturnallcolumndatatotheclient,whetherornottheclientapplicationhasboundthesecolumnsforuseinprogramvariables.Selectingonlythenec2essarycolumnsbynamewillavoidunnecessarynet2worktraffic.ItwillalsomakeyourapplicationmoreReceived6September2000Dec.2000JournalofChinaUniversityofMiningTechnologyVol.10No.2©19942006ChinaAcademicJournalElectronicPublishingHouse.Allrightsreserved.http//www.cnki.netrobustintheeventoftabledefinitionchanges,be2causenewlyaddedcolumnswontbereturnedtotheclientapplication.BeyondtheSQLsyntaxitself,performanceal2sodependsonhowyourapplicationrequestsaresultsetfromtheserver.InanapplicationusingODBC,thehowisdeterminedbythestatementoptionssetpriortoexecutingaSELECT.Whenyouleavethestatementoptionsatdefaultvalues,SQLServersendstheresultsetwiththemostefficientway.SQLServerassumesthatyourapplicationwillfetchalltherowsfromadefaultresultsetimmediately.Therefore,yourapplicationmustbufferanyrowsthatarenotusedimmediatelybutmaybeneededlater.ThisbufferingrequirementmakesitespeciallyimportantforyoutospecifybyusingSQLonlythedatayouneed.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,sometypesofservercursorsthosede2claredasdynamicarerestrictedtousinguniquein2dexesonly,whileothertypeskeysetandstaticcursorsmakeheavyuseoftemporarystorageattheserver.Forthesereasons,onlyuseservercursorswhereyourapplicationneedstheirfeatures.3OneMethodofIncreasingQuerySpeed3.1DesignideaSQLEnterpriseManagertoolprovidedbySQLServercanbeusedforcreatingandoperatingdatabase.ButitisbestthingforuserthattheyareabletoremotelytriggeroffthemanagementdatabasedesignedbySQLServer,whichspeciallymanagedatabasesandtheirdevicesneededbyappli2cations.Inordertoconnectdatabase,youshouldchoicetheoneofthedatabaseaccessinterfaces.Al2thoughtherearemanyinterfacesthatcanbeselect2edonnet,asthesuccessorofbothRDOandDAO,thenewestdatabaseaccessinterfaceADOmaybethebestchoice,becauseADOmakesitpossibletorealizedatabaseapplicationbasedonbrowser.ToremotelytriggeroffSQLServerforcreat2ingtemporaryviewandtableontheexisteddatabaseanditsdevicecanstorethepreviousresults181XiaYangUsingMicrosoftSQLServerEfficientlyonNet©19942006ChinaAcademicJournalElectronicPublishingHouse.Allrightsreserved.http//www.cnki.netfornextquerying.Itshrinkstherangeofqueryaswellasenhancesqueryspeed.Themethodisnotcomplicated.AfterconfiguredtheODBC,youcanusetheserverobjectofASPtocreateconnection,then,operatethedatabasefromADO.Thedetailedmethodisasfollows1togenerateexecutablesentencestrSQLcreateCREATEVIEWdbo.sessionviewname1ASSELECTTqueryView3.FROMTqueryViewwheretextfield2toconnectexisteddatabaseSetobjPagingConnServer.CreateObjectADODB.ConnectionobjPagingConn.OpenTlogin,sa3toexecuteapplicationandcreatetemporaryviewobjPagingRS.Opensessionquerystring,objPagingConn,3,1.3.2CurrentproblemandthemethodtosolvetheproblemItiswellknownthattheremustexistadatabasedevicewithresidualplacebeforecreatingadatabasebyusingCREATEDATABASEsen2tence.OtherwisewehavetouseDISKINITsen2tencetocreateanewdevice.Howeverthesentenceincludesmanyessentialparameters.Wecouldhard2lybesureoftheparametersvaluewithoutusingthemanagementtoolsofSQL.TaketheDISKINKsentenceasanexample,itscompletesyntaxisasfollowsDISKINITNAMElogicalname,PHYSNAMEphysicalname,VDEVNOvirtualdevicenumber,SIZEnumberof2Kblocks\,VSTARTvirtualaddressTheNAMEandSIZEareeasilyfetched.However,thephysicalnamePHYSNAMEandthevirtualdevicenumberVDEVNOaredifficulttodealwith.Theformerrequiresthewholepathnameofphysicalfilewithinaserverthelatterneedstolo2cateanumberbetween1and255whichisnotoccu2piedbyotherdevices.Whileinwritingthedatabasemanagementprogram,itisunexpectedwhereSQLServerhasbeeninstalledandwhichdevicenumbershavebeenoccupied.EventhoughusingtheSQLEnterpriseManagerofSQLSever,however,wemustalsoinputmanyparametersthatarenotoftenused.ToremotelytriggerofftheSQLServeronnetwillbemorecomplex.Inordertosolvetheproblem,weputforwardthefollowingscheme.1CreatingsentenceparametersfordeviceTheDISKINITsentenceisthesentenceforcreatingdevice.Inordertosimplifytheproblemmentionedabove,wemaysetadevicefilethathasthesamenamewiththedatabaseandsaveitinthesubdirectorywhichhassavedmasterdevice.Thenameofthedatabasehasalreadyfixedwhentheap2plicationwasdesigned.ThesubdirectoryofmasterdevicecouldbeinquiredfromthesystemtableSYS2DEVICES.Thusthephysicalnameparameterofthedevicefileisfixed.Theproblemofvirtualdevicenumberisprettycomplex,becausethereisnosuchafieldcalledvir2tualdevicenumberinthesystemtableSYSDE2VICES.Thereforewehavetotryanotherway.AfteranalyzingthesystemstoredprocedureSP2HELPDEVICEoftheSQLSever,wefoundthatvirtualdevicenumberwashiddenintheLOWfieldofthesystemtableSYSDEVICES.ByusinganothersystemtableSPT2VALUES,wecanfindthevirtualdevicenumberofeachdevice.ThereforeifwecanlocateinacirclewhetheracertaindevicenumberisintheSYSDEVICESornot,wemayfindthevirtualdevicenumberthatcouldbeused.Asforthesizeofthedatabasedevice,wehadbettermakeitalittlebigger,orletuserssetit.2CreatingsentenceparametersfordatabaseThesentenceforcreatingdatabaseisasfol2lowsCREATEDATABASEdatabasenameON{DEFAULTßdatabasedevice}\size\\,databasedevice\size\\...\LOGONdatabasedevice\size\...\\,databasedevice\size\\...\FORLOADMostoftheaboveparametersareoptional.Weonlyneedtodecidethedevicenameanddatabasesize.Howeverthedatabasename,thedevicename281JournalofChinaUniversityofMiningTechnologyVol.10No.2©19942006ChinaAcademicJournalElectronicPublishingHouse.Allrightsreserved.http//www.cnki.netandthesizehavealreadyfixedwhencreatingthedevice.Hencethereisnoproblemabouttheparam2etersinthissentence.3.3Frequentlyusedfunctionsinmanagingdatabaseanditsdevice1TopickupthecurrentworkingdatabaseThemanagementtaskisusuallycompletedinthemasterdatabase.Thereforewehadbettersavethecurrentworkingdatabasebeforethetaskisexe2cuted.Inthiswaywemayswitchbackconvenientlyafterfinishingthetask.PublicFunctionSQL2GetCurrentDatabaseNameCnAsADODB.Connec2tionAsStringDimsSQLAsStringDimRSAsNewADODB.RecordsetOnErrorGoToerrSQLGetCurrent2DatabaseNamesSQLselectCurrentDBDBNAMERS.OpensSQL,CnSQLGetCurrentDatabaseNameTrim$RSCurrentDBRS.CloseExitFunctionerrSQLGetCurrentDatabaseNameSQLGetCurrentDatabaseNameEndFunction2TojudgewhetheradatabasedeviceexistsornotPublicFunctionSQLExistDeviceNameCnAsADODB.Connection,sDevNameAsStringAsBooleanTojudgetheexistenceofadevicebyitsname.Ifexisted,return1else,return0.DimsSQLAsStringDimRSAsNewADODB.RecordsetDimbTmpAsBooleanOnErrorGoToerrSQLExistDeviceNamesSQLselectCntDevcountfrommaster.dbo.sysdeviceswherenamesDev2NameRS.OpensSQL,CnIfRSCntDev0ThenbTmpFalseElsebTmpTrueRS.CloseSQLExistDeviceNamebTmpExitFunctionerrSQLExistDeviceNameSQLExistDeviceNameFalseEndFunction3TojudgewhetheravirtualdevicenumberisoccupiedornotSQLExistDeviceNumber.4Togetthesmallestunoccupiedvirtualde2vicenumberSQLGetUnusedDeviceNumber.5TogettheDATAsubdirectorypathinSQLServersetupdirectorySQLGetDataPath.6TocreateanewdeviceSQLCreateDevice.7TocreateanewdatabaseSQLCreate2Database65.8TogetthedetailedinformationofdatabasedeviceSQLGetDeviceInfo.9ToextendthesizeofdatabasedeviceSQL2ExpandDevice.10TojudgewhetheradatabaseexistsornotSQLExistDatabase.11TodeleteadatabaseSQLDropDatabase.12TodeleteadatabasedeviceSQLDropDe2vice.13TogettheversioninformationofSQLSeverSQLGetVersionString.4ConclusionSomedevelopingtechniquesforSQLServerarediscussedinthispaper.Thekindofproblemsshouldbefurtherresearchedinfuture.Inbrief,anapplicationthatiswelldesignedforSQLServer1UsesSQLeffectively.2Minimizesnetworkroundtripstotheserverduringatransaction.3Usesstoredprocedures.4Incorporatestheessentialindexingandcon2figurationstepsforSQLServer.Thesecharacteristicsdonthavetobeappliedwithanall2or2nothingapproach.Theycanbeincor2poratedintoanapplicationovertime.References\1\美RonSoukup.MicrosoftSQLServer技术内幕M\.姜英,彭梓东,孔得志,等译1北京清华大学出版社,1999.1382156,5332593.\2\汪晓平,吴勇强,张宏林,等1ASP网络开发技术M1北京人民邮电出版社,2000.1872229.381XiaYangUsingMicrosoftSQLServerEfficientlyonNet©19942006ChinaAcademicJournalElectronicPublishingHouse.Allrightsreserved.http//www.cnki.net

注意事项

本文(外文翻译--在.NET上实现大量数据的客户操作 英文版.pdf)为本站会员(英文资料库)主动上传,人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对上载内容本身不做任何修改或编辑。 若此文所含内容侵犯了您的版权或隐私,请立即通知人人文库网([email protected]),我们立即给予删除!

温馨提示:如果因为网速或其他原因下载失败请重新下载,重复下载不扣分。

copyright@ 2015-2017 人人文库网网站版权所有
苏ICP备12009002号-5