Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

CLR Table-Valued Function Example with Full Streaming (STVF) Expand / Collapse
Author
Message
Posted Wednesday, December 23, 2009 12:17 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 3:01 PM
Points: 342, Visits: 1,781
Comments posted to this topic are about the item CLR Table-Valued Function Example with Full Streaming (STVF)




SQL# - http://www.SQLsharp.com/
Post #838388
Posted Wednesday, December 23, 2009 8:35 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 11:24 AM
Points: 32,781, Visits: 14,942
Interesting idea. Is there a place where this makes sense or where you want to stream a result set back in your applications?

Does SSMS do this? It seems that I'll get data on some larger queries while it's still returning other data, or is that just a network/rendering delay as things are transferring?







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #838555
Posted Wednesday, December 23, 2009 10:34 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 3:01 PM
Points: 342, Visits: 1,781
Hey Steve. This is not really about streaming data back to the application; it is really about streaming the results of a TVF back to the calling query, which is typically a Stored Proc or maybe a Job Step or maybe even a SSRS query.

I really can't think of any reason to not always do this since it keeps memory usage low and performs the same as passing back the entire collection all at once. I am not sure what SSMS does, but I think the delay you are seeing is just part of SQL Server as I have also seen that delay. I think this is where the query hint of "FAST number_rows" comes in as well as the WITH NOWAIT option of RAISERROR in terms of trying to get around that delay.

Take care,
Solomon...





SQL# - http://www.SQLsharp.com/
Post #838645
Posted Wednesday, December 23, 2009 10:46 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 35,959, Visits: 30,253
...but I wanted to create TVFs so that I could more easily interact with the output by doing WHERE conditions, GROUP BYs, ORDER BYs, etc. which are not possible with Stored Procedures unless you trap the output into a Table Variable or Temp Table (which I did not want to do).


Well, not quite true. First, as you probably already know, you can interact with TVF's with WHERE, GROUP BY, ORDER BY, etc. Second, you can use the output of a Stored Procedure rather directly using OPENROWSET:

 SELECT * 
FROM OPENROWSET('SQLOLEDB',
'server=(local);trusted_connection=yes',
'set fmtonly off exec sp_who2')
WHERE SPID >= 50
ORDER BY Status ASC, CPUTime DESC

Note that on SQL Server 2005 and up, you must enable 'Ad Hoc Distributed Queries' either through sp_Configure or the "Surface Area Configuration". If necessary, that functionality can be enabled and disabled on the fly in the code when a user with the correct level of permissions uses the code while it rejects those that do not.

I was quite interested to see what wonderful things could be done that either: a) couldn't be done in regular T-SQL or b) would at least be more efficient than being done in T-SQL.


I'm not picking on the author here... it's a general statement that I bring up to anyone who may be considering the use of CLRS... Don't use the excuse of a lack of knowledge of T-SQL functionality as a justification for CLR usage. You'll frequently pay (yes, there are exceptions) for such a mistake in the form of performance and resource usage. There are very few things that can't actually be done in T-SQL and there are very few things where a CLR will actually win the performance foot race if the T-SQL is properly written for performance. Even when T-SQL does lose the performance footrace, it's many times so close to the performance of CLR's that it's just not worth the time and effort to maintain a separate code base outside of T-SQL.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #838652
Posted Wednesday, December 23, 2009 11:55 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 3:01 PM
Points: 342, Visits: 1,781
...but I wanted to create TVFs so that I could more easily interact with the output by doing WHERE conditions, GROUP BYs, ORDER BYs, etc.


Well, not quite true. First, as you probably already know, you can interact with TVF's with WHERE, GROUP BY, ORDER BY, etc.


Hi Jeff. I believe we are saying the same thing here. My point was expressing a preference for interacting with TVFs over Stored Procs. Maybe it would have been clearer if I said CLR Stored Procs. The point of what I wrote was that I feel many / most people are aware that CLR Stored Procs can send each row back as it is created but not everyone is aware of how to do that via TVFs.


which are not possible with Stored Procedures unless you trap the output into a Table Variable or Temp Table (which I did not want to do).

Second, you can use the output of a Stored Procedure rather directly using OPENROWSET:


Thank you for this information. I was not aware of using OPENROWSET like this so it is definitely good to know. Although personally, using a TVF seems like a cleaner / less clunky interface. Again, the point is that if one is already doing something in CLR, it is good to have the option of creating a Function over a Procedure.


I'm not picking on the author here... it's a general statement that I bring up to anyone who may be considering the use of CLRS... Don't use the excuse of a lack of knowledge of T-SQL functionality as a justification for CLR usage.


I generally agree with this statement and was not advocating that people use SQLCLR simply for the sake of using it or in doing something that is natively available in T-SQL. But, when a good use for SQLCLR is found it is good to not be forced into the Stored Procedure interface.


Take care,
Solomon...





SQL# - http://www.SQLsharp.com/
Post #838718
Posted Wednesday, December 23, 2009 12:44 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:12 PM
Points: 7,084, Visits: 14,685
Hi Solomon -

This is definitely an interesting solution. I had noticed that ugliness of having to wait for the whole recordset to render before you could send it downrange with CLR TVF's, so I will take a look at this one for sure next time it comes up.

Besides the speed - I'd anticipate this also reduces the memory footprint as well - have you had any exposure to this (it should be smaller since the entire recordset doesn't need to live in memory beofre it starts being sent)?


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #838735
Posted Wednesday, December 23, 2009 2:19 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 3:01 PM
Points: 342, Visits: 1,781
Besides the speed - I'd anticipate this also reduces the memory footprint as well - have you had any exposure to this (it should be smaller since the entire recordset doesn't need to live in memory beofre it starts being sent)?


Hey Matt #4 , thanks. Actually, the main benefit of using "yield return" -- or even the full IEnumerator syntax -- is in reducing the memory footprint. In fact, in my testing I did not see much of a performance gain at all. When I tried the example code shown in the article with a value of 8,000,000 -- which did work in the "standard" method and hence I could compare the timing of each -- both solutions returned in 4 seconds no matter how many times I ran it. I would have thought that there would be some performance gain but even if the end result is that performance is the same at least we have a very-low memory usage gain.


Take care,
Solomon...





SQL# - http://www.SQLsharp.com/
Post #838758
Posted Wednesday, December 23, 2009 4:13 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
C# "yield break"? Yikes!

I don't suppose there's a way to implement your example in VB.net, is there? (I don't know of any way to directly emulate yield break in VB.)


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #838800
Posted Wednesday, December 23, 2009 4:26 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Sunday, April 13, 2014 12:23 PM
Points: 3,433, Visits: 14,411
RBarryYoung (12/23/2009)
C# "yield break"? Yikes!

I don't suppose there's a way to implement your example in VB.net, is there? (I don't know of any way to directly emulate yield break in VB.)


No yield is one those C# only definition because per Microsoft there is no valid reason to define it in VB.


Kind regards,
Gift Peddie
Post #838805
Posted Wednesday, December 23, 2009 5:54 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 35,959, Visits: 30,253
Next question, please... What application would have the need for such a thing where it could not be done using T-SQL? Not trying to be a smart guy here... I really want to know where folks have used or will use this type of thing and why they couldn't do it in T-SQL.

Going back to my previous post... no, I wouldn't use a stored proc if a native TVF would do either (of course, "it depends"). I was just providing a direct method for how to use the output of an existing stored proc.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #838818
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse