0

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()
4
  • 2
    query-plan-mysteries. addwithvalue-is-evil Commented Dec 2, 2022 at 22:10
  • 1
    @Stu is right about addwithvalue. Also, I would close the connection at the end. I know it's disposed, but I'd try closing it anyway to see if that didn't help. Commented Dec 2, 2022 at 22:23
  • AddWithValue apparently is very evil. Replaced it with good old fashioned string concatenations and it's flying. (All company internal, so no concerns on malicious injection.) Commented Dec 2, 2022 at 22:35
  • 1
    @Eugene, string concatenation is worse than AddWithValue for other reasons. Use strongly-typed parameters as detailed in the Addwith Value is Evil article. Your issue may be parameter sniffing. Try SELECT Date, Col1 FROM myTable WHERE Date = @myDate OPTION(RECOMPILE) and make sure stats are up-to-date (UPDATE STATISTICS mytable). Commented Dec 2, 2022 at 23:24

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.