WEO啦

外文翻译--在.NET上实现大量数据的客户操作 英文版
收录时间:2022-03-04 14:21:45  浏览:218

1、UsingMicrosoftSQLServerEfficientlyonNetXiaYang(夏阳)(DepartmentofComputerScienceandTechnology/CUMT/Xuzhou221008)AbstractHowtouseMicrosoftSQL(structuredquerylanguage)Serverefficientlyonnetisanalyzed/AndthekeytechnologyabouthowtouseADOtomanageSQLserverdatabasesandtheirdevicesandhowtoremotelytriggeroffth

2、eSQLserverdatabaseonnetforimprovingqueryspeedaremainlydiscussedinthispaper/Keywords/SQLServer;ADO;ASP;database1IntroduceMicroSQLServerisincommonuseasdatabasemanagementsystemoncurrentsmallnet2work/Applicationsusedtothiskindofnetworkdatabaseareprogressive/Keyaspectofdatabaseap2plicationdesignishowthea

3、pplicationcodeinteractswiththedatabase/Someapplicationstreatthedatabasesimplyasastorageplaceforrecords/Theapplicationitselfperformsmostoftheoperationsonthedata/suchasfiltering/counting/ormatchingrecords/Otherapplicationstreatthedatabaseasadatamanagementengine/performingallofthesedataoperationsatthes

4、erver/Thefirststyleofdatabaseaccessiscommoninprogramswrittentouseanindexedsequentialaccessmethod(ISAM)database/Thesecondstyleofdatabaseaccessismoreappropriateforaprograminteractingwitharelationaldatabase/MicroSQLServerisjustthiskindofdatabase/2AboutSQLStructuredquerylanguage(SQL)isthelan2guageofMicr

5、osoftSQLServer/Itmakessensethatapplicationdeveloperslearnto“speak”thislanguagefluentlyiftheywanttheirapplicationstocommuni2cateeffectivelywiththeserver/EffectiveuseofSQLcanminimizetheamountofdatathatmustbereadfromandwrittentodiskdevicesattheserver/Simultaneously/effectiveuseofSQLcanminimizetheamount

6、ofdatashippedtoandfromSQLServeracrossthenetwork/SavingdiskIOandnetworkIOarethemostimportantfactorsforimprovingapplicationperformance/OneofthecapabilitiesofSQLisitsabilitytofilterdataattheserversothatonlytheminimumdatarequiredisreturnedtotheclient/Usingthesefacilitiesminimizesexpensivenetworktrafficb

7、e2tweentheserverandclient/ThismeansthatWHEREclausesmustberestrictiveenoughtogetonlythedatathatisrequiredbytheapplication/Itisalwaysmoreefficienttofilterdataattheserverthantosendittotheclientandfilteritintheapplication/Thisalsoappliestocolumnsrequestedfromtheserver/AnapplicationthatissuesaSE2LECT3FRO

8、M/statementrequirestheservertoreturnallcolumndatatotheclient/whetherornottheclientapplicationhasboundthesecolumnsforuseinprogramvariables/Selectingonlythenec2essarycolumnsbynamewillavoidunnecessarynet2worktraffic/ItwillalsomakeyourapplicationmoreReceived6September2000Dec/2000JournalofChinaUniversity

9、ofMining&TechnologyVol/10No/21994-2006ChinaAcademicJournalElectronicPublishingHouse/Allrightsreserved/http//robustintheeventoftabledefinitionchanges/be2causenewlyaddedcolumnswontbereturnedtotheclientapplication/BeyondtheSQLsyntaxitself/performanceal2sodependsonhowyourapplicationrequestsaresultsetfro

10、mtheserver/InanapplicationusingODBC/the“howisdeterminedbythestatementoptionssetpriortoexecutingaSELECT/Whenyouleavethestatementoptionsatdefaultvalues/SQLServersendstheresultsetwiththemostefficientway/SQLServerassumesthatyourapplicationwillfetchalltherowsfromadefaultresultsetimmediately/Therefore/you

11、rapplicationmustbufferanyrowsthatarenotusedimmediatelybutmaybeneededlater/Thisbufferingrequirementmakesitespeciallyimportantforyoutospecify(byusingSQL)onlythedatayouneed/Itmayseemeconomicaltorequestadefaultre2sultsetandfetchrowsonlyasyourapplicationuserneedsthem/butthisisfalseeconomy/Unfetchedrowsfr

12、omadefaultresultsetcantieupyourcon2nectionwiththeserver/blockingotherworkinthesametransaction/Stillworse/unfetchedrowsfromadefaultresultsetcancauseSQLServertoholdlocksattheserver/possiblypreventingotherusersfromupdating/Thishiddenproblemmaynotshowupinsmall2scaletesting/butitcanappearlaterwhentheappl

13、icationisrunning/Thelessonhereissimple2immediatelyfetchallrowsfromadefaultre2sultset/Someapplicationscannotbufferallthedatatheyrequestfromtheserver/Forexample/anap2plicationthatqueriesalargetableandallowstheus2ertospecifytheselectioncriteriamayreturnnorowsormillionsofrows/Theuserisunlikelytowanttose

14、emillionsofrows/Instead/theuserismorelikelytore2executethequerywithnarrowerselectioncriteria/Inthiscase/fetchingandbuffer2ingmillionsofrowsonlytohavethemthrownawaybytheuserwouldbeawasteoftimeandresources/Forapplicationslikethese/SQLServeroffersservercursorsthatallowanapplicationtofetchasmallsubsetor

15、blockofrowsfromanarbitrarilylargeresultset/Iftheuserwantstoseeotherrecordsfromthesameresultset/aservercursoral2lowstheapplicationtofetchanyotherblockofrowsfromtheresultset/includingthenextnrows/thepreviousnrows/ornrowsstartingatacertainrownumberintheresultset/SQLServerdoestheworktofulfilleachblockfe

16、tchrequestonlyasneeded/andSQLServerdoesnotnormallyholdlocksbe2tweenblockfetchesonservercursors/Servercur2sorsalsoallowanapplicationtodoapositionedup2dateordeleteofafetchedrowwithouthavingtofigureoutthesourcetableandprimarykeyoftherow/Iftherowdatachangesbetweenthetimeitisfetchedandthetimetheupdateisr

17、equested/SQLServerdetectstheproblemandpreventsalostup2date/Allofthesefeaturesofservercursorscomeatacost/IfalltheresultsfromagivenSELECTstate2mentaregoingtobeusedinyourapplication/aservercursorisalwaysgoingtobemoreexpensivethanadefaultresultset/Adefaultresultsetalwaysrequirescommunicationbetweenclien

18、tandserver/Moreover/sometypesofservercursors(thosede2claredasdynamic)arerestrictedtousinguniquein2dexesonly/whileothertypes(keysetandstaticcursors)makeheavyuseoftemporarystorageattheserver/Forthesereasons/onlyuseservercursorswhereyourapplicationneedstheirfeatures/3OneMethodofIncreasingQuerySpeed3/1D

19、esignideaSQLEnterpriseManagertoolprovidedbySQLServercanbeusedforcreatingandoperatingdatabase/ButitisbestthingforuserthattheyareabletoremotelytriggeroffthemanagementdatabasedesignedbySQLServer/whichspeciallymanagedatabasesandtheirdevicesneededbyappli2cations/Inordertoconnectdatabase/youshouldchoiceth

20、eoneofthedatabaseaccessinterfaces/Al2thoughtherearemanyinterfacesthatcanbeselect2edonnet/asthesuccessorofbothRDOandDAO/thenewestdatabaseaccessinterfaceADOmaybethebestchoice/becauseADOmakesitpossibletorealizedatabaseapplicationbasedonbrowser/ToremotelytriggeroffSQLServerforcreat2ingtemporaryviewandta

21、bleontheexisteddatabaseanditsdevicecanstorethepreviousresults181XiaYangUsingMicrosoftSQLServerEfficientlyonNet1994-2006ChinaAcademicJournalElectronicPublishingHouse/Allrightsreserved/http//fornextquerying/Itshrinkstherangeofqueryaswellasenhancesqueryspeed/Themethodisnotcomplicated/Afterconfiguredthe

22、ODBC/youcanusetheserverobjectofASPtocreateconnection/then/operatethedatabasefromADO/Thedetailedmethodisasfollows/1)togenerateexecutablesentencestrSQLcreate=“CREATEVIEWdbo/“&session(“viewname”)&“1ASSELECTTqueryView3/FROMTqueryViewwhere”&textfield;2)toconnectexisteddatabaseSetobjPagingConn=Server/Crea

23、teObject(“ADODB/Connection”)objPagingConn/Open“Tlogin”/“sa”;3)toexecuteapplicationandcreatetemporaryviewobjPagingRS/Opensession(“querystring”)/objPagingConn/3/1/3/2CurrentproblemandthemethodtosolvetheproblemItiswellknownthattheremustexistadatabasedevicewithresidualplacebeforecreatingadatabasebyusing

24、CREATEDATABASEsen2tence/OtherwisewehavetouseDISKINITsen2tencetocreateanewdevice/Howeverthesentenceincludesmanyessentialparameters/Wecouldhard2lybesureoftheparametersvaluewithoutusingthemanagementtoolsofSQL/TaketheDISKINKsentenceasanexample/itscompletesyntaxisasfollows/DISKINITNAME=logicalname/PHYSNA

25、ME=physicalname/VDEVNO=virtualdevicenumber/SIZE=numberof2Kblocks/VSTART=virtualaddressTheNAMEandSIZEareeasilyfetched/However/thephysicalnamePHYSNAMEandthevirtualdevicenumberVDEVNOaredifficulttodealwith/Theformerrequiresthewholepathnameofphysicalfilewithinaserver;thelatterneedstolo2cateanumberbetween

26、1and255whichisnotoccu2piedbyotherdevices/Whileinwritingthedatabasemanagementprogram/itisunexpectedwhereSQLServerhasbeeninstalledandwhichdevicenumbershavebeenoccupied/EventhoughusingtheSQLEnterpriseManagerofSQLSever/however/wemustalsoinputmanyparametersthatarenotoftenused/ToremotelytriggerofftheSQLSe

27、rveronnetwillbemorecomplex/Inordertosolvetheproblem/weputforwardthefollowingscheme/1)CreatingsentenceparametersfordeviceTheDISKINITsentenceisthesentenceforcreatingdevice/Inordertosimplifytheproblemmentionedabove/wemaysetadevicefilethathasthesamenamewiththedatabaseandsaveitinthesubdirectorywhichhassa

28、vedmasterdevice/Thenameofthedatabasehasalreadyfixedwhentheap2plicationwasdesigned/ThesubdirectoryofmasterdevicecouldbeinquiredfromthesystemtableSYS2DEVICES/Thusthephysicalnameparameterofthedevicefileisfixed/Theproblemofvirtualdevicenumberisprettycomplex/becausethereisnosuchafieldcalled“vir2tualdevic

29、enumber”inthesystemtableSYSDE2VICES/Thereforewehavetotryanotherway/Afteranalyzingthesystem-storedprocedureSP2HELPDEVICEoftheSQLSever/wefoundthatvirtualdevicenumberwashiddenintheLOWfieldofthesystemtableSYSDEVICES/ByusinganothersystemtableSPT2VALUES/wecanfindthevirtualdevicenumberofeachdevice/Therefor

30、eifwecanlocateinacirclewhetheracertaindevicenumberisintheSYSDEVICESornot/wemayfindthevirtualdevicenumberthatcouldbeused/Asforthesizeofthedatabasedevice/wehadbettermakeitalittlebigger/orletuserssetit/2)CreatingsentenceparametersfordatabaseThesentenceforcreatingdatabaseisasfol2lows/CREATEDATABASEdatab

31、asenameONDEFAULTdatabasedevice=size/databasedevice=size/LOGONdatabasedevice=size//databasedevice=size/FORLOADMostoftheaboveparametersareoptional/Weonlyneedtodecidethedevicenameanddatabasesize/Howeverthedatabasename/thedevicename281JournalofChinaUniversityofMining&TechnologyVol/10No/21994-2006ChinaAc

32、ademicJournalElectronicPublishingHouse/Allrightsreserved/http//andthesizehavealreadyfixedwhencreatingthedevice/Hencethereisnoproblemabouttheparam2etersinthissentence/3/3Frequentlyusedfunctionsinmanagingdatabaseanditsdevice1)TopickupthecurrentworkingdatabaseThemanagementtaskisusuallycompletedinthemas

33、terdatabase/Thereforewehadbettersavethecurrentworkingdatabasebeforethetaskisexe2cuted/Inthiswaywemayswitchbackconvenientlyafterfinishingthetask/PublicFunctionSQL2GetCurrentDatabaseName(CnAsADODB/Connec2tion)AsStringDimsSQLAsStringDimRSAsNewADODB/RecordsetOnErrorGoToerrSQLGetCurrent2DatabaseNamesSQL=

34、“selectCurrentDB=DBNAME()”RS/OpensSQL/CnSQLGetCurrentDatabaseName=Trim(RS!CurrentDB)RS/CloseExitFunctionerrSQLGetCurrentDatabaseName/SQLGetCurrentDatabaseName=“”EndFunction2)TojudgewhetheradatabasedeviceexistsornotPublicFunctionSQLExistDeviceName(CnAsADODB/Connection/sDevNameAsString)AsBoolean(Tojud

35、getheexistenceofadevicebyitsname/Ifexisted/return“1”;else/return“0”/)DimsSQLAsStringDimRSAsNewADODB/RecordsetDimbTmpAsBooleanOnErrorGoToerrSQLExistDeviceNamesSQL=“selectCntDev=count(*)frommaster/dbo/sysdeviceswherename=“&sDev2Name&”RS/OpensSQL/CnIfRS!CntDev=0ThenbTmp=FalseElsebTmp=TrueRS/CloseSQLExi

36、stDeviceName=bTmpExitFunctionerrSQLExistDeviceName/SQLExistDeviceName=FalseEndFunction3)Tojudgewhetheravirtualdevicenumberisoccupiedornot/SQLExistDeviceNumber/4)Togetthesmallestunoccupiedvirtualde2vicenumber/SQLGetUnusedDeviceNumber/5)TogettheDATAsubdirectorypathinSQLServersetupdirectory/SQLGetDataP

37、ath/6)Tocreateanewdevice/SQLCreateDevice/7)Tocreateanewdatabase/SQLCreate2Database65/8)Togetthedetailedinformationofdatabasedevice/SQLGetDeviceInfo/9)Toextendthesizeofdatabasedevice/SQL2ExpandDevice/10)Tojudgewhetheradatabaseexistsornot/SQLExistDatabase/11)Todeleteadatabase/SQLDropDatabase/12)Todele

38、teadatabasedevice/SQLDropDe2vice/13)TogettheversioninformationofSQLSever/SQLGetVersionString/4ConclusionSomedevelopingtechniquesforSQLServerarediscussedinthispaper/Thekindofproblemsshouldbefurtherresearchedinfuture/Inbrief/anapplicationthatiswelldesignedforSQLServer/1)UsesSQLeffectively/2)Minimizesn

39、etworkroundtripstotheserverduringatransaction/3)Usesstoredprocedures/4)Incorporatestheessentialindexingandcon2figurationstepsforSQLServer/Thesecharacteristicsdonthavetobeappliedwithanall2or2nothingapproach/Theycanbeincor2poratedintoanapplicationovertime/References1美RonSoukup/MicrosoftSQLServer技术内幕M/姜英/彭梓东/孔得志/等译1北京/清华大学出版社/1999/1382156/5332593/2汪晓平/吴勇强/张宏林/等1ASP网络开发技术M1北京/人民邮电出版社/2000/1872229/381XiaYangUsingMicrosoftSQLServerEfficientlyonNet1994-2006ChinaAcademicJournalElectronicPublishingHouse/Allrightsreserved/http//

温馨提示:
1. WEO啦仅展示《外文翻译--在.NET上实现大量数据的客户操作 英文版》的部分公开内容,版权归原著者或相关公司所有。
2. 文档内容来源于互联网免费公开的渠道,若文档所含内容侵犯了您的版权或隐私,请通知我们立即删除。
3. 当前页面地址:https://www.weo.la/doc/3a0e75d8a8cfb2eb.html 复制内容请保留相关链接。