



全文預(yù)覽已結(jié)束
付費(fèi)下載
下載本文檔
版權(quán)說明:本文檔由用戶提供并上傳,收益歸屬內(nèi)容提供方,若內(nèi)容存在侵權(quán),請進(jìn)行舉報(bào)或認(rèn)領(lǐng)
文檔簡介
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. 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶所有。
- 3. 本站RAR壓縮包中若帶圖紙,網(wǎng)頁內(nèi)容里面會(huì)有圖紙預(yù)覽,若沒有圖紙預(yù)覽就沒有圖紙。
- 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
- 5. 人人文庫網(wǎng)僅提供信息存儲(chǔ)空間,僅對用戶上傳內(nèi)容的表現(xiàn)方式做保護(hù)處理,對用戶上傳分享的文檔內(nèi)容本身不做任何修改或編輯,并不能對任何下載內(nèi)容負(fù)責(zé)。
- 6. 下載文件中如有侵權(quán)或不適當(dāng)內(nèi)容,請與我們聯(lián)系,我們立即糾正。
- 7. 本站不保證下載資源的準(zhǔn)確性、安全性和完整性, 同時(shí)也不承擔(dān)用戶因使用這些下載資源對自己和他人造成任何形式的傷害或損失。
最新文檔
- 2025至2030中國自動(dòng)售貨機(jī)行業(yè)市場深度研究與戰(zhàn)略咨詢分析報(bào)告
- 2025至2030中國膝關(guān)節(jié)骨性關(guān)節(jié)炎的粘液補(bǔ)充劑行業(yè)產(chǎn)業(yè)運(yùn)行態(tài)勢及投資規(guī)劃深度研究報(bào)告
- 2025至2030中國脫硫脫硝行業(yè)市場深度研究及發(fā)展前景投資可行性分析報(bào)告
- 2025至2030中國脊柱植入物和裝置行業(yè)產(chǎn)業(yè)運(yùn)行態(tài)勢及投資規(guī)劃深度研究報(bào)告
- 2025至2030中國聚氯乙烯電線行業(yè)市場占有率及投資前景評估規(guī)劃報(bào)告
- 八年級數(shù)學(xué)重點(diǎn)復(fù)習(xí)計(jì)劃
- 2025至2030中國網(wǎng)絡(luò)廣告行業(yè)營銷推廣模式及發(fā)展走向研究報(bào)告
- 2025至2030中國經(jīng)顱磁刺激儀(TMS)行業(yè)產(chǎn)業(yè)運(yùn)行態(tài)勢及投資規(guī)劃深度研究報(bào)告
- 從市場調(diào)研到定制化產(chǎn)品的成功案例分享
- 2025至2030中國組織提取袋行業(yè)產(chǎn)業(yè)運(yùn)行態(tài)勢及投資規(guī)劃深度研究報(bào)告
- 天津市和平區(qū)二十一中2025年英語七年級第二學(xué)期期末考試試題含答案
- 2025-2030中國轉(zhuǎn)輪除濕機(jī)行業(yè)前景動(dòng)態(tài)及投資規(guī)劃分析報(bào)告
- 八年級上冊語文必背課文資料合集
- 針灸醫(yī)學(xué)的歷史回顧之古代名醫(yī)的針灸先例
- 【艾瑞咨詢】2024年中國健康管理行業(yè)研究報(bào)告494mb
- 年產(chǎn)xxx千件自行車配件項(xiàng)目可行性研究報(bào)告
- DZ/T 0261-2014滑坡崩塌泥石流災(zāi)害調(diào)查規(guī)范(1∶50 000)
- T/CQAP 3014-2024研究者發(fā)起的抗腫瘤體細(xì)胞臨床研究細(xì)胞制劑制備和質(zhì)量控制規(guī)范
- 初中體育教學(xué)中德育教育的現(xiàn)狀、問題與突破路徑探究
- 基層供銷社管理制度
- 農(nóng)業(yè)供應(yīng)鏈管理考試試題及答案
評論
0/150
提交評論