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

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

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

宽屏显示 收藏 分享

页面加载中... ... 广告 0 秒后退出

资源预览需要最新版本的Flash Player支持。
您尚未安装或版本过低,建议您

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
编号:201311171123385862    大小:141.68KB    格式:PDF    上传时间:2013-11-17
  【编辑】
5
关 键 词:
教育专区 外文翻译 精品文档 外文翻译
温馨提示:
1: 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
2: 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
3.本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
5. 人人文库网仅提供交流平台,并不能对任何下载内容负责。
6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
  人人文库网所有资源均是用户自行上传分享,仅供网友学习交流,未经上传用户书面授权,请勿作他用。
0条评论

还可以输入200字符

暂无评论,赶快抢占沙发吧。

当前资源信息

4.0
 
(2人评价)
浏览:16次
英文资料库上传于2013-11-17

官方联系方式

客服手机:13961746681   
2:不支持迅雷下载,请使用浏览器下载   
3:不支持QQ浏览器下载,请用其他浏览器   
4:下载后的文档和图纸-无水印   
5:文档经过压缩,下载后原文更清晰   

相关资源

相关资源

相关搜索

教育专区   外文翻译   精品文档   外文翻译  
关于我们 - 网站声明 - 网站地图 - 友情链接 - 网站客服客服 - 联系我们
copyright@ 2015-2017 人人文库网网站版权所有
苏ICP备12009002号-5