I am running a relatively simple query using SqlConnection / SqlCommand / SqlDataReader. The code looks like the below. I have this inside of a function where I pass in a date to call this function, that executes this query.
The odd thing is that the first time the function is run, it's fast. The second time, it's fast too, but on the third time it gets stuck for ~20 seconds and sometimes longer, causing a time out. In SSMS, the same query is quite quick and never lasts longer than ~1 second.
Any suggestions of what might be casuing this?
let conString = "..."
use connection = new SqlConnection(conString)
connection.Open()
let qry = " SELECT Date, Col1 FROM myTable
WHERE
Date = @myDate"
use command = new SqlCommand(qry, connection)
command.Parameters.AddWithValue("@myDate", startDate) |> ignore
use reader = command.ExecuteReader()
while reader.Read() do
Console.WriteLine(reader.GetDateTime(0).ToShortDateString())
sw.Close()
AddWithValuefor other reasons. Use strongly-typed parameters as detailed in the Addwith Value is Evil article. Your issue may be parameter sniffing. TrySELECT Date, Col1 FROM myTable WHERE Date = @myDate OPTION(RECOMPILE)and make sure stats are up-to-date (UPDATE STATISTICS mytable).