河南新华印刷集团有限公司32

1、Advanced Perl DBIMaking simple things easyand difficult things possible by Tim BunceTopical TopicsSpeed Speed Speed!Handling handles and binding valuesError checking and error handlingWheels within wheelsTransactionsDBI for the webTaintingHandling LONG/BLOB dataPortabilityProxy power and flexible mu

2、ltiplexWhats new and whats plannedTrimmed Topics and TipsLack of time prevents the inclusion of .Details of issues relating to specific databases and drivers(other than where used as examples of general issues)each driver would warrant a tutorial of its own!Non-trivial worked examplesHandy DBIx:* an

3、d other DBI related modules and anything Id not finished implementing when this was written .But I hope youll agree that theres ample information in the following 90+ slidesTips for those attending the conference tutorial:Doodle notes from my whitterings about the whys and wherefores on your printed

4、 copy of the slides as we go along.The DBI - Whats it all about?The Perl DBI defines and implements an interface to databasesPlug-in driver modules do the database-specific workDBI provides default methods, functions, tools etc for driversNot limited to the lowest common denominatorDesigned and buil

5、t for speedPowerful automatic error checking built-inValuable detailed call tracing/debugging built-inUseful detailed call profiling/benchmarking built-inDBI ModulePerl ApplicationDBD:OtherDBD:InformixDBD:OracleOracle ServerInformix ServerOther ServerA picture is worth?Speed Speed Speed!What helps,w

6、hat doesnt,and how to measure itGive me speed!DBI was designed for speed from day oneDBI method dispatcher written in hand-crafted XS/CDispatch to XS driver method calls is specially optimizedCached attributes returned directly by DBI dispatcherDBI overhead is generally insignificantSo well talk abo

7、ut other speed issues instead .Partition for speedApplication partitioningdo what where? - stop and think - work smarter not harderPick the right database for the job, if you have the choice.Work close to the dataMoving data to/from the client is always expensiveConsider latency as well as bandwidth

8、Use stored procedures where appropriateDo more in SQL where appropriate - get a good bookMultiple simple queries with joins in Perl may be faster.Use proprietary bulk-load, not Perl, where appropriate.Mix n Match techniques as neededexperiment and do your own benchmarks.Prepare for speedprepare() -

9、what happens in the server.Receive and parse the SQL statement into internal formGet details for all the selected tablesCheck access rights for eachGet details for all the selected fieldsCheck data types in expressionsGet details for the indices on all the fields in where/join clausesDevelop an opti

10、mised query access plan for best executionReturn a handle for all this cached informationThis can be an expensive processespecially the access plan for a complex multi-table querySome databases, like MySQL, dont cache the information but have simpler, and thus faster, plan creation.How would you do

11、it?One possible approach: Select from one table using its key field (assume both tables have an index on key) Then, loop for each row returned, and. select from the other table using its key field and the current rows value fieldBut which table to select first? To keep it simple, assume that both ta

12、bles have the same value in all rowsIf we know that t1.key=1 matches 1000 rows and t2.key=2 matches 1then we know that we should select from t2 firstbecause that way we only have to select from each table onceIf we selected from t1 firstthen wed have to select from t2 1000 times!An alternative appro

13、ach would be to select from both and merge.The best laid plansQuery optimisation is hardIntelligent high quality cost based query optimisation is really hard!Know your optimiserOracle, Informix, Sybase, DB2, SQL Server etc. all slightly different.Check what its doingUse tools to see the plans used f

14、or your queries - very helpfulHelp it alongMost big name databases have a mechanism to analyse and store the key distributions of indices to help the optimiser make good plans.Most important for tables with skewed (uneven) key distributionsBeware: keep it fresh, old key distributions might be worse

15、than noneSome also allow you to embed hints into the SQL as commentsBeware: take it easy, over hinting hinders dynamic optimisation .accessMySQLs EXPLAIN PLANTo generate a plan:EXPLAIN SELECT tt.TicketNumber, tt.TimeIn, tt.ProjectReference, tt.EstimatedShipDate, tt.ActualShipDate, tt.ClientID, tt.Se

16、rviceCodes, tt.RepetitiveID, tt.CurrentProcess, tt.CurrentDPPerson, tt.RecordVolume, tt.DPPrinted, et.COUNTRY, et_1.COUNTRY, do.CUSTNAME FROM tt, et, et AS et_1, do WHERE tt.SubmitTime IS NULL AND tt.ClientID = do.CUSTNMBR;The plan is described using results like this: TABLE TYPE POSSIBLE_KEYS KEY K

17、EY_LEN REF ROWS EXTRAet ALL PRIMARY NULL NULL NULL 74tt ref AssignedPC,ClientID,ActualPC ActualPC 15 et.EMPLOYID 52 where usedet_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1Oracles EXPLAIN PLANTo generate a plan: EXPLAIN PLAN SET STATEMENT_ID = Emp_Sal FOR SE

18、LECT ename, job, sal, dname FROM emp, dept AND NOT EXISTS (SELECT * FROM salgrade WHERE emp.sal BETWEEN losal AND hisal);That writes plan details into a table which can be queried to yield results like this: ID PAR Query Plan 0 Select Statement Cost = 69602 1 0 Nested Loops 2 1 Nested Loops 3 2 Merg

19、e Join 4 3 Sort Join 5 4 Table Access Full T3 6 3 Sort Join 7 6 Table Access Full T4 8 2 Index Unique Scan T2 9 1 Table Access Full T1Changing plans (hint hint)Most database systems provide some way to influence the execution plan - typically via hintsOracle supports a very large and complex range o

20、f hintsHints must be contained within special comments /*+ */SELECT /*+ INDEX(table1 index1) */ foo, barFROM table1 WHERE key1=1 AND key2=2 AND key3=3;MySQL has a very limited set of hintsHints can optionally be placed inside comments /*! */SELECT foo, bar FROM table1 /*! USE INDEX (key1,key2) */WHE

21、RE key1=1 AND key2=2 AND key3=3;.Respect your servers SQL cacheOptimised Access Plan etc. is cached within the serverkeyed by the exact original SQL string usedComparedo(insert $id); withdo(insert ?, undef, $id);Without placeholders, SQL string varies each timeso cached one is not reusedso time is w

22、asted creating a new access planthe new statement and access plan are added to cacheso the cache fills and other statements get pushed outon a busy system this can lead to thrashingOracle now has a way to avoid/reduce this problemit can effectively replace literal constants with placeholdersbut the

23、quality of query execution plans can suffer.Hot handlesAvoid using $dbh-do() in a speed-critical loopIts usually creating and destroying a statement handle each timeUse $sth = $dbh-prepare()and $sth-execute() insteadUsing prepare() gets a handle on the statement in the SQL cacheAvoids a round-trip t

24、o server for SQL cache check on each useFor example convert looped $dbh-do(insert ?, undef, $id) into $sth = $dbh-prepare(insert ?) before the loop and $sth-execute($id) inside the loopThis often gives a significant performance boosteven where placeholders are emulated, such as the current DBD:mysql

25、because it avoids statement handle creation overhead.Sling less for speedwhile(row = $sth-fetchrow_array) one column: 51,155 fetches per second20 columns: 24,032 fetches per second while($row = $sth-fetchrow_arrayref) one column: 58,653 fetches per second - approximately 12% faster20 columns: 49,390

26、 fetches per second - approximately 51% fasterwhile($row = shift($rowcache) | shift($rowcache=$sth-fetchall_arrayref(undef, $max_rows) one column: 348,140 fetches per second - by far the fastest!20 columns: 42,128 fetches per second - now slower than fetchrow_arrayref!Why? Balance time saved making

27、fewer calls with time spent managing more memoryDo your own benchmarks to find what works best for your situationsNotes:Tests used DBD:mysql on 100,000 rows with fields 9 chars each. $max_rows=1000;Time spent inside fetchrow_* method is approximately 0.000011s (90,000 per second)Bind those columns!C

28、omparewhile($row = $sth-fetchrow_arrayref) print “$row-0: $row-1n;with$sth-bind_columns($key, $value);while($sth-fetchrow_arrayref) print “$key: $valuen;No row assignment code!No column access code! . just magicDo more with less!Reduce the number of DBI callsThe DBI is fast - but it isnt free!Using

29、RaiseError is faster than checking return valuesand much faster than checking $DBI:err or $h-errUsing fetchall_arrayref (or selectall_arrayref) is now much fasterif using a driver extension compiled with the DBIs Driver.xst wrapper (most are) because the loop is written in C and doesnt make a method

30、 call per rowUsing fetchall_arrayref is now possible for very large result setsnew $max_rows parameter limits rows returned (and memory consumed)just add an outer loop to process the results in batches, or do it in-line:$row = shift($cache) | shift $cache=$sth-fetchall_arrayref(undef,10_000);Speedy

31、SummaryThink about the big picture firstPartitioning, choice of tools etcCheck and tune the access plans for your statementsTeach your database about any uneven key distributionsUse placeholders - where supportedEspecially for any statements that vary and will be executed oftenReplace do() in a loop

32、with prepare() and execute()Sling less data for faster row fetchingOr sling none per row by binding columns to perl variablesDo more with less by using the DBI in the most efficient wayMake fewer, better, DBI method callsOther important things to consideryour perl code, plus hardware, operating syst

33、em, and database configuration tuning-Optimizing Perl - Some TipsPerl is fast, but not that fast.Still need to take care with apparently simple things in hot codeFunction/method calls have significant overheads per call.Copying data also isnt cheap, especially long strings (allocate and copy)Perl co

34、mpiles to op codes then executes them in a loop.The more ops, the slower the code (all else being roughly equal).Try to do more with fewer ops. Especially if you can move loops into ops.Key techniques include:Caching at many levels, from common sub-expression elimination to web cachingFunctional pro

35、gramming: result = map grep data; But dont get carried away. only optimize hot code, and only if neededDont optimize for performance at the cost of maintenance. Learn perl idioms.Beware Compulsive Tuning Disorder - Gaja Krishna Vaidyanatha And remember that Premature optimization is the root of all

36、evil - Donald KnuthProfiling DBI PerformanceTime flies like an arrow(fruit flies like a banana)How fast was that?The DBI now has performance profiling built inEasiest to demonstrate by example.Overall summary:$ DBI_PROFILE=1Breakdown by statement:$ DBI_PROFILE=2 = 0.007768s / 9 = 0.000863s avg (firs

37、t 0.000022s, min 0.000022s, max 0.007444s)INSERT INTO prices (prod_id,price) VALUES(?,?) = 0.001715s / 4 = 0.000429s avg (first 0.000587s, min 0.000040s, max 0.000612s)UPDATE prices SET price=? WHERE prod_id=? = 0.001359s / 2 = 0.000680s avg (first 0.000404s, min 0.000404s, max 0.000955s)How fast wa

38、s what?Breakdown by statement and method:$ DBI_PROFILE=6 test.pl (only part of output is shown here)UPDATE prices SET price=? WHERE prod_id=? = execute = prepare =Some key pointsOnly profiles top level calls from application, not recursive calls from within DBI/DBD.Timing data is collected and merge

39、d into a $h-Profile data treeAll handles share the same data tree by default - giving one overall set of resultsThe path through the tree to where the data is merged-in can be dynamically controlledBy default $dbh method calls are usually associated with the $dbh-Statement stringDBI:Profile can be s

40、ubclassed (e.g., DBI:ProfileDumper for mod_perl)Applications can add their own timing dataMore features than I can fit on the slide.AttributionNames and PlacesAttributionTwo kinds: Handle Attributes and Method AttributesA DBI handle is a reference to a hashHandle Attributes can be read or set by acc

41、essing the hash$h-AutoCommit = 0;$autocomitting = $h-AutoCommit;Some attributes are read-only$sth-NUM_OF_FIELDS = 42; # fatal errorMany DBI methods take an attributes parameterin the form of a reference to a hash of key-value pairsThe attributes parameter is typically used to provide hints to the dr

42、iverUnrecognised attributes are ignoredThe method attributes are generally unrelated to handle attributesThe DBI-connect() method is an exceptionWhats in a name?The letter case used for attribute names is significantplays an important part in the portability of DBI scriptsUsed to signify who defined

43、 the meaning of that name and its values Case of nameHas a meaning defined by UPPER_CASEFormal standards, e.g., X/Open, SQL92 etc (portable) MixedCase DBI API (portable), underscores are not used. lower_caseDriver specific, private attributes (non-portable)Each driver has its own prefix for its priv

44、ate method and handle attributesEnsures two drivers cant define different meanings for the same attribute$sth-bind_param( 1, $value, ora_type = 97, ix_type = 42 );Handling your HandlesGet a gripLet the DBI cache your handlesSometimes its not easy to hold all your handlese.g., library code to lookup

45、values from the databaseThe prepare_cached() method gives you a client side statement handle cache:sub lookup_foo my ($dbh, $id) = _; $sth = $dbh-prepare_cached(select foo from table where id=?); return $dbh-selectrow_array($sth, $id);On later calls returns the previous cached handlefor the given st

46、atement text (and any method attributes)Can avoid the need for global statement handle variableswhich can cause problems in some situations, see laterKeep a handle on your databasesConnecting to a database can be slowOracle especially soTry to connect once and stay connected where practicalWell disc

47、uss web server issues laterThe connect_cached() method Acts like prepare_cached() but for database handlesLike prepare_cached(), its handy for library codeIt also checks the connection and automatically reconnects if its brokenWorks well combined with prepare_cached(), see following example.A connec

48、t_cached() exampleCompare and contrast.my $dbh = DBI-connect();sub lookup_foo_1 my ($id) = _; $sth = $dbh-prepare_cached(select foo from table where id=?); return $dbh-selectrow_array($sth, $id);with.sub lookup_foo_2 my ($id) = _; my $dbh = DBI-connect_cached(); $sth = $dbh-prepare_cached(select foo

49、 from table where id=?); return $dbh-selectrow_array($sth, $id);Clue: what happens if the database is restarted?.Some connect_cached() issuesBecause connect_cached() may return a new connection its important to specify all significant attributes at connect timee.g., AutoCommit, RaiseError, PrintErro

50、rSo pass the same set of attributes into all connect callsSimilar, but not quite the same as Apache:DBIDoesnt disable the disconnect() method.The caches can be accessed via the CachedKids handle attribute$dbh-CachedKids and $dbh-Driver-CachedKidsCould also be tied to implement LRU and other size-lim

51、iting caching strategiestie %$dbh-CachedKids, SomeCacheModule-Binding (Value Bondage)Placing values in holdersFirst, the simple stuff.After calling prepare() on a statement with placeholders:$sth = $dbh-prepare(“select * from table where k1=? and k2=?);Values need to be assigned (bound) to each plac

52、eholder before the database can execute the statementEither at execute, for simple cases:$sth-execute($p1, $p2);or before execute:$sth-bind_param(1, $p1);$sth-bind_param(2, $p2);$sth-execute;Then, some more detail.If $sth-execute() specifies any values, it must specify them allBound values are stick

53、y across multiple executions:$sth-bind_param(1, $p1);foreach my $p2 (p2) $sth-bind_param(2, $p2); $sth-execute;The currently bound values are retrievable using:%bound_values = % $sth-ParamValues ;New DBI feature, added in 1.28, not implemented by all drivers yetYour TYPE or mine?Sometimes the data t

54、ype for bind values needs to be specifieduse DBI qw(:sql_types);to import the type constants$sth-bind_param(1, $value, TYPE = SQL_INTEGER );to specify the INTEGER typewhich can be abbreviated to:$sth-bind_param(1, $value, SQL_INTEGER);To just distinguish numeric versus string types, try$sth-bind_par

55、am(1, $value+0); # bind as numeric value$sth-bind_param(1, $value); # bind as string valueWorks because perl values generally know if they are strings or numbers. So.Generally the +0 or isnt needed because $value has the right perl type alreadySome TYPE gotchasBind TYPE attribute is just a hintand l

56、ike all hints in the DBI, they can be ignoredMany drivers only care about the number vs string distinctionand ignore other kinds of TYPE valueFor some drivers/databases that do pay attention to the TYPEusing the wrong type can mean an index on the value field isnt used!Some drivers let you specify p

57、rivate types$sth-bind_param(1, $value, ora_type = 97 );-Error Checking & Error HandlingTo err is human,to detect, divine!The importance of error checkingErrors happen!Failure happens when you dont expect errors!database crash / network disconnectionlack of disk space for insert or select (sort space

58、 for order by)server math error on select (divide by zero after 10,000 rows)and maybe, just maybe, errors in your own code Gasp!Beat failure by expecting errors!Detect errors early to limit effectsDefensive Programming, e.g., check assumptionsThrough Programming, e.g., check for errors after fetch l

59、oops.Error checking - ways and meansError checking the hard way.$h-method or die DBI method failed: $DBI:errstr;$h-method or die DBI method failed: $DBI:errstr;$h-method or die DBI method failed: $DBI:errstr;Error checking the smart way.$h-RaiseError = 1;$h-method;$h-method;$h-method;Handling errors

60、 the smart waySetting RaiseError make the DBI call die for youFor simple applications immediate death on error is fineThe error message is usually accurate and detailed enoughBetter than the error messages some developers use!For more advanced applications greater control is needed, perhaps:Correct