InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 14:17:41 GMT20http://www.sqlservercentral.com/Forums/Topic552139-360-1.aspx[quote][b]Grant Fritchey (1/12/2011)[/b][hr][quote][b]TheSQLGuru (1/12/2011)[/b][hr]I advise my clients to avoid ALL UDFs of ALL flavors simply because there are WAY too many ways to get in trouble with them.[/quote]Proof positive that you're a good consultant. A bad one would snicker with glee and start counting the cash.[/quote]True dat! Fortunately there is a nearly infinite array of ways in which potential clients can generate demand for my services!! :-DWed, 12 Jan 2011 11:44:17 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic552139-360-1.aspx[quote][b]TheSQLGuru (1/12/2011)[/b][hr]I advise my clients to avoid ALL UDFs of ALL flavors simply because there are WAY too many ways to get in trouble with them.[/quote]Proof positive that you're a good consultant. A bad one would snicker with glee and start counting the cash.Wed, 12 Jan 2011 09:29:37 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic552139-360-1.aspxI advise my clients to avoid ALL UDFs of ALL flavors simply because there are WAY too many ways to get in trouble with them.Wed, 12 Jan 2011 08:57:53 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic552139-360-1.aspx[quote][b]Ramón Ontiveros (1/11/2011)[/b][hr]By the way, I did work around this problem replacing the ITVF with store procedures, I don't know exactly way, but talking about performance, it's better a sp. I could use a lot of nested sp and make a more DRY design. I think maybe its because in the sp I can use temporary tables that use less memory and the SQL Server can make a better execution plan. The disadvantage is that sp are a bit more dificult to write and less maintainable than ITVF's[/quote]yeah, without seeing the code for the functions or the proc, I couldn't tell you why either. But when you're trying to figure this stuff out, the answer usually can be found in the execution plans. That's where I'd go to understand why something is running slow.Wed, 12 Jan 2011 04:09:19 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic552139-360-1.aspxBy the way, I did work around this problem replacing the ITVF with store procedures, I don't know exactly way, but talking about performance, it's better a sp. I could use a lot of nested sp and make a more DRY design. I think maybe its because in the sp I can use temporary tables that use less memory and the SQL Server can make a better execution plan. The disadvantage is that sp are a bit more dificult to write and less maintainable than ITVF'sTue, 11 Jan 2011 13:09:13 GMTRamón Ontiveroshttp://www.sqlservercentral.com/Forums/Topic552139-360-1.aspxIs the slow down because of the INSERT itself?If not, I'd say check the execution plan. If it's the same as the one you get when you run the function on its own... I'm not sure what's up. You'd need to look at locking or wait stats or something. But I'll bet the exec plans are different.Tue, 11 Jan 2011 12:57:25 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic552139-360-1.aspxI'm only nesting a few levels deep. And I know it's like nesting views. And when I execute the function via:SELECT TOP 1 * FROM dbo.udf_...orSELECT COUNT(*) FROM dbo.udf_...The performance is reasonable. It's when I do theINSERT INTO <table> SELECT <column-list> FROM dbo.udf_...that the performance goes to hell well beyond what seems reasonable in comparison to if the same ITVF had been written to utilize # temporary tables that are pulled together using similar logic as what occurs using the nested ITVFsRespectfully,DarryllTue, 11 Jan 2011 12:54:56 GMTDarryll Petrancurihttp://www.sqlservercentral.com/Forums/Topic552139-360-1.aspx[quote][b]Darryll Petrancuri (1/11/2011)[/b][hr]I'm seeing a related issue in that when I try to use an ITVF as the source data for an INSERT INTO a table (using minimally logged operations), where the ITVF is at the root of a hierarchy of nested ITVFs, the performance of the INSERT INTO totally sucks. We're talking fairly large resultsets (6-12 million records).I'm scratching my head, and I've been at this for some time.Any comments or thoughts.Respectfully,Darryll[/quote]Nesting that is like nesting views. At some point the optimizer is overwhelmed and just comes up with whatever plan it can. It's a bad design approach that does lead to issues like you're experiencing.Tue, 11 Jan 2011 12:40:50 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic552139-360-1.aspxI'm seeing a related issue in that when I try to use an ITVF as the source data for an INSERT INTO a table (using minimally logged operations), where the ITVF is at the root of a hierarchy of nested ITVFs, the performance of the INSERT INTO totally sucks. We're talking fairly large resultsets (6-12 million records).I'm scratching my head, and I've been at this for some time.Any comments or thoughts.Respectfully,DarryllTue, 11 Jan 2011 12:36:35 GMTDarryll Petrancurihttp://www.sqlservercentral.com/Forums/Topic552139-360-1.aspxAll functions are inline table valued function but there is a couple of sub function in waterfall inside of each functions, (all inline table valued function), maybe this is the problem.I being using function because of the ease of work with filters of the query, I think I can use temporal table directly from the application I’m developing instead of put all in just one function.Or reading the article http://sqlinthewild.co.za/index.php/2008/08/12/views-or-functions/ I gone a try use a stored procedure too, the think is that the information is used in a Crystal Report and I never tray to use a stored procedure as a source I don’t know if is possible.If is this is helpful I attach the execution plan in the replay.Wed, 13 Aug 2008 12:57:30 GMTRamón Ontiveroshttp://www.sqlservercentral.com/Forums/Topic552139-360-1.aspxAre these multi-statement table valued functions? If so, understand that this type of function has not statistics. This means the optimizer treats them like a table that returns one row. That's OK when the number of rows returned is relatively low (depending on your data, say less than 100 rows). But when you get to larger sets of data or you start doing things like joins or unions between multiple UDF's like this, you will see a serious performance penalty.If not, can you post the execution plans for the UDF's?Either way, are you sure you need to use a UDF to return this data? Even simple table valued functions can underperform regular TSQL queries.Here's a post outline some details around this:[url=http://sqlinthewild.co.za/index.php/2008/08/12/views-or-functions/]http://sqlinthewild.co.za/index.php/2008/08/12/views-or-functions/[/url]And another, just not as detailed:[url=http://scarydba.wordpress.com/2008/08/13/view-vs-table-valued-function-vs-multi-statement-table-valued-function/]http://scarydba.wordpress.com/2008/08/13/view-vs-table-valued-function-vs-multi-statement-table-valued-function/[/url]Wed, 13 Aug 2008 12:16:06 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic552139-360-1.aspxHi I’m having a problem with a union of two table valued function, it’s very weird I don’t know if anyone some time had the same problem. I make two table valued functions that’s works fine. But the problem came when I make a third function that’s have a union of the two first one´s :SELECT * FROM dbo.estadisticas_ventas_facturas_clientes(@id_empresa,@id_sucursal,@inicio,@fin,@cliente1,@cliente2,@clasificacion,@activo,@ventas1,@ventas2,@id_vendedor)UNION SELECT * FROM dbo.estadisticas_ventas_devoluciones_clientes(@id_empresa,@id_sucursal,@inicio,@fin,@cliente1,@cliente2,@clasificacion,@activo,@ventas1,@ventas2,@id_vendedor) Then when I execute the select statement of the function, the function stay executing without giving any result. I’m end canceling the query because of the time of execution. Because of this then I try to insert the results sets of each query into a table variable. But the result was the same.INSERT INTO @tempSELECT * FROM dbo.estadisticas_ventas_facturas_clientes(@id_empresa,@id_sucursal,@inicio,@fin,@cliente1,@cliente2,@clasificacion,@activo,@ventas1,@ventas2,@id_vendedor)But if instead of that I insert the result set into a temporally table the query works fineINSERT INTO #tempSELECT * FROM dbo.estadisticas_ventas_facturas_clientes(@id_empresa,@id_sucursal,@inicio,@fin,@cliente1,@cliente2,@clasificacion,@activo,@ventas1,@ventas2,@id_vendedor)The problem is that I can’t use a temporally table inside a function.I don’t know if this is a bug or is a problem of design in my functions.Wed, 13 Aug 2008 11:33:07 GMTRamón Ontiveros