Inconsistent date conversions

ms-sql

(Jeffrey Adams) #1

We have two different projects that connect to two unrelated server/db/table structures and one of them (our heavier use project) converts date ranges to datetime2 in the generated SQL. Another project converts the same style date range filter to just datetime.

After a lot of digging with the looker team, we identified a query return time issue with the datetime2 instance was due to a precedence issue in which the looker convert side of the argument (datetime2) had a higher precedence than the field comparing it to in our table (datetime) and was causing our index to scan instead of seek. We have remedied this with a workaround that strips the 2 from datetime2 in liquid SQL to allow us to get improved performance.

While I am not sure why the convert is not generated to match whatever the field type in the database is, the problem does not replicate itself on a different project/server. On that second server, the convert (in multiple tests on different DBs/tables) is to datetime, allowing the precedence to match the DB field type and use the index efficiently.

The only difference we can note is that the servers are running slightly different SQL versions (13.04446 vs 13.04411), but other than that, don’t know what might cause this difference in convert datatype.


(Peggy Beard) #2

Thanks, Jeff! Posts here come to our support team as well as the community. :slight_smile: If anyone in the community has an idea or similar experience, we’re happy to have them chime in! I’ll reply to your direct email on this as well.


(Jeffrey Adams) #3

Came back to delete this, but figured it would be better to explain why in case anyone who viewed was looking into this…

My supervisor and I both replicated the issue above on Thursday and Friday of last week and found the same issue as stated above.

However, the same look loaded today (5 days later) generated SQL that did, in fact, convert to datetime2 instead of converting to datetime. We can’t identify any other inconsistencies between the two databases other than the SQL version they have, nor can we find any changes to our model or view definition code since we last tested. But both server/projects now generate SQL in looker that converts consistently, despite our conversion precedence issue still being there (which looker is working on a fix for! Thanks!)

But if anyone has any hunches, we’d be happy to test/follow up.