SQL Server Performance

Huge performance hit when using .net sql provider

Discussion in 'SQL Server 2005 General Developer Questions' started by nanixi77, May 28, 2008.

  1. nanixi77 New Member

    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
  2. Adriaan New Member

    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?
  3. satya Moderator

    Welcome to the forums.
    In addition to Adriaan's question, I would like to refer that always run/display execution plan from query analyzer when testing out stored procs/ad-hoc sql and ensure clustered index seek or nested loops are used. I/O or hash joins would mean spikes in CPU usage in the performance monitor(perfmon).
  4. dschaeff New Member

    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.
  5. MartinSmithh New Member

  6. nanixi77 New Member

    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
  7. nanixi77 New Member

    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
  8. Adriaan New Member

    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.
  9. MartinSmithh New Member

    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.
  10. moh_hassan20 New Member

    .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.

Share This Page