Database performance tuning: in-depth

I. Introduction

This text works is the performance of the second database performance tuning. The index on an article about that performance tuning database performance tuning is the basis of (). This stresses that the in-depth understanding of single-table operation planning, is a powerful database performance tuning props.

Query results are not planning to run a variety of options, how to select the efficient operation of planning? Up dream database, database, sql database and are based on cost-based query optimization, planning to stop running on the alternative rate, select the most were planning to stop people running the smallest run. 

This part of the contents, I will follow-up of the few works to stop a detailed text description. Prior to this, we first understand the database needs to grasp how to run the plan. This version works only say that the operation involves a single table operations planning.

Dream up a database, oracle database, sql server database, no results appear to be planning to run the statement. I have the detailed planning of the operation of these three database planning, there is no essential difference between the three. Therefore, the content of this article for the three databases. Similarly, it should fit most other databases.

Depth understanding of running a single table, is to understand the basis for planning multi-table operation. Dream run over there planning the database, there will be more a part of the message. Therefore, this text works in my dream was selected to the database as an example database that runs in terms of planning ideas. After reading this article, should be able to read one of these three database tables to run planning.

Second, in-depth understanding of database operation planning

Planning a dream run up to the database occur in two ways: NO · 1 species in the appearance of the graphical method; the second appearance of the document-style approach. The second method used to stop there, and speaking. Understand the operation planning, understanding of database performance tuning towards a very important step.

From the operation plan, we did not run the score to see how to query the database and run the plan according to judge the operation of the query may be efficient, and how to stop the optimization. We will go through the following example to understand the database as part of Operation Planning.

1. There is no index filters out how to run a full table scan?

Structure, run the scenario:

Query is:

The statement of the running process, if the statement did not describe the results described by words like:

• 
  
• 1) If it is NO · 1 second to run the steps, then made the table of NO · 1 records; otherwise get the next record the current record. If the records have been scanned over, then run the step 4, otherwise run the step 2.
• 
  
• 2) determine whether the record may be filtered to meet the requirements c1 = 2, to meet the running step 3, otherwise run the step 1.
• 
  
• 3) put the record into the result set, run the step 1.
• 
  
• 4) the result set returned to the client. A total of five.

Read more »

Posted in: Database

Database performance tuning: Understanding

I. Introduction

This text works is the third on the performance of database performance tuning. Works on a text () stresses that in-depth understanding of planning to run a single table, single table operation planning is to understand the basis for planning multi-table operation.

Connect two tables, there are three running methods: 

1) nested loop join; 

2) hash connection; 

3) merge connection. 

Two tables in connection select which of the three? This depends on the index, and the connection cost. Highlights of the third article in the (paper) say that the text works, nested loop join, Part IV stresses that the text works hash connection, the fifth chapter about the works that merge the text link. Will be planned later in the sixth chapter, and EXISTS IN subquery subquery.

View the Gallery NO · 1 chapter:

Dream up a database, oracle database, sql server database, the database run planning is not fundamentally different, so the application works up to a dream text article database as an example database to stop planning, this text works we select oracle 10g database as an example.

After reading this article, should be able to read these three databases nested loop join operation planning. Another point is the stated demand: As the oracle of the stream of code is not open, and I got it running plans description of the basis, the cost of cost and the 10,053 documents the results of an end to anti push, though, as the direction from the large sense, does not appear difficult, just do start a discussion.

Second, in-depth understanding of the nested loop operation planning

Oracle database to run programs appear daily in two ways:

• 
  
• 1) autotrace on command;
• 
  
• 2) explain plan for the command;

Example application set autotrace command:

We did not score that run the "set autotrace on explain;" statement, the following query, insert, update, delete statement, there will be planning to run until the run "set autotrace off;" statement. If it is set "set autotrace on;", in addition to planning a run outside, there will be a part of the amount of case information useful visit. This highlights the text works do not involve assessment of the cost of the planning inquiry. 6 pp.

Read more »

Posted in: Database

How to find the operating system in bad SQL

Find operating system in a bad sql old topic, we have to the actual situation according to their plan. Must not be dogmatic use of this part of the tips described below. Application of this part of the SQL database statement, the group will generate operating system table, of course, increases the load on the system. Views of most people in the system some time after startup, in the middle of the night time load in less time (for example: a month) to check. Difficult to determine the specific plan should be specific.

The following is a part of my collection to find bad sql tips:

- SQL database running times and more

- Read more hard disk SQL database

- Sorted by number of SQL database

- The number of planning too much, too little number of runs, tied variables to use to write sql tricks

- Observation of the cursor

- View the current user running the SQL database & usertitle

Read more »

Posted in: Database

open-source database prices 60% lower than

according to the latest data show that a check, open-source database products industry, the average selling price lower than that of commercial copyright database 60%.

According to vnunet.com reported here, this copies the data from individual companies to explore check Forrester, senior strategist said the open-source database maker, such as EnterpriseDB, Ingres and MySQL's database software, entirely free from licensing fees, at the same time control should be less than the cost of props commercial database industry products, such as Microsoft, Oracle and IBM.

In general, open-source databases are often entrenched in low-end exploration, that is, those capacity of not more than 200G of the small database. "In the field of import and export trade database, 8% of the users can only play 30% of the software industry, the function of goods." Family planning process in an interview that "the most simple functions, not completely open-source database products industry performance there, so the purchase and application of all aspects of complex commercial database industry products, there is also a waste of time. "

Of course, the open-source database is not as omnipotent, the first of which lack a number of popular features such as security, XML support. The function of this part is a commercial database just advocated. On the other hand, the practical application of the enterprise market stylish procedures, such as Oracle and SAP's software industry goods, not compatible with open-source database, but the plan had predicted, "Within a few years, the practical application of business process will land an open database platform." In fact, the operation of open-source database business, not to their grade in the low-end industrial goods exploration, Ingres CTO Dave individual companies at the beginning of the interview was that the future will open up commercial databases do not have the function to competition stand out. Of course, planning family show that the current database software, the user first planned, industrial commodity prices are still difficulties, this is the biggest advantage of open source products industry.

Read more »

Posted in: Database

Firebird is more difficult than the MySQL database

Text works planning and Firebird and MySQL database on the different methods of operation, this part of the cause not the same as Oracle's business as hard as the acquisition of individual companies the same as InnoDB and SleepyCat acquisition Firebird. Text works with the main difference is:

Are you afraid of SomeCo buying Firebird?

Text works with planning and operational methods of Firebird's not the same, this part of the same result as the Oracle is not as difficult as the business person company acquired the same InnoDB and SleepyCat acquisition Firebird. Text works with the main difference is:

a, MySQL database and other personal copyright is owned by the company processes personal company, so when the object on a single acquisition, but Firebird's rights depends on each program's contributors, the acquisition would have are from the hands of the copyright holder to buy a lot of , trouble is.

b, another difference is that Firebird uses a single licensing model, while the MySQL database is GPL / commercial dual-licensing model, which individual companies took the trouble even bought a part of the stream of code, more people still do not results in the original Chinese Internet Development Network code on the basis of progress towards independence. c, then is the difference between an overall higher Firebird, and MySQL database is more modular, if only one can replace the InnoDB engine.

d, another point that is Firebird authorization rules require so the changes are necessary to re-contribute to the community, and PostgreSQL database, MySQL database, the rules can not request it, you do not score from a code to stop the increase of the source is not published.

Read more »

Posted in: Database

SQL database, a high level of human Posts: exquisite

Description: Copy table (only copy the framework, the source table name: a New Table Name: b)

Note: Copy table (copy the data source table name: a destination table name: b)

Note: There is a text works, author and the final response time

Description: outer join query (table name 1: a table name 2: b)

Note: schedule at least five minutes to remind

SQL database: select * from schedule wITe datediff ('minute', f the beginning of time, getdate ())> 5

Description: two related tables, delete the main table, the table has been no news of the Deputy

Description: -

Description: -

Description: one year from the various tasks to local traffic conditions visit cellphone charges (cellphone fees fixed message list of the two tables of chemical fertilizers in China Internet Development Network)

Description: Four tables difficulties associated check:

Description: get a table without the application of the smallest ID number

Read more »

Posted in: Database

how to make your SQL database run

SQL database in the application we often fall into a trap, that is too focused on the results may be correct while ignoring the emergence of different tips for the existence of performance differences between the estimates, this difference in performance in large or complex database environment (such as online transaction processing OLTP or decision support system DSS) was particularly evident in the. I invented the practice in the workers, often poor SQL database indexing from the inappropriate design of the connection is not required and can not be fully optimized wITe clause. They stopped right in the optimization, its speed has significantly increased! The following three areas I will summarize each stop:

 Introduction to the text works  1, the index is not logical design  2, the connection requirement does not fully  3, can not be optimized wITe clause  4, 1 Summary  5, Summary 2 

SQL database in the application we often fall into a trap, that is too focused on the results may be correct while ignoring the emergence of different tips for the existence of performance differences between the estimates, this difference in performance in large or complex database environment (such as online transaction processing OLTP or decision support system DSS) was particularly evident in the. I invented the practice in the workers, often poor SQL database indexing from the inappropriate design of the connection is not required and can not be fully optimized wITe clause. They stopped right in the optimization, its speed has significantly increased! The following three areas I will summarize each stop:

More difficult to visually explain all the examples in the SQL database running time is tested, not more than one second of all that is (<1 second).

Test Environment -

Host: HP LH II

Frequency: 330MHZ

Memory: 128 MB

Operating System: Operserver5.0.4

Database: Sybase11.0.3

First, the index is not logical design

Example: the table record with 620,000 lines, Look in the index is not the same, the following operation of several SQL databases:

1. The date on the construction of a non-clustered indexes were

Planning:

date on a large number of repeat value, the non-clustered index, the data is physically stored in data random page, look at the range, the need to run a table scan to find all the lines within this range.

2. The date on a cluster index

Planning:

In the clustered index, the data in physical order in the data page of press, and repeated value are arranged together, Yin Er Chazhao O'clock in the area, not Cheng Ji Xian Zhao Dao that range from points in and to that extent 只 scan data page avoid large-scale scanning, increase the query speed.

3. Socate, date, amount, on a combination of the index

Planning:

This is not a very logical combination of the index, because of its leading column is socate, NO · 1 and the second SQL database does not reference socate, so do not use the index; third SQL database applications the socate, and references All columns are included in the combination of the index, an index covering the formation, so it is unusually fast pace.

4. The date, socate, amount on the combination of the index

Planning:

This is a logical combination of the index. It will date as the leading column, so no score for each SQL database using the index, and NO · 1 and the third formed the SQL database indexing coverage, and thus achieve the optimal performance.

5. Summary:

By default, the index established under the non-clustered index, but sometimes it does not do the best; index logical design of the establishment of various queries in the planning and Yuce on. In general:

①. There are a lot of repeat value, and often the scope of inquiry (in with,>, <,>=,< =) and order by, assemble by addition of the column, the index may be planning to establish cluster;

②. Often simultaneous access to multiple columns, and each column contain a duplicate value may be planning to establish combination index;

③. Combined Index to form as far as possible the key to query the index coverage, the leading column is the most frequently identified column.

Page 1:

Page 2:

Page 3:

Page 4:

Page 5:

A total of five.

Read more »

Posted in: Database

Porn Can be Found by Computer Forensics, Even if You Delete it and Format Hard Drive

No matter how hard you try to get rid of porn from your hard drive of computer forensics is yet to come! This can cost you your job, family and see even go to prison for a long, long time ...

Yep, you read right! Computer Forensics can now easily find traces of porn that you have downloaded! It might take them some time, but they can every piece of porn that you downloaded or uploaded from CDs on your computer and use it as evidence against you in court! As you know, porn is highly illegal in some countries, especially things such as child pornography, bestiality and other stuff that sounds pretty discusting but I am quite sure that some people out there, run out of that stuff ...

All these things can and will you go to prison if it is found on your computer through computer forensics and no matter how hard you tried to get rid of porn from your computer and delete porn off of your PC is still traces of files deep in Your hard drive and forensics or even an average IT may be in a few minutes ... All it!

Do you get the point ...? Its time to get rid of porn from your computer for good and there is only one known way, how can actually delete porn no matter how it is distributed on your hard drive and clean, all porn fragments and sub-categories. You can download it and test it for free and called Terminator Porn ... He will always delete porn and if you happen to be under suspicion by the police for illegal pornography on your computer, you are in the clear, but you better hurry up and get rid of porn from your computer right this moment with PORN TERMINATOR!

The police and the government is cracking on illegal pornography, especially child pornography, so I sugget you do something and delete it before you may go to prison! Thousands trapped in this year for child pornography and other illegal images, I suggest that you get rid of porn as soon as possible to you!

Read more »

Posted in: Database