Huge performance hit when using .net sql provider

Dear forum members,

when executing a simple statement as the followingexec
sp_executesql N'SELECT COUNT(*) FROM EventLogMessage
LEFT OUTER JOIN ProcessingLogMessage ON ProcessingLogMessage.EventLogMessageID = EventLogMessage.[ID]
WHERE
(EventLogMessage.Level = @level) AND
(ProcessingLogMessage.ID IS NULL) AND
(EventLogMessage.CreatedAt >= @dateTimeFrom);',N'@level int,@dateTimeFrom datetime',@level=3,@dateTimeFrom='2007-05-28 00:00:00:000'
through SQL Management Studio, it performs very well (~ 600ms). Also when executed through plain old ADO (using a simple .vbs script) it executes fast.
If I try to execute the exact same statement through .net (simple c# console application utilizing SQLCommand / SQLConnection) it takes 14 to 15 seconds!!!
See the according Profiler Trace at http://www.softwaretailor.at/SqlTrace.gif

What am I doing wrong? Why is the .net provider executing the SAME statement so much slower then ADO or Management Studio?

Thanks in advance for every reply!!

Kind regards,
Wolfgang

Is the .net app connecting to the same database on the same server instance?
Is the .net app connecting using identical credentials (user name, login)?
Is the .net app using the exact same parameters?

Wolfgang,
Have you considered adding the query plan to the trace? It's under the performance group in profiler. Perhaps a different query plan is being caused by some datatype idiosyncrancy forcing a scan for the latter case. At any rate I suspect the query plan will provide a good clue.

Also check the connection SET options as here http://www.mssqltips.com/tip.asp?tip=1304

Hi all,

thank you very much for your suggestions so far!

What I've found out is this... if I change the query slightly and add a WITH (NOLOCK) behind every table in the FROM clause, it's equally fast with the .net provider... It seems that NOLOCK tablehint is implicitly used when using ADO or the Management Studio.... irritating though...

@Adriaan: yes, connectionstrings are absolutely identical (everytime using integrated security with admin-user)

I'll dig into your other hints and tips tomorrow when I'm in the office!
Maybe you know something about this NOLOCK thing? is this default behavior? Or maybe it's something totally different?

Kind regards,
Wolfgang

Hi all!

I've followed MartinSmiths hint and SET all the options which are set when opening a connection from Management Studio.... and lo and behold... it's FAST... even without the "nolock"ing... which does not work at all... although it seemed to work last week ... (???!?!)

It has to be one of the following options, I will try to isolate which one of them it actually is:
SET ROWCOUNT 0
SET TEXTSIZE 2147483647
SET NOCOUNT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ARITHABORT ON
SET LOCK_TIMEOUT -1
SET QUERY_GOVERNOR_COST_LIMIT 0
SET DEADLOCK_PRIORITY NORMAL
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET ANSI_NULLS ON
SET ANSI_NULL_DFLT_ON ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET CURSOR_CLOSE_ON_COMMIT OFF
SET IMPLICIT_TRANSACTIONS OFF
SET QUOTED_IDENTIFIER ON;
Thanks for your help!
Wolfgang

I wouldn't be surprised if SET NOCOUNT OFF is the biggest. This should be included at the start of each and every SP anyway ...
CORRECTION
Each and every SP should have SET NOCOUNT ON - not OFF. This makes SQL not return the row count of each individual query to the client application. I would assume the setting inside the SP overrules the connection setting.

It might not be the SET options themselves. It may be that ensuring that you are using the same SET options as the query running from .NET means that you are also now using the same cached plan whereas before you were getting a different plan.
The underlying cause might be a parameter sniffing issue.

.net provider pass the dynamic sql statement to the engine , to be parsed and executed.
Why not to use stored procedure with parameters, and call it from within C#. Stored procedure gives the following benefits:
- db engine create execution plan , and re-use it.
- Avoid parsing the sql statement every time when is executed(it is compiled once).
- easy for maintainance.