SQL performance anomaly through .NET with parameters

I have three database queries that, as listed here, do the same thing. #1
uses two parameters, #2 uses one parameter, and #3 uses none.

1)
SqlCommand cmd = new SqlCommand("SELECT * FROM DataView WHERE Date>=@Start
AND Date<=@End", conn);
cmd.Parameters.AddWithValue("@Start", day);
cmd.Parameters.AddWithValue("@End", day);

2)
SqlCommand cmd = new SqlCommand("SELECT * FROM DataView WHERE Date>=@Start
AND Date<='"+day.ToShortDateString()+"'", conn);
cmd.Parameters.AddWithValue("@Start", day);

3)
SqlCommand cmd = new SqlCommand(
"SELECT * FROM DataView WHERE Date>='" + day.ToShortDateString() + "' AND
Date<='" + day.ToShortDateString() + "'", conn);

When I execute these, either with a SqlDataAdapter or SqlDataReader, #2 & #3
run in less than half a second but #1 consistently takes 3 seconds!!!

Running the queries directly in SQL Server Management Studio gives nearly
instantaneous returns on all three, which makes me think that the problem is
in .NET somewhere.

Can anyone offer hints as to what is bogging down in .NET or how I can work
around it to get decent performance with two parameters?

Hi,

Are you executing the SQL the first time that app is built? If so, it is
slow because the assembly is JITing.

If you execute it in the 2nd and 3rd time, does it still takes 3 seconds?

Kevin Yu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...rt/default.aspx.
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

These characteristics persist even after the application has been running and
interacting with the database, and each subsequent call to these queries
during the same application session gives the same performance.

Hi,

I tried it on my machine with the three SQL statements. They all returned
at about 0.1 second. Can you try to use SQL profiler to trace the execution
of these SQL statements to see what is actually running on the server?
Also, you can get the duration of the execution. If you don't mind, please
post the trace log as an attachment. Thank you!

Kevin Yu
Microsoft Online Community Support
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Hi,

To reveal what is actually blocking the execution of the query, we can try
to get the execution plan on the SQL server. To get the execution plan, you
can do the following in SQL Server Profiler.

1. When starting a new trace, switch to Events Selection tab.
2. Check Show all events checkbox.
3. Expand Performance, and check Showplan All and Showplan Text.
4. Do the trace again. You will see the execution plan for each query.

If you have any problem reviewing the trace log, please feel free to email
it to me. Remove 'online' from the no spam alias is my real email.

Kevin Yu
Microsoft Online Community Support
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)