Using Offline Mode to speed up your Report Objects

Complex Queries often require a considerable amount of to be executed.

In these cases, Offline Mode may help by caching – saving – the Query result to the local computer and then loading it from the cache whenever the Offline Mode Report Object is executed again.

In general, DBxtra Queries which its Executed Time exceeds its Data Retrieval Time are a good target for using Offline Mode.

Let’s take, for example, the “Dashboard – Sales – Product – Last vs Current Year” dashboard included in the DBxtra Sample project which uses the following query:

Select Sum([Sub Query1].[Qty - Current Year]) As [Qty - Current Year],
Sum([Sub Query1].[Net Sales - Current Year]) As [Net Sales - Current Year],
Sum([Sub Query 2].[Qty - Last Year]) As [Qty - Last Year],
Sum([Sub Query 2].[Net Sales - Last Year]) As [Net Sales - Last Year],
[Sub Query1].[Product Category],
[Sub Query1].Product,
[Sub Query1].Month,
[Sub Query1].Territory,
Sum([Sub Query1].[Net Sales - Current Year] -
[Sub Query 2].[Net Sales - Last Year]) As [Net Sales Variation],
[Sub Query1].Latitude,
[Sub Query1].Longitude,
[Sub Query1].Country
From (Select Sum(tbl_Invoice_Sub.Qty) As [Qty - Current Year],
Sum(tbl_Invoice_Sub.Qty * tbl_Invoice_Sub.Price) As
[Net Sales - Current Year],
[tbl_Product Category].[Product Category],
tbl_Product.Product,
Month(tbl_Invoice_Main.[Date Invoice]) As Month,
tbl_Territory.Territory,
tbl_Country.Country,
z_country_codes.Latitude,
z_country_codes.Longitude
From ((((((tbl_Invoice_Main tbl_Invoice_Main
Inner Join tbl_Invoice_Sub tbl_Invoice_Sub On tbl_Invoice_Main.[Id Invoice]
= tbl_Invoice_Sub.[Id Invoice])
Inner Join tbl_Product tbl_Product On tbl_Product.[Id Product] =
tbl_Invoice_Sub.[Id Product])
Inner Join [tbl_Product Category] [tbl_Product Category]
On [tbl_Product Category].[Id Product Category] =
tbl_Product.[Id Product Category])
Inner Join tbl_Customer tbl_Customer On tbl_Customer.[Id Customer] =
tbl_Invoice_Main.[Id Customer])
Inner Join tbl_Country tbl_Country On tbl_Country.[Id Country] =
tbl_Customer.[Id Country])
Inner Join tbl_Territory tbl_Territory On tbl_Territory.[Id Territory] =
tbl_Country.[Id Territory])
Inner Join z_country_codes z_country_codes On tbl_Country.Country =
z_country_codes.Country
Where tbl_Invoice_Main.[Date Invoice] = '<|Current year|>'
Group By [tbl_Product Category].[Product Category],
tbl_Product.Product,
Month(tbl_Invoice_Main.[Date Invoice]),
tbl_Territory.Territory,
tbl_Country.Country,
z_country_codes.Latitude,
z_country_codes.Longitude) [Sub Query1]
Inner Join (Select Sum(tbl_Invoice_Sub.Qty) As [Qty - Last Year],
Sum(tbl_Invoice_Sub.Qty * tbl_Invoice_Sub.Price) As
[Net Sales - Last Year],
[tbl_Product Category].[Product Category],
tbl_Product.Product,
Month(tbl_Invoice_Main.[Date Invoice]) As Month,
tbl_Territory.Territory,
tbl_Country.Country,
z_country_codes.Latitude,
z_country_codes.Longitude
From ((((((tbl_Invoice_Main tbl_Invoice_Main
Inner Join tbl_Invoice_Sub tbl_Invoice_Sub On tbl_Invoice_Main.[Id Invoice]
= tbl_Invoice_Sub.[Id Invoice])
Inner Join tbl_Product tbl_Product On tbl_Product.[Id Product] =
tbl_Invoice_Sub.[Id Product])
Inner Join [tbl_Product Category] [tbl_Product Category]
On [tbl_Product Category].[Id Product Category] =
tbl_Product.[Id Product Category])
Inner Join tbl_Customer tbl_Customer On tbl_Customer.[Id Customer] =
tbl_Invoice_Main.[Id Customer])
Inner Join tbl_Country tbl_Country On tbl_Country.[Id Country] =
tbl_Customer.[Id Country])
Inner Join tbl_Territory tbl_Territory On tbl_Territory.[Id Territory] =
tbl_Country.[Id Territory])
Inner Join z_country_codes z_country_codes On tbl_Country.Country =
z_country_codes.Country
Where tbl_Invoice_Main.[Date Invoice] = '<|Last year|>'
Group By [tbl_Product Category].[Product Category],
tbl_Product.Product,
Month(tbl_Invoice_Main.[Date Invoice]),
tbl_Territory.Territory,
tbl_Country.Country,
z_country_codes.Latitude,
z_country_codes.Longitude) [Sub Query 2] On [Sub Query1].[Product Category]
= [Sub Query 2].[Product Category] And [Sub Query1].Product =
[Sub Query 2].Product And [Sub Query1].Month = [Sub Query 2].Month
And [Sub Query1].Territory = [Sub Query 2].Territory
Group By [Sub Query1].[Product Category],
[Sub Query1].Product,
[Sub Query1].Month,
[Sub Query1].Territory,
[Sub Query1].Latitude,
[Sub Query1].Longitude,
[Sub Query1].Country

Normally this Query takes somewhere between 6 – 8 seconds to run and between 3 – 4 seconds for the data to be loaded.

Using the Offline Mode feature it takes around 700 ms to run and around 250 ms for the data to be loaded

While useful in this case, you must be aware that if you are querying big data sets, or your Query runs in less than a second normally, you may actually experience worse performance using Offline mode.

Important! It is recommended that you bench mark your Report Object execution / load times to verify if it improves loading time and if it is convenient to enable Offline Mode.