Optimizing Data Types for Pushdown

When joining or filtering on time-based criteria, it is advisable to use literals instead of passing the parameters as strings. This ensures that the source system does not have to convert these parameters.

Examples:

For more information, please see Literals.

In this example, we are going to look at a scenario where a simple date comparison is negatively impacting performance:

SELECT
    "SalesOrderID"
    ,"OrderDate"
FROM
    "mssql_advworks_2019.AdventureWorks2019.Sales.SalesOrderHeader"
WHERE
    OrderDate >= '2011-05-31'
    or OrderDate >= {d '2011-05-31' } LIMIT 500;;

Upon inspecting the query plan, we can see how the original query has been translated and pushed down to the data source:

• OrderDate >= '2011-05-31' was translated to (convert(g_0.OrderDate, string) >= '2011-05-31');
• OrderDate >= {d '2011-05-31' } was translate to (g_0.OrderDate >= {ts'2011-05-31 00:00:00.0'}).

By using MSSQL Server Profiler, we can gain even more insights. This is the actual query received by MSSQL:

SELECT TOP 500 g_0."SalesOrderID" AS "c_0", g_0."OrderDate" AS "c_1" FROM "AdventureWorks2019"."Sales"."SalesOrderHeader" g_0 WHERE (convert(varchar(34), g_0."OrderDate", 21) >= N'2011-05-31' COLLATE Latin1_General_CS_AS) OR g_0."OrderDate" >= CAST('2011-05-31 00:00:00.0' AS DATETIME2)'

This portion of the WHERE clause is very efficient, g_0."OrderDate" >= CAST('2011-05-31 00:00:00.0' AS DATETIME2)'. The string is converted once into a date time value and then compared against the column. If there are any indexes on the “OrderDate” column, this comparison can take advantage of the index.

(convert(varchar(34), g_0."OrderDate", 21) >= N'2011-05-31' COLLATE Latin1_General_CS_AS) is inefficient and slow. Every row in the table must be converted from the binary storage format to a human-readable string and then compared against the date string. The conversion from date to string also means that any indexes on OrderDate are ignored. This conversion severely hurts performance and should be avoided.