SQL应用重构：实用技巧与资源指南

5星 · 超过95%的资源需积分: 9 178 浏览量更新于2024-07-24 4 收藏 2.27MB PDF 举报

SQL应用重构是一种关键的编程实践，它涉及改进现有的SQL代码库，以提高其可读性、可维护性和性能，而无需改变其外部行为。在IT行业中，随着数据库系统的广泛应用，对SQL的理解和优化变得至关重要。重构SQL应用程序有助于降低维护成本，提升团队协作效率，并确保长期的数据处理能力。在这个主题下，O'Reilly出版社提供了多本与SQL相关的经典著作，如《The Art of SQL》、《Learning SQL》、《Making Things Happen》和《SQL in a Nutshell》，它们深入浅出地介绍了SQL语言的基础知识、高级技巧以及最佳实践。《SQL Pocket Guide》则是一个便携式的参考工具，适合开发者在日常工作中随时查阅。 oreilly.com不仅是这些书籍的完整目录，还是一个集新闻、活动、文章、博客、样章和代码示例为一体的综合资源平台。对于那些关注开放和新兴技术的开发者，oreilly.net是不可或缺的门户，涵盖了新的开发平台、编程语言和操作系统等内容。此外，O'Reilly还举办各类会议和研讨会，汇集创新者分享最新的工具和技术，帮助IT专业人士提升技能。例如，访问conferences.oreilly.com可以查看即将到来的活动，提供了一个交流和学习的平台。对于需要即时解答问题的专业人士，Safari Bookshelf（safari.oreilly.com）是一个在线参考图书馆，包含超过1000本编程和IT专业书籍。通过这个平台，用户可以进行快速搜索，找到解决实际工作中的复杂问题的答案，甚至可以在电子书上进行全文阅读或翻阅。 SQL应用重构是一个持续学习和实践的过程，通过利用O'Reilly提供的丰富资源，无论是初学者还是经验丰富的开发者都能不断优化他们的SQL技能，从而在日益复杂的IT环境中保持竞争力。

PREFACE xv

Every project needs a coordinator, and Mary Treseler, my editor, played this role on the

O’Reilly side. Mary selected a very fine team of reviewers, several of them authors. First

among them was Brand Hunt, who was the development editor for this book. My hearty

thanks go to Brand, who helped me give this book its final shape, but also to Dwayne

King, particularly for his attention both to prose and to code samples. David Noor, Roy

Owens, and Michael Blaha were also very helpful. I also want to thank two expert long-

time friends, Philippe Bertolino and Cyril Thankappan, who carefully reviewed my first

drafts as well.

Besides correcting some mistakes, all of these reviewers contributed remarks or clarifica-

tions that found their way into the final product, and made it better.

When the work is over for the author and the reviewers, it just starts for many O’Reilly

people: under the leadership of the production editor, copyediting, book designing, cover

designing, turning my lousy figures into something more compatible with the O’Reilly

standards, indexing—all of these tasks helped to give this book its final appearance. All of

my most sincere thanks to Rachel Monaghan, Audrey Doyle, Mark Paglietti, Karen

Montgomery, Marcia Friedman, Rob Romano, and Lucie Haskins.

2 CHAPTER ONE

Such behavior was the conventional wisdom for most of the 1980s, when SQL became

accepted as the language of choice for accessing corporate data. You can still find many peo-

ple who seem to think that the best, if not the only, way to improve database performance is

either to tweak a number of preferably obscure database parameters or to upgrade the hard-

ware. At a more advanced level, you can track full scans of big tables, and add indexes so as to

eliminate them. At an even more advanced level, you can try to tune SQL statements and

rewrite them so as to optimize their execution plan. Or you can reconsider the whole process.

This book focuses on the last three options, and explores various ways to achieve perfor-

mance improvements that are sometimes spectacular, independent of database parameter

tuning or hardware upgrades.

Before trying to define how you can confidently assess whether a particular piece of code

would benefit from refactoring, let’s take a simple but not too trivial example to illustrate

the difference between refactoring and tuning. The following example is artificial, but

inspired by some real-life cases.

WARNING

The tests in this book were carried out on different machines, usually with

out-of-the-box installations, and although the same program was used to

generate data in the three databases used—MySQL, Oracle, and SQL

Server—which was more convenient than transferring the data, the use of

random numbers resulted in identical global volumes but different data

sets with very different numbers of rows to process. Time comparisons are

therefore meaningless among the different products. What is meaningful,

however, is the relative difference between the programs for one product,

as well as the overall patterns.

A Simple Example

Suppose you have a number of “areas,” whatever that means, to which are attached

“accounts,” and suppose amounts in various currencies are associated with these accounts.

Each amount corresponds to a transaction. You want to check for one area whether any

amounts are above a given threshold for transactions that occurred in the 30 days preced-

ing a given date. This threshold depends on the currency, and it isn’t defined for all cur-

rencies. If the threshold is defined, and if the amount is above the threshold for the given

currency, you must log the transaction ID as well as the amount, converted to the local

currency as of a particular valuation date.

I generated a two-million-row transaction table for the purpose of this example, and I

used some Java

™

/JDBC code to show how different ways of coding can impact perfor-

mance. The Java code is simplistic so that anyone who knows a programming or scripting

language can understand its main line.

Let’s say the core of the application is as follows (date arithmetic in the following code

uses MySQL syntax), a program that I called FirstExample.java:

ASSESSMENT 3

1 try {

2 long txid;

3 long accountid;

4 float amount;

5 String curr;

6 float conv_amount;

8 PreparedStatement st1 = con.prepareStatement("select accountid"

9 + " from area_accounts"

10 + " where areaid = ?");

11 ResultSet rs1;

12 PreparedStatement st2 = con.prepareStatement("select txid,amount,curr"

13 + " from transactions"

14 + " where accountid=?"

15 + " and txdate >= date_sub(?, interval 30 day)"

16 + " order by txdate");

17 ResultSet rs2 = null;

18 PreparedStatement st3 = con.prepareStatement("insert into check_log(txid,"

19 + " conv_amount)"

20 + " values(?,?)");

22 st1.setInt(1, areaid);

23 rs1 = st1.executeQuery( );

24 while (rs1.next( )) {

25 accountid = rs1.getLong(1);

26 st2.setLong(1, accountid);

27 st2.setDate(2, somedate);

28 rs2 = st2.executeQuery( );

29 while (rs2.next( )) {

30 txid = rs2.getLong(1);

31 amount = rs2.getFloat(2);

32 curr = rs2.getString(3);

33 if (AboveThreshold(amount, curr)) {

34 // Convert

35 conv_amount = Convert(amount, curr, valuationdate);

36 st3.setLong(1, txid);

37 st3.setFloat(2, conv_amount);

38 dummy = st3.executeUpdate( );

39 }

40 }

41 }

42 rs1.close( );

43 st1.close( );

44 if (rs2 != null) {

45 rs2.close( );

46 }

47 st2.close( );

48 st3.close( );

49 } catch(SQLException ex){

50 System.err.println("==> SQLException: ");

51 while (ex != null) {

52 System.out.println("Message: " + ex.getMessage ( ));

53 System.out.println("SQLState: " + ex.getSQLState ( ));

54 System.out.println("ErrorCode: " + ex.getErrorCode ( ));

55 ex = ex.getNextException( );

56 System.out.println("");

57 }

58 }

剩余296页未读，继续阅读

seapexjiang

粉丝: 0
资源: 6

SQL应用重构：实用技巧与资源指南

SQL应用重构：提升数据库性能的艺术

提升SQL应用性能：重构策略与实践

优化SQL应用：重构与性能提升策略

SQL应用重构 PDF

《SQL应用重构》

table_index.sql_重构_zip_

重构SQL应用：提升数据库性能

SQL语句重构的艺术：简化复杂查询的技巧，让代码更加优雅高效

浅谈SQL重构在铁路工务信息PWMIS软件中的应用.pdf

SQL重构–使混乱不堪

最新资源