Improved Performance for Lists in Version March 2013

Before time cockpit version March 2013 queries for most lists looked similar to this one for projects:

From Current In APP_Project.Include(*) Order By Current.APP_Code Select Current

From Project.Include(*) specifies that we want to load projects and all its relations. Project has a relation to Customer, and Customer has a relation to Country. So we load projects, customers and countries. Select P specifies that all columns should be loaded, so we get all columns from Project, Customer and Country. The TCQL (time cockpit query language) query looks very simple, but when the query is translated to T-SQL, the problem that too many columns are loaded becomes visible:

SELECT  T586.APP_ProjectUuid as T586_APP_ProjectUuid,
        T586.CreationVersion as T586_CreationVersion, 
        T586.LastUpdateVersion as T586_LastUpdateVersion, 
        T586.CurrentSyncSource as T586_CurrentSyncSource, 
        T586.APP_Billable  as T586_APP_Billable, 
        T586.APP_Budget  as T586_APP_Budget, 
        T586.APP_BudgetInHours  as T586_APP_BudgetInHours, 
        T586.APP_Closed  as T586_APP_Closed, 
        T586.APP_Code  as T586_APP_Code, 
        T586.APP_Description  as T586_APP_Description,
        T586.APP_EndDate  as T586_APP_EndDate, 
        T586.APP_ExternalProjectCode  as T586_APP_ExternalProjectCode, 
        T586.APP_FixedPrice  as T586_APP_FixedPrice, 
        T586.APP_HourlyRate  as T586_APP_HourlyRate, 
        T586.APP_InvoicingRules  as T586_APP_InvoicingRules, 
        T586.APP_ProjectName  as T586_APP_ProjectName, 
        T586.APP_StartDate  as T586_APP_StartDate,
        iT587.APP_CustomerUuid as iT587_APP_CustomerUuid, 
        iT587.CreationVersion as iT587_CreationVersion, 
        iT587.LastUpdateVersion as iT587_LastUpdateVersion, 
        iT587.CurrentSyncSource as iT587_CurrentSyncSource, 
        iT587.APP_Code as iT587_APP_Code , 
        iT587.APP_CommercialRegNumber as iT587_APP_CommercialRegNumber , 
        iT587.APP_CompanyName as iT587_APP_CompanyName , 
        iT587.APP_ContactInfo as iT587_APP_ContactInfo , 
        iT587.APP_Email as iT587_APP_Email , 
        iT587.APP_Fax as iT587_APP_Fax , 
        iT587.APP_HourlyRate as iT587_APP_HourlyRate , 
        iT587.APP_InvoiceFAO as iT587_APP_InvoiceFAO , 
        iT587.APP_InvoicingRules as iT587_APP_InvoicingRules , 
        iT587.APP_Phone as iT587_APP_Phone , 
        iT587.APP_Street as iT587_APP_Street , 
        iT587.APP_Town as iT587_APP_Town , 
        iT587.APP_VatID as iT587_APP_VatID , 
        iT587.APP_ZipCode as iT587_APP_ZipCode ,
        iT588.APP_CountryUuid as iT588_APP_CountryUuid, 
        iT588.CreationVersion as iT588_CreationVersion, 
        iT588.LastUpdateVersion as iT588_LastUpdateVersion, 
        iT588.CurrentSyncSource as iT588_CurrentSyncSource, 
        iT588.APP_CountryName as iT588_APP_CountryName , 
        iT588.APP_IsoCode as iT588_APP_IsoCode 
FROM    [mmimknbtfp].APP_Project T586 
        LEFT JOIN [mmimknbtfp].APP_Customer iT587 ON iT587.APP_CustomerUuid = T586.Relation_APP_Customer
        LEFT JOIN [mmimknbtfp].APP_Country iT588 ON iT588.APP_CountryUuid = iT587.Relation_APP_Country

Select New With Queries for Slimmer Lists

In the new version we decided only to select the columns we really need to display in the list. For that we use the Select New With clause in the TCQL query:

From Current In APP_Project
Order By :DisplayValue(Current.APP_Customer), Current.APP_Code
Select New With
{
    .ObjectUuid = Current.APP_ProjectUuid,
    .CustomerUuid = Current.APP_Customer.APP_CustomerUuid,
    .CustomerName = :DisplayValue(Current.APP_Customer),
    Current.APP_Code,
    Current.APP_ProjectName,
    Current.APP_StartDate,
    Current.APP_EndDate,
    Current.APP_Budget,
    Current.APP_BudgetInHours,
    Current.APP_Billable,
    Current.APP_Closed
}

The TCQL query is a bit more complex because we have to specify all columns that we need for our list. But the generated T-SQL is much simpler now. It only selects 12 columns instead of 41, and the Country table is not joined because we do not need it for our list:

SELECT  newid() as [T592_USR_ModelEntity_5509FD37_1A91_4841_A67E_86F25F59F74AUuid],
        (T592.APP_ProjectUuid) as T592_USR_ObjectUuid, 
        (iT593.APP_CustomerUuid) as T592_USR_CustomerUuid, 
        (iT593.APP_CompanyName) as T592_USR_CustomerName, 
        (T592.APP_Code) as T592_USR_Code, 
        (T592.APP_ProjectName) as T592_USR_ProjectName, 
        (T592.APP_StartDate) as T592_USR_StartDate, 
        (T592.APP_EndDate) as T592_USR_EndDate, 
        (T592.APP_Budget) as T592_USR_Budget, 
        (T592.APP_BudgetInHours) as T592_USR_BudgetInHours, 
        (T592.APP_Billable) as T592_USR_Billable, 
        (T592.APP_Closed) as T592_USR_Closed
FROM    [mmimknbtfp].APP_Project T592 
        LEFT JOIN [mmimknbtfp].APP_Customer iT593 ON iT593.APP_CustomerUuid = T592.Relation_APP_Customer
ORDER BY iT593.APP_CompanyName Asc , T592.APP_Code Asc 

When we run these two queries on SQL Azure we see that the bytes sent from client and the bytes received from server are much smaller in trial 6 (New With query) than in trial 5 (simple TCQL query).

In this case the difference is not so big because Project only has two relations, but when loading timesheets with Include(*) the generated T-SQL query will become really large and slow. Therefore we recommend to use Select New With queries for lists whenever possible, or at least only to include the required relations (e.g. From P In Project.Include('Customer') Select P).

Limitations of New With Queries

Whenever you want to use a list for relation combo boxes in forms, Select New With queries cannot be used. You can specify the default relation list for an entity in the model entity editor. Another option is to specify the list directly in the form or filter where it should be used (<RelationCell List="DefaultProjectRelationList" ... />). Please make sure that you do not use lists with Select New With queries in both cases.

Usually we ship a list and a relation list for each entity (e.g. APP_DefaultProjectList and APP_DefaultProjectRelationList). The ProjectList uses a Select New With query whereas the ProjectRelationList selects all properties and relations.

What's Changed for Export to Microsoft Excel

If you have defined you own Excel templates for export, they might not work in the new version for two reasons:

• The prefix for most columns has changed from APP_ to USR_. Select New With queries generate a new entity type on the fly that is always prefixed with USR_ even if the source entity is prefixed with APP_. Change the prefixes in your Excel template to match the new property names.
• We have removed all unnecessary properties. If you use properties that are no longer contained in the result of the query, you can either remove unavailable properties from your Excel template, or you can copy the list we ship and add all required properties to the Select New With clause. Additionally, change the default list in the model entity editor to your new list.