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 12»»

Performance between union of table valued functions Expand / Collapse
Author
Message
Posted Wednesday, August 13, 2008 11:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 9, 2014 5:59 PM
Points: 17, Visits: 95
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.
Post #552139
Posted Wednesday, August 13, 2008 12:16 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:38 PM
Points: 13,999, Visits: 28,378
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/

And another, just not as detailed:
http://scarydba.wordpress.com/2008/08/13/view-vs-table-valued-function-vs-multi-statement-table-valued-function/


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #552165
Posted Wednesday, August 13, 2008 12:57 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 9, 2014 5:59 PM
Points: 17, Visits: 95
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.


  Post Attachments 
main.sqlplan.txt (11 views, 323.28 KB)
Post #552203
Posted Tuesday, January 11, 2011 12:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 3:57 PM
Points: 17, Visits: 77
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



Post #1046088
Posted Tuesday, January 11, 2011 12:40 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:38 PM
Points: 13,999, Visits: 28,378
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
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1046096
Posted Tuesday, January 11, 2011 12:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 3:57 PM
Points: 17, Visits: 77
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



Post #1046102
Posted Tuesday, January 11, 2011 12:57 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:38 PM
Points: 13,999, Visits: 28,378
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
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1046104
Posted Tuesday, January 11, 2011 1:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 9, 2014 5:59 PM
Points: 17, Visits: 95
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
Post #1046114
Posted Wednesday, January 12, 2011 4:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:38 PM
Points: 13,999, Visits: 28,378
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
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1046374
Posted Wednesday, January 12, 2011 8:57 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 6:31 PM
Points: 4,435, Visits: 6,336
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 at GMail
Post #1046554
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse