How Do I Find Slow SQL Statements?

All updates to this order of magnitude will certainly be slow.

First. Your records don't have to be in the same partition, second. I don't understand why so many people suggest that you build an index, the more indexes you build, the slower your update will be, because you update the records at the same time as you update the index.

Third. You have to know that the bottleneck of slow updates is in. Whether it's too much reading and writing, or not enough memory, or not fast enough, then prescribe the right medicine.

Here are two simple ways to do what might work:

First, split the table in 100w rows vertically into two and connect them with foreign key relationships, one containing small and frequently changing data such as id, foreign key, state value, time, etc., and the other containing large and infrequently changing data, such as very long strings, xml, text, etc.

In this way, the operation of this small table during update can greatly save memory and CPU overhead, and reduce disk operations.

The downside is that the query is slower.

Second: Cut the 100w row horizontally into many tables, for example, the records of each month are packed in a table, so that the number of records in each table may only be tens of thousands, and the query and update will be much faster.

The disadvantage is that the query and update are not as easy to write as the original.

1,000,000 pieces of data, a lot of data, you can establish a reasonable index, which can greatly improve the speed, and you said that the execution is slow, the stored process is not easy to say, I don't know if what you write is reasonable, unreasonable ** and reasonable **query speed is very different, from a few minutes or even longer with a dozen magic clocks.

If you are sure that your syntax cannot be optimized, and the database index is already optimal, you can consider backing up the old data of this data table, the data that is rarely used in the database, to other tables, if you want to query historical data, either in the allowed query or you can use the program to query the backup data table.

Indexing is required. Updating is also queried first and then updated, so it is necessary to improve the query efficiency first!!

Full table scan?It looks like you're going to need to upgrade your hardware, or optimize your Oracle system's parameter settings.

Another way is to break down a large table into multiple smaller tables.

2 ways.

1) Establishing a proper index can improve efficiency appropriately, which is the most important thing.

2) Write your SQL nicely, and use less in exists and so on

There are many reasons for this.

Primary key constraints. When the amount of data reaches more than one million, the speed is extremely fast when you use the primary key to search for a certain piece of data. But when you don't use the primary key to search, the speed is reduced by dozens or even hundreds of times, which is the benefit of the primary key.

The SQL statement is not optimized enough.

When querying a certain data, try not to use it if you can, check which field you want, and don't want to do the redundant, so that the data transmission can be simplified and the query speed can be much faster.

Joint query of multiple tables.

When using a large amount of data, try not to use this multi-table query, after all, it is very memory-intensive, and it is better to use other languages to loop and execute simple SQL statements such as select field from table name where condition, which can also speed up.

There are many other aspects, such as the reason for the server, the type of database table structure... I won't say more.

If it refers to page execution speed.

1. Internet speed problems.

2. SQL parameter address.

3. Whether to lock the table.