Performance tips for the data tierWord文档格式.docx

Performance tips for the data tierWord文档格式.docx

《Performance tips for the data tierWord文档格式.docx》由会员分享，可在线阅读，更多相关《Performance tips for the data tierWord文档格式.docx（14页珍藏版）》请在冰豆网上搜索。

UsingDatabaseMetadataMethods

BecausedatabasemetadatamethodsthatgenerateResultSetobjectsareslowcomparedtootherJDBCmethods,theirfrequentusecanimpairsystemperformance.Theguidelinesinthissectionwillhelpyouoptimizesystemperformancewhenselectingandusingdatabasemetadata.

MinimizingtheUseofDatabaseMetadataMethods

ComparedtootherJDBCmethods,databasemetadatamethodsthatgenerateResultSetobjectsarerelativelyslow.Applicationsshouldcacheinformationreturnedfromresultsetsthatgeneratedatabasemetadatamethodssothatmultipleexecutionsarenotneeded.

AlthoughalmostnoJDBCapplicationcanbewrittenwithoutdatabasemetadatamethods,youcanimprovesystemperformancebyminimizingtheiruse.ToreturnallresultcolumninformationmandatedbytheJDBCspecification,aJDBCdrivermayhavetoperformcomplexqueriesormultiplequeriestoreturnthenecessaryresultsetforasinglecalltoadatabasemetadatamethod.TheseparticularelementsoftheSQLlanguageareperformanceexpensive.

Applicationsshouldcacheinformationfromdatabasemetadatamethods.Forexample,callgetTypeInfoonceintheapplicationandcacheawaytheelementsoftheresultsetthatyourapplicationdependson.Itisunlikelythatanyapplicationusesallelementsoftheresultsetgeneratedbyadatabasemetadatamethod,sothecacheofinformationshouldnotbedifficulttomaintain.

AvoidingSearchPatterns

Usingnullargumentsorsearchpatternsindatabasemetadatamethodsresultsingeneratingtime-consumingqueries.Inaddition,networktrafficpotentiallyincreasesduetounwantedresults.Alwayssupplyasmanynon-nullargumentstoresultsetsthatgeneratedatabasemetadatamethodsaspossible.

Becausedatabasemetadatamethodsareslow,applicationsshouldinvokethemasefficientlyaspossible.Manyapplicationspassthefewestnon-nullargumentsnecessaryforthefunctiontoreturnsuccess.

forexample:

ResultSetWSrs=WSc.getTables（null,null,"

WSTable"

null）;

shouldbe:

ResultSetWSrs=WSc.getTables（"

cat1"

"

johng"

"

TABLE"

）;

InthefirstgetTables（）call,theapplicationprobablywantstoknowifthetableWSTableexists.Ofcourse,aJDBCdrivertakesthecallliterallyandinterpretstherequestdifferently.AJDBCdriverinterpretstherequestas:

returnalltables,views,systemtables,synonyms,temporarytables,oraliasesthatexistinanydatabaseschemainsideanydatabasecatalogthatarenamed‘WSTable’.

ThesecondcalltogetTables（）moreaccuratelyreflectswhattheapplicationwantstoknow.AJDBCdriverinterpretsthisrequestas:

returnalltablesthatexistinthe‘johng’schemainthecurrentcatalogwherethenameis‘WSTable’.

Clearly,aJDBCdrivercanprocessthesecondrequestmuchmoreefficientlythanitcanprocessthefirstrequest.

Sometimes,littleinformationisknownabouttheobjectforwhichyouarerequestinginformation.Anyinformationthattheapplicationcansendthedriverwhencallingdatabasemetadatamethodscanresultinimprovedperformanceandreliability.

UsingaDummyQuerytoDetermineTableCharacteristics

AvoidusinggetColumns（）todeterminecharacteristicsaboutatable.Instead,useadummyquerywithgetMetadata（）.

Consideranapplicationthatallowstheusertochoosethecolumnsthatwillbeselected.ShouldtheapplicationusegetColumns（）toreturninformationaboutthecolumnstotheuserorinsteadprepareadummyqueryandcallgetMetadata（）?

Case1:

GetColumnsMethod

ResultSetWSrc=WSc.getColumns（..."

UnknownTable"

...）;

//ThiscalltogetColumns（）willgenerateaqueryto

//thesystemcatalogs...possiblyajoin

//whichmustbeprepared,executed,andproduce

//aresultset

...

WSrc.next（）;

stringCname=getString（4）;

//usermustretrieveNrowsfromtheserver

//N=#resultcolumnsofUnknownTable

//resultcolumninformationhasnowbeenobtained

Case2:

GetMetadataMethod

//preparedummyquery

PreparedStatementWSps=WSc.prepareStatement（"

SELECT*fromUnknownTableWHERE1=0"

//queryisneverexecutedontheserver-onlyprepared

ResultSetMetaDataWSsmd=WSps.getMetaData（）;

intnumcols=WSrsmd.getColumnCount（）;

...

intctype=WSrsmd.getColumnType（n）

Inbothcases,aqueryissenttotheserver.ButinCase1,thequerymustbepreparedandexecuted,theresultdescriptioninformationmustbeformulated,andaresultsetofrowsmustbesenttotheclient.InCase2,asimplequerymustbepreparedandonlyresultdescriptioninformationmustbeformulated.Clearly,Case2isthebetterperformingmodel.

Tosomewhatcomplicatethisdiscussion,letusconsideraDBMSserverthatdoesnotnativelysupportpreparingaSQLstatement.TheperformanceofCase1doesnotchange,buttheperformanceofCase2increasesslightlybecausethedummyquerymustbeevaluatedinsteadofonlyprepared.BecausetheWhereclauseofthequeryalwaysevaluatestoFALSE,thequerygeneratesnoresultrowsandshouldexecutewithoutaccessingtabledata.Forthissituation,method2stilloutperformsmethod1.

Insummary,alwaysuseresultsetmetadatatoretrievetablecolumninformationsuchascolumnnames,columndatatypes,andcolumnprecisionandscale.OnlyusegetColumns（）whentherequestedinformationcannotbeobtainedfromresultsetmetadata（i.e.tablecolumndefaultvalues）.

Lookoutfornextmonth'

sperformancetip,on'

RetrievingOnlyRequiredData'

.

∙UsingDatabaseMetaDataMethodsAppropriately

∙RetrievingonlyRequiredData

∙SelectingFunctionsthatOptimizePerformance

∙ManagingConnectionsandUpdates

∙DesigningandRunningBenchmarksforPerformance

Inthelastperformancetip,wediscussedusingdatabasemetadataappropriately.ThesegeneralrulesaboutretrievingdatashouldhelpyousolvesomecommonJDBCsystemperformanceproblems.

RetrievingData

Toretrievedataefficiently,returnonlythedatathatyouneed,andchoosethemostefficientmethodofdoingso.TheguidelinesinthissectionwillhelpyoutooptimizesystemperformancewhenretrievingdatawithJDBCapplications.

RetrievingLongData

Unlessitisnecessary,applicationsshouldnotrequestlongdatabecauseretrievinglongdataacrossanetworkisslowandresource-intensive.

Mostusersdon’twanttoseelongdata.Iftheuserdoeswanttoprocesstheseresultitems,thentheapplicationcanquerythedatabaseagain,specifyingonlythelongcolumnsintheselectlist.Thismethodallowstheaverageusertoretrievetheresultsetwithouthavingtopayahighperformancepenaltyfornetworktraffic.

Althoughthebestmethodistoexcludelongdatafromtheselectlist,someapplicationsdonotformulatetheselectlistbeforesendingthequerytotheJDBCdriver（thatis,someapplicationssendselect*from<

tablename>

...）.Iftheselectlistcontainslongdata,mostJDBCdriversmustretrievethatdataatfetchtime,eveniftheapplicationdoesnotaskforthelongdataintheresultset.Whenpossible,thedevelopershouldattempttoimplementamethodthatdoesnotretrieveallcolumnsofthetable.

Forexample,considerthefollowingJDBCcode:

ResultSetrs=stmt.executeQuery（"

select*fromEmployeeswhereSSID='

999-99-2222'

"

rs.next（）;

stringname=rs.getString（4）;

RememberthataJDBCdriverisnotintuitive.Ithasnoideawhatresultcolumnsanapplicationmightbetryingtoretrievewhenthequeryisexecuted.Adriveronlyknowsthatanapplicationcanrequestanyoftheresultcolumns.WhentheJDBCdriverprocessesthers.next（）request,itwillmostlikelyreturnatleastone（ifnotmore）resultrowsacrossthenetworkfromthedatabaseserver.Inthiscase,aresultrowwillcontainallthecolumnvaluesforeachrow–includinganemployeepictureiftheEmployeestablehappenstocontainsuchacolumn.Limitingtheselectlisttocontainonlythenamecolumnresultsindecreasednetworktrafficandafasterperformingqueryatruntime.

Additionally,althoughthegetClob（）andgetBlob（）methodsallowtheapplicationtocontrolhowlongdataisretrievedintheapplication,thedevelopermustrealizethatinmanycasestheJDBCdriveremulatesthesemethodsduetothelackoftrueLOBlocatorsupportintheDBMS.Insuchcases,thedrivermustretrieveallofthelongdataacrossthenetworkbeforeexposingthegetClob（）andgetBlob（）methods.

ReducingtheSizeofDataRetrieved

Sometimeslongdatamustberetrieved.Whenthisisthecase,rememberthatmostusersmightnotwanttosee100KB（ormore）oftextonthescreen.

Toreducenetworktrafficandimproveperformance,youcanreducethesizeofanydatabeingretrievedtosomemanag