SQL statement tuning

2010-02-06  来源:本站原创  分类:Database  人气:201 

Often heard friends complain about doing database application performance problems, such as very low concurrency, response time is collapse, long lock wait, lock upgrades, or even deadlock, and so on. In the process of solving these problems, DBA application developers often find the database "misuse." Include too much unnecessary data returns, unnecessary and inappropriate to lock in the isolation level of abuse and misuse of the stored procedures and so on. However, with the vast database of knowledge required to fully grasp the application developers are really boring difficult. Therefore, I specially refined for application developers to help write some SQL to look forward to help database developers .

"According to our experience (by many industry experts show), In SQL Server to achieve the performance increase of 80% of the Improvement on SQL coding , not Lai Zi Yu Duiyupeizhi Huo Ji Tong performance of Tiaozheng."

- Kevin Kline, etc., Transact-SQL Programming of

"Experience has shown that 80% -90% of the performance tuning was done at the application level rather than at the database level"

- Thomas White Point, Expert One on One: Oracle of

This article will discuss the optimization-based syntax and simple simple query. Grammar-based optimization refers to not consider any of the non-grammatical factors (eg, index, table size and storage, etc.), just consider the SQL statements for the choice of words and writing the order.

More details, see the following links:

http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-1002limh/index.html?ca=drs-tp4608

相关文章
  • SQL statement tuning _SQL data type to pass parameters table index field type consistent 2010-10-27

    SQL statement tuning _SQL data type to pass parameters table index field type consistent Today in a production database, found a TOP SQL , SQL itself is very simple, but COST is very large (implementation plan to go full table scan), SQL statement is

  • SQL statement tuning - a simple query tuning 2010-04-22

    Often heard to do database application's performance problems complained to a friend, such as very low in the concurrency, it is the collapse of the Xiangyingshijian, Zhang Shijian of lock waits, lock escalation, or even a deadlock, Deng Deng. In the

  • SQL statement tuning 2010-02-06

    Often heard friends complain about doing database application performance problems, such as very low concurrency, response time is collapse, long lock wait, lock upgrades, or even deadlock, and so on. In the process of solving these problems, DBA app

  • sql statement or the use of optimization 2011-05-25

    SQL statements used in the OR after the fact, very easy to control, the first is to make a band with two OR-UNION to implement the results after using the USE_CONCAT into four UNION, and there's no way to get him to I imagine the first OR the UNION.

  • the principles of performance tuning sql statement 2010-07-03

    1, of the problem Early in the application system development, development of database data as relatively small, the query SQL statements, complex view of the preparation of other SQL statements can not see the performance advantages and disadvantage

  • Database index Oracle 10g SQL Performance Tuning - use the function index to address the role of general index of problem does not occur 2010-03-18

    Transfer from: http://www.javaeye.com/articles/2516 As the Oracle 10g SQL Performance Tuning - use the index to improve the performance of the database select statement as an example, for the test app_user table, 3 million records, the select * from

  • Reposted elsewhere - by analyzing the SQL statement execution plan optimization of SQL (summary) 2010-06-30

    By analyzing the SQL statement execution plan optimization of SQL (summary) DBA did almost 7 years, and sentiment among many. In the DBA's daily work, to adjust individual performance to a less challenging when the SQL statement of work. The key lies

  • Output parameters configuration hibernate sql statement 2010-08-09

    In J2ee applications, if the use of Hibernate framework, you can automatically create a database of entities and entity class mapping Java, Java program much easier to access and manipulate persistent objects, which significantly improve the efficien

  • By analyzing the SQL statements SQL statement execution plan optimization 2010-09-08

    By analyzing the SQL statements SQL statement execution plan optimization Routine maintenance in the database, adjust the individual performance of the SQL statement is a less challenging job. The key is how to get SQL statement execution plans and h

  • Oracle Sql Performance Tuning Summary 2010-10-03

    Oracle Sql Performance Tuning Summary (1) Choose the most efficient sequence table name ( Only rule-based optimizer in the effective ): ORACLE Parser in accordance with the order processing from right to left in the FROM clause of table ,FROM Clause

  • How to optimize the performance of SQL Server database SQL statement Posts 2010-11-08

    Recent projects need to do a period of time SQL Server performance tuning, encountered some problems, but also accumulated some experience, is to sum up, and the king shared. SQL Server performance optimization involves many aspects, such as sound sy

  • By analyzing the SQL statement SQL statement execution plan optimization 2010-09-08

    By analyzing the SQL statement SQL statement execution plan optimization Routine maintenance on the database, adjust the individual performance of poorly performing SQL statements is a challenging task. The key is how to get SQL statement execution p

  • (R) Oracle SQL performance tuning techniques large sum 2011-03-25

    Transfer: http://1zebra.iteye.com/blog/363712 (1) choose the most efficient sequence table name (only in the rule-based optimizer effectively): ORACLE parser in accordance with the order processing from right to left in the FROM clause table name, FR

  • Oracle's DBMS_PROFILER: PL / SQL Performance Tuning 2011-08-12

    By Amar Kumar Padhi <! - Content_start -> An application can always be fine-tuned for better performance with the use of better alternatives or with the new features introduced with every release of Oracle. Simply inspecting the code can bring out t

  • Skills: dynamically created SQL statement 2009-06-12

    MATT on his blog, has posted a dynamically created SQL statements Bo-wen. In the text, he referred to himself using the IN operator when a database query problems: According to the set of external incoming IN operator in the number of decisions place

  • ID generation strategies and Save () whether to issue the sql statement of: 2008-08-23

    ID generation strategies and Save () whether to issue the sql statement of: The primary key generation side of road is the uuid, so called after the completion of save, but the entity object into the session management, and will not issue insert stat

  • SQL statement used the classical 2010-02-04

    1 - Note: the word of several high-level query operations 2A: UNION operator 3UNION operator results through a combination of the other two tables (for example, TABLE1 and TABLE2) and eliminate any duplicate rows in the table derives a result table.

  • In the SQL statement optimization process, often used hint (change) 2010-02-26

    In the SQL statement optimization process, often used hint, The following are the common Oracle SQL optimization process in the "HINT" use of 30 1. / * + ALL_ROWS * / Show that the choice of the block cost-based optimization method, and the best

  • Rails detail (12) Sql statement 2010-03-24

    Active Record is how to deal with SQL, we find ways to look at the: conditions parameter, when called like this: find (: all,: conditions => ...), here: conditions parameter determines which records find method returns It is equivalent to where part

  • sql statement to update the same data, different data insert 2010-03-09

    Execute a Sql statement, the same vendor_id, inventory_item_id to update, insert new data in different merge into tscs_ems_upload_storage_t a using (select? as vendor_id, ? As inventory_item_id, ? As description,? As unit, ? As quantity,? As check_da