August 13, 2008 at 11:33 am
Hi 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 @temp
SELECT * 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 fine
INSERT INTO #temp
SELECT * 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.
August 13, 2008 at 12:16 pm
Are 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:
http://sqlinthewild.co.za/index.php/2008/08/12/views-or-functions/[/url]
And another, just not as detailed:
http://scarydba.wordpress.com/2008/08/13/view-vs-table-valued-function-vs-multi-statement-table-valued-function/[/url]
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 13, 2008 at 12:57 pm
All 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.
January 11, 2011 at 12:36 pm
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
January 11, 2011 at 12:40 pm
Darryll Petrancuri (1/11/2011)
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
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.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 11, 2011 at 12:54 pm
I'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_...
or
SELECT COUNT(*) FROM dbo.udf_...
The performance is reasonable. It's when I do the
INSERT 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 ITVFs
Respectfully,
Darryll
January 11, 2011 at 12:57 pm
Is 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.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 11, 2011 at 1:09 pm
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
January 12, 2011 at 4:09 am
Ramón Ontiveros (1/11/2011)
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
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.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 12, 2011 at 8:57 am
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.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 12, 2011 at 9:29 am
TheSQLGuru (1/12/2011)
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.
Proof positive that you're a good consultant. A bad one would snicker with glee and start counting the cash.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 12, 2011 at 11:44 am
Grant Fritchey (1/12/2011)
TheSQLGuru (1/12/2011)
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.Proof positive that you're a good consultant. A bad one would snicker with glee and start counting the cash.
True dat! Fortunately there is a nearly infinite array of ways in which potential clients can generate demand for my services!! 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply