CODEWITHSUNDEEP
c#sql-serverado.net
user441521
user441521

Reputation: 6988

ADO.NET Parameter causes a timeout

I have a big nasty pivot query that I run from C# via ADO.NET. I use this in 2 places and in 1 place I want the data but in the other place I just want the structure (no data). So in the query I added in the where clause:

1 = @test

So in the query where I only want the table structure I set @test to 2 and that works great. However, in the query I want the data I set it to 1 but that query times out then (after 5 mins). If I pull the sql into sql server and manually set the parameter it comes back in 3 mins. If I remove the 1 = @test completely from my C# app it comes back in 3 mins just fine. If I hardcode 1 = 1 in my C# app it comes back in 3 mins just fine.

I'm also adding a date parameter in this query and that works just fine.

My question is, what in the world about the @test parameter could cause this timeout from happening?

Is there a way to see what ADO.NET is really sending over to SQL Server for my query?

Upvotes: 0

Views: 200

Answers (1)

Robert McKee
Robert McKee

Reputation: 21487

Simple answer is to modify the actual query when you need to do a structure only so that it contains your 1=@test. That way two separate query plans will be created, one for each type of query, both of which will be optimized best for the query it actually needs to do.

Upvotes: 1

Related Questions