Virtual UI or Virtual DB?

iDempiere can show data to users without actually storing these values in the database.  A developer has the option of using a Virtual UI or a Virtual DB column.  Both of these concepts allow the developer to set a field based on another set of values. What is the difference between Virtual UI or Virtual DB? Which one should be used? 

I had a case where I used Virtual UI in setting a customized “UPC” field in the C_OrderLine table.   Virtual UIs are set by using the @SQL annotation.  For example, you can use:

 @SQL=select upc from m_product where m_product_id = @M_Product_ID@ 

to return a UPC field based on the m_product_id context value. Using @SQL is the newer way of creating virtual columns. It is faster to load and easier on performance as data is loaded on the fly. 

This was all good until I was informed that the field had to be exported and must be seen in the grid.  Unfortunately, @SQL results cannot be exported nor can be seen on the grid unless the field is clicked.  In order for data to be seen in the grid and to be exported, we had to update the logic to use the old way of creating virtual columns i.e. Virtual DB.  A typical virtual DB will be written this way: 

(SELECT m. upc FROM c_orderline c JOIN m_product m on (c.M_Product_ID=p.M_Product_ID) WHERE c.C_Orderline_ID = c_orderline.C_Orderline_ID)  

Virtual DB performance is slower because all records are read at once.  

Which one should be used? Ideally, we would want to use the faster Virtual UI. However, if virtual columns need to be exported, then you would go with Virtual DB. In the end, the developer will need to provide the advantages and disadvantages of both in order for their clients to make the right decision.  Let me know if there is a better way of writing virtual columns.

For more information: