0

I have a query with 4 parameters (@p0, @p1, @p2, @p3). When I run this query in my ASP.NET Website using

var data = ent.Database.SqlQuery<QueryItem>(query, params.ToArray()).ToList();

it runs between 5 and 8 seconds, while if I take exactly the same query and I run it in SSMS (adding parameters declaration at the beginning of course):

declare @p0 as datetime = '2022-02-01'
declare @p1 as datetime = '2022-02-01'
declare @p2 as int = 0
declare @p3 as int = 10

then it runs in less than 1 second. I think I'm doing something wrong with parameters.

Any help?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Alessio Innocenzi
  • 401
  • 5
  • 19
  • 2
    Could be parameter sniffing: [Slow in the Application, Fast in SSMS?](https://www.sommarskog.se/query-plan-mysteries.html) – GarethD Feb 01 '22 at 15:23
  • 1
    @GarethD or different actual values. The T-SQL code uses `datetime`, which could translate those dates as `January 2 2022` – Panagiotis Kanavos Feb 01 '22 at 15:25
  • 1
    Without the actual query and table schema we can only guess. `datetime` is a legacy type too, with legacy behaviors that can easily cause problems. In a US locale, `2022-02-01` will be treated as Janurary 2, not February 1. The "new" types introduced in 2005 (date, datetime2, datetimeoffset) don't have this problem. – Panagiotis Kanavos Feb 01 '22 at 15:27
  • 1
    You **should not** use the `datetime` and `date` types interchangeably, nor should you use a `datetime`-typed variable to store a `date` literal: they are very different types used to represent very different things and this causes plenty of gotchas when trying to compare dates, datetime values, and especially when using ranges in queries, _especially_ with the (awful) `BETWEEN` operator. – Dai Feb 01 '22 at 15:28
  • @PanagiotisKanavos Do you have a documentation reference where SQL Server would parse an ISO date as YYYY-DD-MM by default? To my knowledge SQL Server is pretty consistent that when it gets an ISO formatted string for a date it parses it as ISO YYYY-MM-DD, the whole Jan 2 or Feb 1 comes from confusing DD/MM/YYYY and MM/DD/YYYY between the client/server and database. The go-to step for consistent date serialization across locales is ISO (YYYY-MM-DD) – Steve Py Feb 01 '22 at 20:42
  • @StevePy Start here https://www.sentryone.com/blog/aaronbertrand/backtobasics-dating-responsibly see also https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver15#supported-string-literal-formats-for-datetime – Charlieface Feb 01 '22 at 23:16
  • We need to see the actual query, the relevant tables and indexes, and please share query plans for both the slow and fast versions via https://brentozar.com/pastetheplan. SQL performance issues are far too localized to be able to give generalized advice, we need the details to answer this properly. Note that you already have 2 close votes, you are likely to get another one from someone if you don't provide the right info. I'll follow this post and if it gets closed but you update I'll vote to reopen – Charlieface Feb 01 '22 at 23:17
  • 1
    @StevePy `set dateformat dmy; select cast('2020-02-01' as datetime)`. Only YYYYMMDD or YYYY-MM-DDYHH:MM:SS are parsed the same in all languages. – David Browne - Microsoft Feb 01 '22 at 23:45
  • @StevePy that's why people *shouldn't* use the legacy `datetime` type, and why almost every `datetime`-related SO question has a comment warning against that type. The "new" types aren't even new - they were introduced in 2005, take *less* space and have *better* accuracy than `datetime`. The only reason `datetime` is used is inertia, copy-pasta, and ... hacky intervals. In AD 2022 SQL Server still has no period/interval type, so some people hack it by storing intervals as `datetime` and take advantage of `datetime`'s implicit conversion to `float` to add `datetime` values together – Panagiotis Kanavos Feb 02 '22 at 08:25
  • I checked whether it could be an issue due to datetime type, as suggested from some of you, and I found out that when executing query through entity framework, date parameters are passed as nvarchar(10) (automatic mapping made by entity framework, since I passed them as string to the `SqlQuery()` function). Forcing those parameters to be passed as date fixed my issue. – Alessio Innocenzi Feb 02 '22 at 10:56

0 Answers0