Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jan 2006
    Posts
    4

    Unanswered: Crappy efficiency of the CLR stored proc.

    Hi there,

    when MsSQL srv 2005 hit the streets I was full of hope that developers have finally got an efficient tool for things like for example string manipulation with which TSQL had some substantial efficiency problems...

    After installing 2k5 server I wrote my first simple CLR stored procedure. It took three parameters (Integers) and added them and returned the result in an output parameter:

    Code:
    declare @x int
    
    set @x = 1
    
    while (@x < 100000)
    begin
    
    exec testSP
    @p1 = @p1,
    @p2 = @p2,
    @p3 = @p3 output
    
    set @x = @x + 1
    
    end
    I executed the script and waited... and waited... 10 seconds! That means that a single loop took about 100 ms! That was NOT what I had expected... Since then I've been experimenting with lots of different combinations of parameters... passing them byVal, byRef, as String, as SqlString, Integer, SqlInt32 and so on...all of this was just so damn slow I've been expecting something completely different.

    Could somebody explain it to me?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I haven't dabbled with CLR yet, but the concept appears to me to be a crutch for developers who are unable or unwilling to learn SQL. I would certainly try to find a way to program database data manipulation in SQL before resorting to CLR.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2006
    Posts
    4
    I wouldn't agree with you. First of all TSQL is one of the simplest programming languages in the world (I even doubt if one should call it "programming language", it is rather a scripting language). It is certainly much much easier than VB .net or C#. Moreover, it is not possible to do ANY data manipulation without SQL. That's not the point. TSQL has a lot of drawbacks. I'm doing a lot of computing and string manipulation (data mining and data cleaning) and TSQL can't handle it. Using SQL 2000 SRV I was taking advantage of extended stored procedures. Unfortunately writing extended stored procedures is a nightmare (I'm not a C++ fan). That is why I was so anxious when I heard that M$ is integrating .NET platform and SQL Server. Now I'm very disappointed

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    it is a poor artisan that blames his tools and as far as sql is concerned, it is true you can learn it in a afernoon but it takes a long time to truly master. I have programmed C++, FoxPro, java, VB, VB.Net and lately C# and I will tell you learning object oriented programing was far easier for me than learning how to program efficient fast set based T-SQL and this is why most developers write crappy inefficient sql and design horrendous table and index structures and end up with problems like yours.
    “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.

  5. #5
    Join Date
    Jan 2006
    Posts
    4
    I think I've been misunderstood. I wasn't talking about "horrendous tables and indexes stuctures" but something as easy as writing a string tokenizer function in TSQL. Now if you used your hard learned "efficient fast set based T-SQL" for parsing 800.000.000 varchars into tokens and waited a few days for the result, you'd understand what I was talking about. My "crappy inefficient" C# code would do it in a few hours.

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I deal with some rather large databases and I have no sql that takes days. If you do, that's what I am talking about.
    “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.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by kosimazaki
    I think I've been misunderstood. I wasn't talking about "horrendous tables and indexes stuctures" but something as easy as writing a string tokenizer function in TSQL. Now if you used your hard learned "efficient fast set based T-SQL" for parsing 800.000.000 varchars into tokens and waited a few days for the result, you'd understand what I was talking about. My "crappy inefficient" C# code would do it in a few hours.
    Some examples, perhaps?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    PowerPoint makes a lousy spreadsheet too.

    Think about your tools. Use them appropriately.

    Use Transact-SQL to extract, manage, and move the data your application will need. Use C++ to get fast bit twiddling. Don't try to use one to replace the other.

    -PatP

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Not to jump all over you...but what the heck are you talking about?

    There is very little that I can't do with TSQL, and yes it is a programming language, and it is procedural.

    http://weblogs.sqlteam.com/brettk/ar...0/10/7987.aspx

    I'd really love for you to give us something you thing can't be done is t-sql...seriously, give us anything, and I'm sure we can make the doughnuts...ummmm show you a solution
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The big question was performance, though. It may be that string manipulation is better handled by the CLR, but I need to see a test first. I say this, because I have tried parsing profiler traces, and all the grouping of string functions nested one inside the other on a million queries takes on the order of 8 times longer than just aggregating on the first 50 characters or so.

    Yes, I can manipulate strings and get a desired result purely with T-SQL. The big question is how soon can I get it via pure T-SQL, and how quick can the CLR get it for me?

    With regard to the original post, though, your expectations will have to be tempered with actual testing. It may well be that SQL Server's implementation of CLR will not be "mature" until the second release of it. It was never meant to be a magic bullet, no matter what Microsoft's marketing department said.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    CLR

    Can't Learn Relational SQL
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Jan 2006
    Posts
    4
    Quote Originally Posted by MCrowley
    Yes, I can manipulate strings and get a desired result purely with T-SQL. The big question is how soon can I get it via pure T-SQL, and how quick can the CLR get it for me?
    Finally there is someone here who understands what I'm talking about

    A very trivial test I made earlier today showed that T-SQL was 8 to 9 times slower than VB.NET when it came to string manipulation.

    TSQL code:

    Code:
    DECLARE
    	@STR		VARCHAR(20),
    	@I		INT,
    	@T1		DATETIME,
    	@T2		DATETIME
    
    SELECT
    	@STR = 'ABCDEFGHIJKLMNOPQRST',
    	@I = 0
    
    SET @T1 = GETDATE()
    
    WHILE (@I < 1000000)
    BEGIN
    
    	SET @STR = REVERSE(@STR)
    	SET @STR = LOWER(@STR)
    	SET @STR = UPPER(@STR)
    	SET @STR = REPLACE(@STR, 'A', 'Z')
    	SET @STR = REPLACE(@STR, 'Z', 'A')
    
    	SET @I = @I + 1
    
    END
    
    SET @T2 = GETDATE()
    
    SELECT DATEDIFF(MS, @T1, @T2)
    VB.NET code:

    Code:
            Dim str As String = "ABCDEFGHIJKLMNOPQRST"
            Dim i As Integer = 0
            Dim t1 As Long
            Dim t2 As Long
    
            t1 = DateTime.Now.Ticks
    
            While (i < 1000000)
    
                Dim tab As Char() = str.ToCharArray()
    
                Array.Reverse(tab)
    
                str = New String(tab)
    
                str = str.ToLower
                str = str.ToUpper
    
                str.Replace("T", "X")
    
                str.Replace("X", "T")
    
                i = i + 1
    
            End While
    
            t2 = DateTime.Now.Ticks
    
            Dim t As TimeSpan = New TimeSpan(t2 - t1)
    
            MsgBox(t.ToString)
    TSQL: 20s - 21s
    VB.NET: 2.4 - 2.5

    This example is very TSQL-optimistic because I used only TSQL built-in string functions...

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I haven't used CLR in 2005 as yet and I'm not certain how I would go about testing this but just on a hunch.... are you defo measuring the difference in string manipulations or looping between these two? What happens if you comment everything but the loop? Is there a similar difference between the two sets of code?

    i.e.:
    Code:
    DECLARE @I INT, @T1 DATETIME, @T2 DATETIME SELECT @I = 0 SET @T1 = GETDATE() WHILE (@I < 1000000) BEGIN SET @I = @I + 1 END SET @T2 = GETDATE() SELECT DATEDIFF(MS, @T1, @T2)
    Just curious to see if T-SQL turns out to be a poor looper rather than a poor string manipulator
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Pootle has a point. T-SQL is optimized for set operations, rather than iterations/looping operations. Generate a table of a 100K or a million rows or so with your string as the single column. Create a CLR based function or two to do some sort of string manipulation. Once you have the function, you can test with something like:

    Code:
    // Step one: get the table in the data cache to 
    // strip out disk read performance.
    
    select * from testtable where col1 = 'aaa'
    
    // Step two:  SQL Server function(s):
    select substring (reverse(col1), 1, 20) from testtable
    
    // Step three: CLR function(s)
    select CLRFunction (col1) from testtable
    Mix and match some of the functions. SQL Server may be faster on a single string function, but there is probably a tipping point, where three or four nested functions start to tip performance the other way.

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You assume that you need to use a loop in T-SQL, which is a very bad assumption....and your example is meaningless...

    Want to supply a realworld string manipulation that you might have to do?


    In either case, I'm sure we can't change your mind, so go in peace
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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