I’ve recently been having some performance issues from some looks that use date range filters, such as Date is in the past 8 weeks etc.
When interrogating the SQL for this, the code generated by Looker is extremely convoluted, with multiple converts between the datetime datatype and strings and always ends up with a string. The issue with this, is this affects the cardinality estimator within SQL Server and renders any indexing on the table largely useless; after all strings are ordered differently than dates.
The LookML is effectively trying to work out the start and end date of the range, but the way it’s doing this seems a little… backwards?
For example, to get the start date of the range for in the past 8 weeks, the following SQL code is generated (I’ve stuck a SELECT there in order for you to run it):-
- (((DATEPART(dw, CONVERT(datetime, CONVERT(varchar, CURRENT_TIMESTAMP, 102), 120))
- 1) - 1 + 7) % (7)))
, CONVERT(datetime, CONVERT(varchar, CURRENT_TIMESTAMP, 102), 120))
The whole statement can be replaced with the following logic (and if you wanted minutes/hours/days/months/years/quarters etc, simply replace every instance of the Week keyword with the appropriate keyword):-
SELECT DATEADD(WEEK,-7,DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE()),0))
This works by using an anchor date: 0, which equates to ‘1900-01-01’. We then calculate the number of weeks between this date and GETDATE (or CURRENT_TIMESTAMP if preferred, or SYSDATETIME etc). We then add that number of weeks back on to the anchor date to return the start of the current week, and from there move forwards or backwards the number of weeks we want to go, in this case -7 to go eight weeks back.
The advantage of this method is that at no point is any datatype conversion required, and the datatypes entirely remain within the date realm.
I would strongly recommend trying to switch the date handling syntax to this style if possible, as it will reap serious performance benefits compared to the existing method, along with being simpler code which is easier to understand.
This will also ensure that if the login connected to SQL Server has a different DATEFIRST setting (i.e. which day is considered the first of the week) then the behaviour of week calculations will be consistent, with the existing logic, this is not the case.