<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
SQL Server 2005 Performance Tuning
»
Performance between union of table valued...
12 posts, Page 1 of 2
1
2
»»
Performance between union of table valued functions
Rate Topic
Display Mode
Topic Options
Author
Message
Ramón Ontiveros
Ramón Ontiveros
Posted Wednesday, August 13, 2008 11:33 AM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, December 12, 2012 1:18 PM
Points: 17,
Visits: 88
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
Grant Fritchey
Grant Fritchey
Posted Wednesday, August 13, 2008 12:16 PM
SSChampion
Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 13,436,
Visits: 25,281
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for
Red Gate Software
Post #552165
Ramón Ontiveros
Ramón Ontiveros
Posted Wednesday, August 13, 2008 12:57 PM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, December 12, 2012 1:18 PM
Points: 17,
Visits: 88
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
Darryll Petrancuri
Darryll Petrancuri
Posted Tuesday, January 11, 2011 12:36 PM
Grasshopper
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
Grant Fritchey
Grant Fritchey
Posted Tuesday, January 11, 2011 12:40 PM
SSChampion
Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 13,436,
Visits: 25,281
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for
Red Gate Software
Post #1046096
Darryll Petrancuri
Darryll Petrancuri
Posted Tuesday, January 11, 2011 12:54 PM
Grasshopper
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
Grant Fritchey
Grant Fritchey
Posted Tuesday, January 11, 2011 12:57 PM
SSChampion
Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 13,436,
Visits: 25,281
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for
Red Gate Software
Post #1046104
Ramón Ontiveros
Ramón Ontiveros
Posted Tuesday, January 11, 2011 1:09 PM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, December 12, 2012 1:18 PM
Points: 17,
Visits: 88
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
Grant Fritchey
Grant Fritchey
Posted Wednesday, January 12, 2011 4:09 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 13,436,
Visits: 25,281
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for
Red Gate Software
Post #1046374
TheSQLGuru
TheSQLGuru
Posted Wednesday, January 12, 2011 8:57 AM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 11:35 AM
Points: 3,672,
Visits: 5,172
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 »
12 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
