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//