Create or modify temporary tables to optimize data

Completed

The system stores InMemory tables in memory, so you might find them useful for a small number of records. You can’t use InMemory tables in a query because they don’t exist on SQL Server. Records in a temporary table of the InMemory type exist as long as the object in which you create the table exists.

The following code shows an example of how to insert data into a temporary table.

    public static void main(Args _args)
    {
        SalesShippingLabelTmp       salesShippingLabelTmp;

        ttsbegin;

        salesShippingLabelTmp.Label = 1;
        salesShippingLabelTmp.Name  = 'Finance and operations apps';
        salesShippingLabelTmp.insert();

        ttscommit;

        while select salesShippingLabelTmp
            order by Label
        {
            Info(strFmt('%1 %2', salesShippingLabelTmp.Label, salesShippingLabelTmp.Name));
        }
    }

The code sample appears similar to how code for a normal table would appear. The scope for InMemory tables depends on how you declare it. If you declare it in a method, the scope is only in the method.

Note

You can declare an InMemory table at all levels, so it would be in scope for a class or a form, too.

You can link InMemory tables from one object to another with the setTempData method, thereby creating a new instance link, as follows:

        SalesShippingLabelTmp salesShippingLabelTmp2;
 
        salesShippingLabelTmp2.setTmpData(salesShippingLabelTmp);

You can use all tables as an InMemory table by using the setTmp method of salesShippingLabelTmp2.setTmp();

To use an InMemory table as a form datasource, you need to:

• Add the InMemory table as a form datasource.
• Use the setTempData to configure data to the record set for the form datasource in the Init method for the InMemory table form datasource, as follows:

    [DataSource]
    class SalesShippingLabelTmp
    {
        public void init()
        {
            super();
            salesShippingLabelTmp.setTmpData(mySalesCalcLabel.parmSalesShippingLabelTmp());
        }
    }

The code example shows how to set InMemory table data to a form datasource. When you're using the setTmpData method in a method other than Init, follow it with executeQuery on the datasource.

Create a temporary table with the TempDB table type

TempDB temporary tables are useful for larger amounts of data, and you can create them in the TempDB table in SQL Server. You can create indexes for performance optimization similar to how you’d do it for a regular table in finance and operations apps.

The use of TempDB tables is similar to using InMemory tables, though a few key differences exist, where TempDB tables:

• Require the linkPhysicalTableInstance method when you link data from one TempDB table to another, rather than the InMemory method of setTmpData.
• Can be global or according to company data.
• Support transaction scope or the transaction tracking system (TTS).
• Support indexes.
• Support joins to regular tables.

The following code shows the use of a TempDB table, which is similar to the use of an InMemory table, with linkPhysicalTableInstance used for linking data to a new instance.

    public static void main(Args _args)
    {
        SalesIdTempTable            salesIdTempTable;

        ttsbegin;

        salesIdTempTable.SalesId = 'SO-1';
        salesIdTempTable.insert();

        salesIdTempTable.SalesId = 'SO-2';
        salesIdTempTable.insert();

        ttscommit;

        while select salesIdTempTable
            order by SalesId
        {
            Info(strFmt('%1', salesIdTempTable.SalesId));
        }

        SalesIdTempTable salesIdTempTable2;
 
        salesIdTempTable2.linkPhysicalTableInstance(salesIdTempTable);
    }

You can use TempDB in the Extensible Data Security (XDS) framework to build a temporary table with allowed data for users. Then, you can update it depending on the refresh frequency.

When the user closes their session, the system removes all data in the TempDB table.