Tips for using the universal database query analyzer (10)

 

Tips for using the universal database query analyzer (10)

--- More efficient query result export function than DB2 Export

Magenfeng

(Guangdong Union Electronic Service Co., Ltd., Guangzhou 510300, China)

 

 

SummaryThis article describes in detail the "universal database query analyzer ", features added in the latest version 4.01 of the Chinese version dB query analyzer and the English version dB query analyzer-efficiently Save the query results to files. When the number of query results exceeds 50 thousand or even hundreds of millions of records, the query results can be saved quickly. This function can be combined with "text delimiters" and "whether a table header is included in the exported file" to control the format of the saved result file, which is not available in some DBMS functions.

KeywordsDB query analyzer; DB2; save query results

 

 

1 Introduction to "universal database query analyzer" 

The personal work released by Jack Ma, a local programmer in China, is the universal database query analyzer. The Chinese version is dB query analyzer and the English version is dB query analyzer.

The universal database query analyzer integrates Hash technology, linked lists, and other data structures. Using Advanced System development technology, it will be available in 2006 after four years of research, development, and testing. In the next seven years, we have been constantly improving and upgrading. So far, the latest version is 4.01. The universal database query analyzer has a workload of more than 70 thousand lines of code, making it powerful, user-friendly, and operable. It spans various database platforms and excel.

In the "New Product & tool comments" section of "programmer" 2007, "Special Recommendation" was edited and the "universal database query analyzer" was released. This article only commented on five tools, they are "Adobe Acrobat 8 Chinese version", "Thunder search 1.7 new version", "Google Desktop Search 5.0 Chinese release", "Bea release WebLogic sip server3.0", and "universal database query ". analyzer is released. The first four are products of large software companies at home and abroad, and only the "universal database query analyzer" is a personal software. Up to now, the most famous software download Website "Zhongguancun online" in China has downloaded nearly 90 thousand downloads, ranking first among the top 20 in the database category.

By November 9, 2011, Google searched for the keywords "DB query analyzer" and "DB query analyzer", with the search results around 0.8 million and 1.5 million respectively; in the baidu search keywords "DB query analyzer" and "DB query analyzer", the search results are about 0.4 million.

This article introduces the efficient export function-"Save query results to files" added to the universal database query analyzer in Chinese and English versions 4.01 ".

 

 

24.01New Version function-save results to files 

The query result is directly output to the file, corresponding to the menu item "query à display result-> Save result to file ". In this case, when the number of returned records is very large (for example, more than 50 thousand records, or tens of millions or hundreds of millions), it is best to output the results to the file; if the number of records is between 10 thousand and 20 thousand, you can use "display results in Tables" to display the efficiency. Try not to use "display results in text" to display the results.

You can also set text delimiters and output files with table headers in combination with the "text qualifier" and "header when exporting files. Note that these two settings will take effect immediately after they are changed.

The new feature of version 4.01 provides the database query analyzer with the EXPORT and bcp export functions for large database systems. It can efficiently EXPORT data tables with hundreds of millions of records.

 

 

 

3Several examples to show its high execution efficiency

The universal database query analyzer is available in both Chinese and English versions 4.01. The function of saving query results to files is added, and the execution efficiency is very high due to the adoption of highly efficient algorithms.

Result of a query without a text delimiter:

 

Figure 1 connect to the DB2 database, export more than 11 million records within 14 minutes, and generate 2 GB files

 

Figure 1 Exports files on the PC client. It took nearly 15 minutes. Of course, the network transmission of 2 GB Data from the server to the client also consumes a lot of time. Figure 2 shows that the server host is an IBM 670, 64 GB memory, 8 Physical CPUs, 16 logical CPUs, and the storage uses a disk array. The same query script is executed on such a server, exporting the same data takes nearly 6 minutes. At first glance, the efficiency of "Saving query results to files" in dB query analyzer is equivalent to 40% of that on the server. However, if you want to save the exported file to the client, do you need to include the time when the file exported by the server is transmitted to the client using FTP, what if the time is used? The fourth part of the article provides the answer.

 

Figure 2 on the server host IBM 670, more than 11 million records are exported in the last 6 minutes to generate 2 GB files

 

 

Figure 3 connect to the ms SQL Server database, export more than 2 million records in 2 minutes, and generate a MB File

 

Figure 4 connect to the ms SQL Server database, export nearly 2 million records in 14 seconds, and generate 80 Mb files

 

 

4Why is dB query analyzer more efficient than DB2 export?

In general, the files exported from the server are transmitted to the PC client. Therefore, data from the Server FTP to the client must also be considered.

It took 14 minutes and 36 seconds to export a file of about 2 GB from the Server FTP to the PC client.

 

Figure 5 it takes 14 minutes and 36 seconds for 2 GB files to be transferred from the Server FTP to the PC Client

 

Therefore, the DB2 export time and FTP Transfer Time are more than 20 minutes, while the DB query analyzer time is only more than 14 minutes, therefore, the "Save results to files" export function of DB query analyzer is more efficient.

 

 

 

 

Note:

If your computer is installed with a version earlier than "universal database query analyzer", uninstall the old version before installing the new version. Thank you!

 

 

 

Author profile:

Ma genfeng, Master, research direction: database application.

I have developed a universal database query analyzer. The Chinese version is dB query analyzer and the English version is dB query analyzer. It is designed to address cross-database platform database access.

I have also developed a file Delete absolutely to permanently delete files in windows without being recovered by other software.

These two software are free to use. They are downloaded from several major software download websites, such as Zhongguancun online, skysoftware station, huajun Software Park, Pacific computer, extraordinary software station, and green alliance.

 

 

 

 

 

DB query analyzer 5.03:
Http://xiazai.zol.com.cn/detail/27/264455.shtml

 

DB query analyzer 5.03:
Http://xiazai.zol.com.cn/detail/43/420901.shtml

 

 

 

 

Author's blog:

Csdn blog: http://blog.csdn.net/magenfeng

Sina Blog: http://blog.sina.com.cn/magenfeng

QQ space: http://user.qzone.qq.com/630414817