Content
English Version
Recently, I saw people debating in a technical group about which method is better for writing data to a database, and it surprised me that the way a program controls writing to a database can affect the writing results. This piqued my curiosity, so I decided to conduct a test myself to see how much of an impact poorly written code could have on rapid memory table operations.In Delphi's TDataSet, there are two well-known methods for adding new records:
- INSERT
- APPEND
What is "INSERT"?
Inserts a new, empty record in the dataset.
What is "APPEND"?
Adds a new, empty record to the end of the dataset.
Some people say that INSERT is more efficient than APPEND, which is quite surprising. My previous impression was that APPEND was faster, but I don't remember how much faster it was. Now, I will write down the test results to see how much difference there really is between the two methods in terms of performance.
Testing method:
- Test time: 100,000 records
- Table structure: 3 fields (Integer, String, String)
- Record unit: milliseconds
Example using ClientDataSet
From the above figure, it can be seen that Insert takes much more time than Append.
Next is the time required for each ten thousand records:
As the data amount increases, the time required for Insert also increases. When it reaches 90,000 to 100,000 records, it takes 797 ms.
The result is obvious, Append is better.
But this result is really boring!
ClientDataSet is a type of Memory Table. If we compare it with other Memory Tables...
Speaking of Memory Tables, there are many options to choose from. Here are the competitors for this test:
- FireDAC -- TFdMemTable
- Devart -- TVirtualTable
- DataSnap -- TClientDataSet
FireDAC -- TFdMemTable
Similar to ClientDataSet, FdMemTable also spends more time on Insert than on Append.
Devart -- VirtualTable
In VirtualTable, we can also see that Insert takes longer than Append. But please note that the total difference of 32 ms is not very noticeable in terms of user experience. This means that users of VirtualTable do not need to pay too much attention to program details 😋.
Three-way showdown
The contestants have all completed their individual events, and now it's time for the showdown:
Summary
Although memory operations are very fast, careless handling can still lead to performance tragedies.
In cases of massive data insertion, using the Append method is undoubtedly the best practice.
The above conclusion is an exception for VirtualTable. 😁
The performance difference between Insert and Append is the biggest in ClientDataSet. Developers who use it should pay attention to optimizing their code.
FdMemTable, as the successor of ClientDataSet, is not good enough for performance-obsessed developers yet, but it still has room for improvement.
"DBNavigator.Insert" and "TDataSetInsert" in ActionList are both "Insert" actions and neither of them has "Append". If needed, manual modification is required. Fortunately, in the case of "end-users", it will not have too much impact.
Next time someone tells me that Insert has better performance than Append, I'll kick them out! 😊
中文版本 (2017/08/14)
最近在技術群組裡看到有人在爭論寫入資料庫的方式孰優孰劣，看到程式控制寫入資料庫的方法，居然也會影響寫入結果；這引發了我的好奇心，就自己來測試看看在飛快的記憶體表格操作，如果程式寫得不好，會有多大的影響。
Delphi 的 TDataSet 在新增上有兩個著名的操作方法：
- INSERT
- APPEND
What is "INSERT"?
Inserts a new, empty record in the dataset.
What is "APPEND"?
Adds a new, empty record to the end of the dataset.
這兩者的比較，居然有人說 INSERT 比 APPEND 的效能要來得好，真是意外，我以前的印象中，記得是 APPEND 要來得快，但已經不記得快多少，現在就來把測試結果寫下來，看這兩者到底的效能差別到底有多大。
測試方式：
- Test time：100000 Record
- Table Structure: Integer, String, String; 3 Fields
- Record unit: ms
以 ClientDataSet 為例：
由上圖可知， Insert 遠比 Append 要花更多的時間。
接著是每累加一萬筆時所需的時間：
隨著資料量越多，Insert 所需的時間就越長，當九萬至十萬筆時，就需要花上 797 ms 的時間。
結果很明顯，Append 勝出。
但這樣結果實在是太無趣了！
ClientDataSet是一種 Memory Table，如果把其它的 Memory Table 一起抓來比較的話……
說到 Memory Table 的選擇非常多，以下是本次參戰的選手：
- FireDAC -- TFdMemTable
- Devart -- TVirtualTable
- DataSnap -- TClientDataSet
FireDAC -- TFdMemTable
就像 ClientDataSet 一樣，FdMemTable 在 Insert 明顯比 Append 花更多的時間。
Devart -- VirtualTable
VirtualTable 也可以看到 Insert 也要比 Append 要久。
但請注意！32 ms 的總差距在體感上其實是差不多的！*意謂著使用 VirtualTable 的人可以不用太在意程式細節 (笑)
大亂鬥
選手們都已經完成個人賽事，接下來就要登台對決：
總結
僅管記憶體非常快，但操作不慎仍有可能造成效能悲劇。
在大量新增的場合，使用 Append 的方式絕對是最佳作法。
以上總結在 VirtualTable 是個例外。(笑)
ClientDataSet 在 Insert / Append 效能落差最大，有在使用它的人要注意手上的程式碼優化工作。
FdMemTable 作為未來 ClientDataSet 的接班人，對效能偏執狂的開發者來說還不夠好，請再繼續加油。
「DBNavigator.Insert」和 ActionList 裡的「TDataSetInsert」兩者都是【Insert】行為，而且它們都沒有【Append】，需要的話則須人工修改。所幸在【一般使用者】的場合下，不會有太大的影響。
下次誰再跟我說 Insert 效能比 Append 好就踹飛他！
