Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2004
    Posts
    7

    Unanswered: Performance problems with CLR

    I have problems with performance in a CLR stored procedure. I have a pretty simple SQL statement which i execute. When i call it in CLR it takes about 10 minutes to run! And when i call it in my SQL Server Management Studio it takes less than 1 second! A very big difference i think.

    Any suggestions what is making this big difference?
    Below I have listed how i call it in C# and how I do it in SQL Server Management Studio.

    In my C# code i call it like this:

    string sSql;

    SqlConnection connection;
    sSql = "SELECT p.fondskode, p.regnr, p.depotnr, d.kundenr, p.nomsaldo, p.nombeholdn, d.regtype, p.kursvrd, " +
    " nomudtr, pr.papirnavn, pr.valutakd, pr.kobskurs, pr.isinkode, pr.rentesats, pr.stermdto, " +
    " pr.ntermdto, pr.udloebdato, pr.boers, pr.papudsva, pr.rentetyp " +
    "FROM Papirtot_hist p LEFT JOIN Depot_hist d ON p.depotnr = d.depotnr AND p.dato = d.dato " +
    " LEFT JOIN Papreg_Hist pr ON p.fondskode = pr.fondskode AND p.dato = pr.dato " +
    "WHERE p.dato = @dato";

    connection = new SqlConnection();
    connection.ConnectionString = "Context Connection=true";
    try
    {
    connection.Open();
    {
    SqlCommand command = new SqlCommand(sSql, connection);
    command.Parameters.Add(new SqlParameter("@dato", dtDato));

    SqlDataReader reader = command.ExecuteReader();

    while (reader.Read())
    {
    //do something
    }
    reader.Close();
    }
    }
    finally
    {
    connection.Close();
    }

    -------------------------------------------------------------------------------
    In Sql Server Management Studio I call it like this:

    DECLARE @dato datetime
    set @dato = '05/25/2010'

    SELECT p.fondskode, p.regnr, p.depotnr, d.kundenr, p.nomsaldo, p.nombeholdn, d.regtype, p.kursvrd,
    nomudtr, pr.papirnavn, pr.valutakd, pr.kobskurs, pr.isinkode, pr.rentesats, pr.stermdto,
    pr.ntermdto, pr.udloebdato, pr.boers, pr.papudsva, pr.rentetyp
    FROM Papirtot_hist p LEFT JOIN Depot_hist d ON p.depotnr = d.depotnr AND p.dato = d.dato
    LEFT JOIN Papreg_Hist pr ON p.fondskode = pr.fondskode AND p.dato = pr.dato
    WHERE p.dato = @dato

    Regards Mads

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You would always expect the CLR to take substantially longer.
    Looping through readers will always be slower than a straightforward T-SQL SELECT statement. You also have not specified what //do something entails.

  3. #3
    Join Date
    Apr 2004
    Posts
    7
    I have tried to put in a SqlContext.Pipe.Send before and after the execute statement, and it is here it takes about 10 minutes. The code has not even reached the reader.read() statement yet.

  4. #4
    Join Date
    Apr 2004
    Posts
    7
    In the SqlContext.Pipe.Send I put a DateTime.Now.ToString() to measure the time.

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I am still wondering why CLR is being used here? Do you have some sophisticated string parsing or linear equations going on?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Sounds like you need to break out some of the SQL Server tools, starting with profiler. Get the statement started and completed times first.
    Are the C# and T-SQL code run from the same client?
    I admit I've only ever used a single CLR procedure (but lots of functions) so can't give much in the way of tips re the .NET side of things.

  7. #7
    Join Date
    Apr 2004
    Posts
    7
    I use CLR because I later on in the procedure are making some calculations where I in earlier programming have found CLR handy.

    But i will try look at the SQL profiler.

    I still think it is very odd with a 10 minutes difference on a query which only returns about 6000 rows.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Me too but we'll need some metrics to figure out the issue.

    Although "handy", CLR should only really be used for reasonably complex maths and string manipulation. Maybe some other stuff like accessing external resources but not much else.

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I am not sure we are all talking about the same thing. When you say "CLR", we are hearing ".NET code inside the database engine". That is, a stored procedure or function defined in a .NET language, instead of T-SQL. What you appear to have shown is a .NET program outside the database engine. Perhaps as a console application? How is the .NET code you have shown getting called?

    You should also invest in a catch block, it seems.

    In profiler, you should monitor for the Performance: Showplan All event, so you can compare the two execution plans.

    Lastly, How long does it take for the last record to be returned to SQL Server Management Studio? The first records I expect come in just a few seconds, but I am curious about the last record.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Like your last catch - good point.

    I am 74.7% certain that is CLR code - I think that connection string parameter is only valid in CLR though I estimated a 0.253 probability I am wrong.

  11. #11
    Join Date
    Apr 2004
    Posts
    7
    We are talking about the same thing. But my CLR-SP are taking some data from some tables to do some calculations and write the data in another table.

    I will look at the catch block.

    This evening i will start up the profiler, when i am alone on the database.

    I discovered one odd thing:
    I changed my "fast" sql which was run from the console from:

    DECLARE @dato datetime
    set @dato = '05/25/2010'

    SELECT field1, field 2
    FROM Papirtot_hist p LEFT JOIN Depot_hist d ON p.depotnr = d.depotnr AND p.dato = d.dato
    LEFT JOIN Papreg_Hist pr ON p.fondskode = pr.fondskode AND p.dato = pr.dato
    WHERE p.dato = @dato

    To

    SELECT field1, field 2
    FROM Papirtot_hist p LEFT JOIN Depot_hist d ON p.depotnr = d.depotnr AND p.dato = d.dato
    LEFT JOIN Papreg_Hist pr ON p.fondskode = pr.fondskode AND p.dato = pr.dato
    WHERE p.dato = '05/25/2010'

    The change was to enter the date directly in the where-clause instead of having it as a parameter. This changed the execution time of the query from <1 sec to about 12 seconds. Could this be related with the problem some how?

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Compare the plans.
    Time taken should not be used as a metric for measuring query efficiency - there are too many factors that affect that. It indicates if optimisation is required but should not be used as the main part of that optimisation.

  13. #13
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I have tried to put in a SqlContext.Pipe.Send before and after the execute statement, and it is here it takes about 10 minutes. The code has not even reached the reader.read() statement yet.
    I've never directly manipulated SqlContext.Pipe before, but I cannot figure out how this would be used to time a CLR procedure? Is there a reason to not use a regular System.Diagnostics.Stopwatch instance?

    Also, have you tried calling .Prepare() on your command ahead of time to see if that makes any difference?

    edit: n/m, I misread your last post and thought you were talking about the CLR proc having the 1 vs 12 second execution time.
    Last edited by Teddy; 05-26-10 at 11:13.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  14. #14
    Join Date
    Apr 2004
    Posts
    7
    I just used the Sql.Context.Pipe to send a timestamp to my console on different places in my code, so I could track where the time was used.

    But I have tried calling the .Prepare() statement, and now the SQL call takes about 1 second! A fantastic improvement!

    Thanks for the help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •