Tag: Performance optimization skill

I Problem introduction & solving The JOIN performance in SQL is a long-standing problem, especially when there are a lot of tables to be joined, the performance plummets dramatically. SQL adopts the HASH partition approach to handle JOINs, which first calculates the HASH values of the join keys and then traverses and compares the records with the same HASH values from two tables. The same computation steps are necessary for each JOIN. If the data volume is small compared to the size of memory, we can load the data into memory in advance and create the association using the in-memory pointer mechanism.

1. Data preparation and environment SPL script is used to generate the data file with two columns of data. The first column ID consists of random integers of less than 2 billion, and the second column amount is random real numbers of no more than 10 million. The data record is 8 billion lines, and the original text file size generated is 169 G. With the data importing tool provided by the database, the file data is imported into Oracle’s data table topn, and the SPL composite table file topn.ctx is also generated with the file data. The test is

As we all know, the bottleneck of big data computing performance often lies in external storage (i.e., hard disk) IO because the access performance of external storage is one or two orders of magnitude slower than that of memory. Therefore, it is sometimes more important to reduce the amount of hard disk access than to reduce the amount of CPU calculation when optimizing performance. If the algorithm with less hard disk access can be used, better performance will be obtained for the same task even with the same or slightly more CPU computation. Grouping and aggregation require traversing the dataset. The same