DateTime handling on the SqlServer

ServiceStack.OrmLite SQL Server
1

Hello!

This is more of a question to ensure that there are no hidden handling or conversions of datetime on the SQL Server when using typed API.

The software version at the customer uses ServiceStack v5.14.
I currently don’t know what is the SQL Server version.

The customer complains about a SQL query that is using a lot of resources. In particular, there is a WHERE clause that does that:

WHERE [Started] AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time'  >=  DATEADD(Hour,-3, getdate())

The [Started] column is of a regular datetime, null type. All our dates are stored as datetime objects and we just assume they are in UTC-0.
We mostly use the typed API for the DB calls. We have some extension methods that execute plain SQL but we are definitely not handling time zones or even call SQL functions like dateadd() or getdate().

So my question is:
Is there by accident, some logic in the OrmLite API that is trying to handle the time zone differences, for instance in a query like:

db.From<T>().Where(x => x.Started > DateTime.Now)

And might produce a query similar to above?
I’ve checked the ServiceStack.OrmLite GitHub repo but couldn’t find any logic that might do that.

2

If the SQL query is using a lot of resources that would suggest a server issue? i.e. Does the same query executed outside in the App in a query console consume the same resources?

All data type conversions happen in OrmLite Converters, here’s SQL Server’s DateTime converter:

1 Like
3

Thanks for a prompt answer!

I have currently no access to the customer’s infrastructure so I cannot investigate the performance issues deeper.
But my question on this forum was not about the potential performance problems that the query might be causing.

My assumption is - there is another software that is calling the DB. I just wanted to make sure there is no logic that might produce such query with ServiceStack.OrmLite, because it totally doesn’t fit anywhere in our C# code.
If all conversions happen in the OrmLite Converters, it answers my question and gives me sort of confidence that it’s not our software that generated this SQL query. Thanks again!

4

I don’t see how OrmLite creates that query. Some different ways to view the SQL OrmLite generates are in the Logging and Introspection docs.

1 Like
5

Great! Thanks for a hint. This might be helpful at the customer.

1 Like
6

Just a quick question here - is there a specific log name, that I can set level for, in the appsettings.json?
Something like:

  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "ServiceStack.OrmLite": "Debug",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  }
7

No, logging just controlled with a Debug Logger.

The BeforeExecFilter or PrintSql/UnPrintSql can be used in code to turn on/off viewing generated SQL.