java JDBC 外文翻译 外文文献 英文文献.docx

java JDBC 外文翻译 外文文献 英文文献.docx

《java JDBC 外文翻译 外文文献 英文文献.docx》由会员分享，可在线阅读，更多相关《java JDBC 外文翻译 外文文献 英文文献.docx（25页珍藏版）》请在冰豆网上搜索。

javaJDBC外文翻译外文文献英文文献

javaJDBC外文翻译外文文献英文文献

原文一:

JavaProgrammingwithOracleJDBC:

Performance

Performanceisusuallyconsideredanissueattheendofadevelopmentcyclewhenitshouldreallybeconsideredfromthestart.Often,ataskcalled"performancetuning"isdoneafterthecodingiscomplete,andtheenduserofaprogramcomplainsabouthowlongittakestheprogramtocompleteaparticulartask.Thenetresultofwaitinguntiltheendofthedevelopmentcycletoconsiderperformanceincludestheexpenseoftheadditionaltimerequiredtorecodeaprogramtoimproveitsperformance.It'smyopinionthatperformanceissomethingthatisbestconsideredatthestartofaproject.

WhenitcomestoperformanceissuesconcerningJDBCprogrammingtherearetwomajorfactorstoconsider.ThefirstistheperformanceofthedatabasestructureandtheSQLstatementsusedagainstit.ThesecondistherelativeefficiencyofthedifferentwaysyoucanusetheJDBCinterfacestomanipulateadatabase.

Intermsofthedatabase'sefficiency,youcanusetheEXPLAINPLANfacilitytoexplainhowthedatabase'soptimizerplanstoexecuteyourSQLstatements.Armedwiththisknowledge,youmaydeterminethatadditionalindexesareneeded,orthatyourequireanalternativemeansofselectingthedatayoudesire.

Ontheotherhand,whenitcomestousingJDBC,youneedtoknowaheadoftimetherelativestrengthsandweaknessesofusingauto-commit,SQL92syntax,andaStatementversusaPreparedStatementversusaCallableStatementobject.Inthischapter,we'llexaminetherelativeperformanceofvariousJDBCobjectsusingexampleprogramsthatreporttheamountoftimeittakestoaccomplishagiventask.We'llfirstlookatauto-commit.Next,we'lllookattheimpactoftheSQL92syntaxparser.Thenwe'llstartaseriesofcomparisonsoftheStatementobjectversusthePreparedStatementobjectversustheCallableStatementobject.Atthesametimewe'llalsoexaminetheperformanceoftheOCIversustheThindriverineachsituationtoseeif,asOracle'sclaims,thereisasignificantenoughperformancegainwiththeOCIdriverthatyoushoulduseitinsteadoftheThindriver.Forthemostpart,ourdiscussionswillbebasedontimingdatafor1,000insertsintothetestperformancetableTESTXXXPERF.Thereareseparateprogramsforperformingthese1,000insertsusingtheOCIdriverandtheThindriver.

Theperformancetestprogramsthemselvesareverysimpleandareavailableonlinewiththerestoftheexamplesinthisbook.However,forbrevity,I'llnotshowthecodefortheexamplesinthischapter.I'llonlytalkaboutthem.Althoughtheactualtimingvalues

changefromsystemtosystem,theirrelativevalues,orratiosfromonesystemtoanother,remainconsistent.ThetimingsusedinthischapterweregatheredusingWindows2000.Usingobjectivedatafromtheseprogramsallowsustocometofactualconclusionsonwhichfactorsimproveperformance,ratherthanrelyingonhearsay.

I'msureyou'llbesurprisedattherealityofperformancefortheseobjects,andIhopeyou'llusethisknowledgetoyouradvantage.Let'sgetstartedwithalookatthetestingframeworkusedinthischapter.

ATestingFramework

Forthemostpart,thetestprogramsinthischapterreportthetimingsforinsertingdataintoatable.IpickedanINSERTstatementbecauseiteliminatestheperformancegainofthedatabaseblockbuffersthatmayskewtimingsforanUPDATE,DELETE,orSELECT.

Thetesttableusedintheexampleprogramsinthischapterisasimplerelationaltable.IwantedittohaveaNUMBER,asmallVARCHAR2,alargeVARCHAR2,andaDATEcolumn.TableTESTXXXPERFisdefinedas:

createtableTestXXXPerf（

idnumber,

codevarchar2（30）,

descrvarchar2（80）,

insert_uservarchar2（30）,

insert_datedate）

tablespaceuserspctfree20

storage（initial1Mnext1Mpctincrease0）;

altertableTestXXXPerf

addconstraintTestXXXPerf_Pk

primarykey（id）

usingindex

tablespaceuserspctfree20

storage（initial1Mnext1Mpctincrease0）;

Theinitialextentsizeusedforthetablemakesitunlikelythatthedatabasewillneedtotakethetimetoallocateanotherextentduringtheexecutionofoneofthetestprograms.Therefore,extentallocationwillnotimpactthetimings.Giventhisbackground,youshouldhaveacontexttounderstandwhatisdoneineachsectionbyeachtestprogram.Auto-Commit

Bydefault,JDBC'sauto-commitfeatureison,whichmeansthateachSQLstatementiscommittedasitisexecuted.IfmorethanoneSQLstatementisexecutedbyyour

program,thenasmallperformanceincreasecanbeachievedbyturningoffauto-commit.

Let'stakealookatsomenumbers.Table19-1showstheaveragetime,inmilliseconds,neededtoinsert1,000rowsintotheTESTXXXPERFtableusingaStatementobject.Thetimingsrepresenttheaveragefromthreerunsoftheprogram.Bothdriversexperienceapproximatelyaone-secondlossasoverheadforcommittingbetweeneachSQLstatement.Whenyoudividethatonesecondby1,000inserts,youcanseethatturningoffauto-commitsavesapproximately0.001seconds（1millisecond）perSQLstatement.Whilethat'snotinterestingenoughtowritehomeabout,itdoesdemonstratehowauto-commitcanimpactperformance.

Table19-1:

Auto-committimings（inmilliseconds）

Auto-commitOCIThin

On3,7123,675

Off2,6132,594

Clearly,it'smoreimportanttoturnoffauto-commitformanagingmultisteptransactionsthanforgainingperformance.Butonaheavilyloadedsystemwheremanyusersarecommittingtransactions,theamountoftimeittakestoperformcommitscanbecomequitesignificant.Somyrecommendationistoturnoffauto-commitandmanageyourtransactionsmanually.Therestofthetestsinthischapterareperformedwithauto-committurnedoff.

SQL92TokenParsing

Likeauto-commit,SQL92escapesyntaxtokenparsingisonbydefault.Incaseyoudon'trecall,SQL92tokenparsingallowsyoutoembedSQL92escapesyntaxinyourSQLstatements（see"OracleandSQL92EscapeSyntax"inChapter9）.Thesestandards-basedsnippetsofsyntaxareparsedbyaJDBCdrivertransformingtheSQLstatementintoitsnativesyntaxforthetargetdatabase.SQL92escapesyntaxallowsyoutomakeyourcodemoreportable--butdoesthisportabilitycomewithacostintermsofperformance?

Table19-2showsthenumberofmillisecondsneededtoinsert1,000rowsintotheTESTXXXPERFtable.TimingsareshownwiththeSQL92escapesyntaxparseronandoffforboththeOCIandThindrivers.Asbefore,thesetimingsrepresenttheresultofthreeprogramrunsaveragedtogether.

Table19-2:

SQL92tokenparsertimings（inmilliseconds）

SQL92parserOCIThin

On2,5672,514

Off2,7442,550

NoticefromTable19-2thatwiththeOCIdriverwelose177millisecondswhenescapesyntaxparsingisturnedoff,andweloseonly37millisecondswhentheparseristurnedoffwiththeThindriver.Theseresultsaretheoppositeofwhatyoumightintuitivelyexpect.ItappearsthatbothdrivershavebeenoptimizedforSQL92parsing,soyoushouldleaveitonforbestperformance.

NowthatyouknowyouneverhavetoworryaboutturningtheSQL92parseroff,let'smoveontosomethingthathassomepotentialforprovidingasubstantialperformanceimprovement.

StatementVersusPreparedStatement

There'sapopularbeliefthatusingaPreparedStatementobjectisfasterthanusingaStatementobject.Afterall,apreparedstatementhastoverifyitsmetadataagainstthedatabaseonlyonce,whileastatementhastodoiteverytime.Sohowcoulditbeanyotherway?

Well,thetruthofthematteristhatittakesabout65iterationsofapreparedstatementbeforeitstotaltimeforexecutioncatchesupwithastatement.WhenitcomestowhichSQLstatementobjectperformsbetterundertypicaluse,aStatementora

PreparedStatement,thetruthisthattheStatementobjectyieldsthebestperformance.WhenyouconsiderhowSQLstatementsaretypicallyusedinanapplication--1or2here,maybe10-20（rarelymore）pertransaction--yourealizethataStatementobjectwillperformtheminlesstimethanaPreparedStatementobject.Inthenexttwosections,we'lllookatthisperformanceissuewithrespecttoboththeOCIdriverandtheThindriver.TheOCIDriver

Table19-3showsthetimingsinmillisecondsfor1insertand1,000insertsintheTESTXXXPERFtable.TheinsertsaredonefirstusingaStatementobjectandthenaPreparedStatementobject.Ifyoulookattheresultsfor1,000inserts,youmaythinkthatapreparedstatementperformsbetter.Afterall,at1,000inserts,thePreparedStatementobjectisalmosttwiceasfastastheStatementobject,butifyouexamineFigure19-1,you'llseeadifferentstory.

Table19-3:

OCIdrivertimings（inmilliseconds）

InsertsStatementPreparedStatement

110113

1,0002,8041,412

Figure19-1isagraphofthetimingsneededtoinsertvaryingnumbersofrowsusingbothaStatementobjectandaPreparedStatementobject.Thenumberofinsertsbeginsat1andclimbsinintervalsof10uptoamaximumof150inserts.Forthisgraphandforthose

thatfollow,thelinesthemselvesarepolynomialtrendlineswithafactorof2.Ichosepolynomiallinesinsteadofstraighttrendlinessoyoucanbetterseeachangeintheperformanceasthenumberofinsertsincreases.Ichoseafactorof2sothelineshaveonlyonecurveinthem.Theimportantthingtonoticeaboutthegraphisthatit'snotuntilabout65insertsthatthePreparedStatementobjectoutperformstheStatementobject.65inserts!

Clearly,theStatementobjectismoreefficientundertypicalusewhenusingtheOCIdriver.

Figure19-1

TheThinDriver

IfyouexamineTable19-4（whichshowsthesametimingsasforTable19-3,