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

Advanced Perl DBIMaking simple things easyand difficult things possible by Tim Bunce2 2Advanced DBI tutorial Tim BunceJuly 2003Topical Topicsl Speed Speed Speed!l Handling handles and binding valuesl Error checking and error handlingl Wheels within wheelsl Transactionsl DBI for the webl Taintingl Handling LONG/BLOB datal Portabilityl Proxy power and flexible multiplexl Whats new and whats planned3 3Advanced DBI tutorial Tim BunceJuly 2003Trimmed Topics and Tipsl Lack of time prevents the inclusion of.n 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!n Non-trivial worked examplesn Handy DBIx:*and other DBI related modulesn and anything Id not finished implementing when this was written.l But I hope youll agree that theres ample information in the following 90+slidesl Tips for those attending the conference tutorial:n Doodle notes from my whitterings about the whys and wherefores on your printed copy of the slides as we go along.4 4Advanced DBI tutorial Tim BunceJuly 2003The DBI-Whats it all about?l The Perl DBI defines and implements an interface to databasesl Plug-in driver modules do the database-specific workl DBI provides default methods,functions,tools etc for driversl Not limited to the lowest common denominatorl Designed and built for speedl Powerful automatic error checking built-inl Valuable detailed call tracing/debugging built-inl Useful detailed call profiling/benchmarking built-in5 5Advanced DBI tutorial Tim BunceJuly 2003DBI ModulePerl ApplicationDBD:Other DBD:Informix DBD:OracleOracle Server Informix Server Other ServerA picture is worth?Speed Speed Speed!What helps,what doesnt,and how to measure it7 7Advanced DBI tutorial Tim BunceJuly 2003Give me speed!l DBI was designed for speed from day onel DBI method dispatcher written in hand-crafted XS/Cl Dispatch to XS driver method calls is specially optimizedl Cached attributes returned directly by DBI dispatcherl DBI overhead is generally insignificant So well talk about other speed issues instead.8 8Advanced DBI tutorial Tim BunceJuly 2003Partition for speedl Application partitioning do what where?-stop and think-work smarter not hardern Pick the right database for the job,if you have the choice.n Work close to the data Moving data to/from the client is always expensive Consider latency as well as bandwidth Use stored procedures where appropriate Do more in SQL where appropriate-get a good bookn Multiple simple queries with joins in Perl may be faster.n Use proprietary bulk-load,not Perl,where appropriate.n Mix n Match techniques as needed experiment and do your own benchmarks.9 9Advanced DBI tutorial Tim BunceJuly 2003Prepare for speedl prepare()-what happens in the server.Receive and parse the SQL statement into internal form Get details for all the selected tables Check access rights for each Get details for all the selected fields Check data types in expressions Get details for the indices on all the fields in where/join clauses Develop an optimised query access plan for best execution Return a handle for all this cached informationn This can be an expensive process especially the access plan for a complex multi-table queryn Some databases,like MySQL,dont cache the information but have simpler,and thus faster,plan creation.10 10Advanced DBI tutorial Tim BunceJuly 2003How would you do it?l 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 fieldl But which table to select first?To keep it simple,assume that both tables have the same value in all rowsl If 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 oncel If we selected from t1 firstthen wed have to select from t2 1000 times!l An alternative approach would be to select from both and merge.11 11Advanced DBI tutorial Tim BunceJuly 2003The best laid plansl Query optimisation is hard Intelligent high quality cost based query optimisation is really hard!l Know your optimiser Oracle,Informix,Sybase,DB2,SQL Server etc.all slightly different.l Check what its doing Use tools to see the plans used for your queries-very helpfull Help it alongn Most 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 distributions Beware:keep it fresh,old key distributions might be worse than nonen Some also allow you to embed hints into the SQL as comments Beware:take it easy,over hinting hinders dynamic optimisation.