May 17, 2018 at 12:41 pm
I have reports that are between 6 and 100 User DefinedFunctions joined together.
When I have the compatibility level set to 2012 or join lessthan 15 UDF’s together, they run just fine.
When I set the compatibility level to 2016 and join more than15 UDF’s together, the query does not come back. If I turn on Include Live QueryStatistics, it does not get past “Waiting for query plan”.
I am using the SQL 2016 Enterprise Edition.
Thanks for your help.T
May 17, 2018 at 12:55 pm
GBimberg - Thursday, May 17, 2018 12:41 PMI have reports that are between 6 and 100 User DefinedFunctions joined together.
When I have the compatibility level set to 2012 or join lessthan 15 UDF’s together, they run just fine.
When I set the compatibility level to 2016 and join more than15 UDF’s together, the query does not come back. If I turn on Include Live QueryStatistics, it does not get past “Waiting for query planâ€.
I am using the SQL 2016 Enterprise Edition.
Thanks for your help.T
Somehow, I would suggest that you reconsider your design. I really hope that the UDFs that you're joining are In-Line Table-Valued Functions, otherwise, you'll never get good performance.
In 2014 a new cardinality estimator was released, I'm not sure if the 2012 compatibility is preventing it from being used. There's no limit on the number of joins, otherwise it would throw an error almost immediately.
May 17, 2018 at 1:02 pm
Luis Cazares - Thursday, May 17, 2018 12:55 PM... I'm not sure if the 2012 compatibility is preventing it from being used.
It does prevent it, AFAIK, and that would explain the behaviour.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 17, 2018 at 1:05 pm
Luis Cazares - Thursday, May 17, 2018 12:55 PMGBimberg - Thursday, May 17, 2018 12:41 PMI have reports that are between 6 and 100 User DefinedFunctions joined together.
When I have the compatibility level set to 2012 or join lessthan 15 UDF’s together, they run just fine.
When I set the compatibility level to 2016 and join more than15 UDF’s together, the query does not come back. If I turn on Include Live QueryStatistics, it does not get past “Waiting for query planâ€.
I am using the SQL 2016 Enterprise Edition.
Thanks for your help.T
Somehow, I would suggest that you reconsider your design. I really hope that the UDFs that you're joining are In-Line Table-Valued Functions, otherwise, you'll never get good performance.
In 2014 a new cardinality estimator was released, I'm not sure if the 2012 compatibility is preventing it from being used. There's no limit on the number of joins, otherwise it would throw an error almost immediately.
They are all Table Values Functions.
May 17, 2018 at 1:20 pm
GBimberg - Thursday, May 17, 2018 1:05 PMLuis Cazares - Thursday, May 17, 2018 12:55 PMGBimberg - Thursday, May 17, 2018 12:41 PMI have reports that are between 6 and 100 User DefinedFunctions joined together.
When I have the compatibility level set to 2012 or join lessthan 15 UDF’s together, they run just fine.
When I set the compatibility level to 2016 and join more than15 UDF’s together, the query does not come back. If I turn on Include Live QueryStatistics, it does not get past “Waiting for query planâ€.
I am using the SQL 2016 Enterprise Edition.
Thanks for your help.T
Somehow, I would suggest that you reconsider your design. I really hope that the UDFs that you're joining are In-Line Table-Valued Functions, otherwise, you'll never get good performance.
In 2014 a new cardinality estimator was released, I'm not sure if the 2012 compatibility is preventing it from being used. There's no limit on the number of joins, otherwise it would throw an error almost immediately.They are all Table Values Functions.
Inline or multi-statement?
May 17, 2018 at 2:24 pm
Lynn Pettis - Thursday, May 17, 2018 1:20 PMGBimberg - Thursday, May 17, 2018 1:05 PMLuis Cazares - Thursday, May 17, 2018 12:55 PMGBimberg - Thursday, May 17, 2018 12:41 PMI have reports that are between 6 and 100 User DefinedFunctions joined together.
When I have the compatibility level set to 2012 or join lessthan 15 UDF’s together, they run just fine.
When I set the compatibility level to 2016 and join more than15 UDF’s together, the query does not come back. If I turn on Include Live QueryStatistics, it does not get past “Waiting for query planâ€.
I am using the SQL 2016 Enterprise Edition.
Thanks for your help.T
Somehow, I would suggest that you reconsider your design. I really hope that the UDFs that you're joining are In-Line Table-Valued Functions, otherwise, you'll never get good performance.
In 2014 a new cardinality estimator was released, I'm not sure if the 2012 compatibility is preventing it from being used. There's no limit on the number of joins, otherwise it would throw an error almost immediately.They are all Table Values Functions.
Inline or multi-statement?
They are multi-statement.
May 17, 2018 at 2:27 pm
GBimberg - Thursday, May 17, 2018 2:24 PMLynn Pettis - Thursday, May 17, 2018 1:20 PMGBimberg - Thursday, May 17, 2018 1:05 PMLuis Cazares - Thursday, May 17, 2018 12:55 PMGBimberg - Thursday, May 17, 2018 12:41 PMI have reports that are between 6 and 100 User DefinedFunctions joined together.
When I have the compatibility level set to 2012 or join lessthan 15 UDF’s together, they run just fine.
When I set the compatibility level to 2016 and join more than15 UDF’s together, the query does not come back. If I turn on Include Live QueryStatistics, it does not get past “Waiting for query planâ€.
I am using the SQL 2016 Enterprise Edition.
Thanks for your help.T
Somehow, I would suggest that you reconsider your design. I really hope that the UDFs that you're joining are In-Line Table-Valued Functions, otherwise, you'll never get good performance.
In 2014 a new cardinality estimator was released, I'm not sure if the 2012 compatibility is preventing it from being used. There's no limit on the number of joins, otherwise it would throw an error almost immediately.They are all Table Values Functions.
Inline or multi-statement?
They are multi-statement.
That can kill your performance. If you are joining between them, each must complete and return their table making them a real performance killer.
May 17, 2018 at 2:37 pm
Lynn Pettis - Thursday, May 17, 2018 2:27 PMThat can kill your performance. If you are joining between them, each must complete and return their table making them a real performance killer.
And here's a performance test that I keep for people using multi-statement functions.
/*
Author: Luis Cazares
Description:
This script demonstrates the performance hit on the options available for user defined functions.
It's meant to discourage the use of Scalar and Multi-statement Valued Functions.
In my tests
Results:
Scalar function is 6 times slower
Multi-statement TVF is over 200 times slower.
*/
IF OBJECT_ID(N'[dbo].[ScalarFunction]') IS NOT NULL
DROP FUNCTION [dbo].[ScalarFunction];
GO
CREATE FUNCTION [ScalarFunction](
@Value int
)
RETURNS INT
AS
BEGIN
RETURN @Value;
END
GO
IF OBJECT_ID(N'[dbo].[MultiStatementTableFunction]') IS NOT NULL
DROP FUNCTION [dbo].[MultiStatementTableFunction];
GO
CREATE FUNCTION [MultiStatementTableFunction](
@Value int
)
RETURNS
@SomeTable TABLE (ID INT)
AS
BEGIN
INSERT @SomeTable (ID)
SELECT @Value;
RETURN;
END;
GO
IF OBJECT_ID(N'[dbo].[InLineTableFunction]') IS NOT NULL
DROP FUNCTION [dbo].[InLineTableFunction]
GO
CREATE FUNCTION [InLineTableFunction](
@Value int
)
RETURNS TABLE
AS
RETURN
SELECT @Value AS ID;
GO
SELECT TOP(100000) ISNULL(CHECKSUM(NEWID()) % 100000, 0) myID
INTO PerformanceTestTable
FROM sys.all_columns, sys.all_columns b;
DECLARE @Dummy int,
@TimeStamp datetime2 = SYSDATETIME();
SELECT @Dummy = myID FROM dbo.PerformanceTestTable;
SELECT 'Dry run', DATEDIFF(MS, @TimeStamp, SYSDATETIME());
SET @TimeStamp = SYSDATETIME();
SELECT @Dummy = x.ID
FROM dbo.PerformanceTestTable
CROSS APPLY dbo.MultiStatementTableFunction(myID) x
SELECT 'MultiStatementTableFunction', DATEDIFF(MS, @TimeStamp, SYSDATETIME());
SET @TimeStamp = SYSDATETIME();
SELECT @Dummy = x.ID
FROM dbo.PerformanceTestTable
CROSS APPLY dbo.InLineTableFunction(myID) x
SELECT 'InLineTableFunction', DATEDIFF(MS, @TimeStamp, SYSDATETIME());
SET @TimeStamp = SYSDATETIME();
SELECT @Dummy = dbo.[ScalarFunction](myID)
FROM dbo.PerformanceTestTable
SELECT 'ScalarFunction', DATEDIFF(MS, @TimeStamp, SYSDATETIME());
SET @TimeStamp = SYSDATETIME();
GO
DROP TABLE dbo.PerformanceTestTable
DROP FUNCTION dbo.MultiStatementTableFunction, dbo.InLineTableFunction, dbo.ScalarFunction
May 17, 2018 at 2:56 pm
GBimberg - Thursday, May 17, 2018 2:24 PMThey are multi-statement.
You would be far better served by making them inline table valued functions instead. As Luis and Lynn have already indicated, multi-statement functions can blow performance out of the water rather quickly. Put any sizable number of rows through such functions and you've got a genuine mess on your hands. If you can post these functions, we may be able to help you convert them to inline table valued ones.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 17, 2018 at 6:51 pm
sgmunson - Thursday, May 17, 2018 2:56 PMGBimberg - Thursday, May 17, 2018 2:24 PMThey are multi-statement.
You would be far better served by making them inline table valued functions instead. As Luis and Lynn have already indicated, multi-statement functions can blow performance out of the water rather quickly. Put any sizable number of rows through such functions and you've got a genuine mess on your hands. If you can post these functions, we may be able to help you convert them to inline table valued ones.
I agree on what you're saying, but I believe that the queries should be rewritten from scratch. Going over 10 iTVF might start to become a problem when generating execution plans by the optimizer.
May 17, 2018 at 7:01 pm
sgmunson - Thursday, May 17, 2018 2:56 PMGBimberg - Thursday, May 17, 2018 2:24 PMThey are multi-statement.
You would be far better served by making them inline table valued functions instead. As Luis and Lynn have already indicated, multi-statement functions can blow performance out of the water rather quickly. Put any sizable number of rows through such functions and you've got a genuine mess on your hands. If you can post these functions, we may be able to help you convert them to inline table valued ones.
I'll go one step further.. People who are new to a declarative language, like SQL, tended to write it as if it was one of their old procedural languages. This means they tend to write badly performing code such as UDFs because it looks like subroutines, or COBOL paragraphs or whatever they originally had. This is true with natural languages too – use the syntax and word order of your original language in your the learning phase.
I'm willing to bet that with this huge number of UDFs, most of them can probably replaced with queries, subqueries or views.
Please post DDL and follow ANSI/ISO standards when asking for help.
May 18, 2018 at 5:44 am
jcelko212 32090 - Thursday, May 17, 2018 7:01 PMsgmunson - Thursday, May 17, 2018 2:56 PMGBimberg - Thursday, May 17, 2018 2:24 PMThey are multi-statement.
You would be far better served by making them inline table valued functions instead. As Luis and Lynn have already indicated, multi-statement functions can blow performance out of the water rather quickly. Put any sizable number of rows through such functions and you've got a genuine mess on your hands. If you can post these functions, we may be able to help you convert them to inline table valued ones.
I'll go one step further.. People who are new to a declarative language, like SQL, tended to write it as if it was one of their old procedural languages. This means they tend to write badly performing code such as UDFs because it looks like subroutines, or COBOL paragraphs or whatever they originally had. This is true with natural languages too – use the syntax and word order of your original language in your the learning phase.
I'm willing to bet that with this huge number of UDFs, most of them can probably replaced with queries, subqueries or views.
+1000 to THAT!
--Jeff Moden
Change is inevitable... Change for the better is not.
May 18, 2018 at 6:51 am
Luis Cazares - Thursday, May 17, 2018 6:51 PMsgmunson - Thursday, May 17, 2018 2:56 PMGBimberg - Thursday, May 17, 2018 2:24 PMThey are multi-statement.
You would be far better served by making them inline table valued functions instead. As Luis and Lynn have already indicated, multi-statement functions can blow performance out of the water rather quickly. Put any sizable number of rows through such functions and you've got a genuine mess on your hands. If you can post these functions, we may be able to help you convert them to inline table valued ones.
I agree on what you're saying, but I believe that the queries should be rewritten from scratch. Going over 10 iTVF might start to become a problem when generating execution plans by the optimizer.
Yep, figured that would be likely. However, these functions may be used elsewhere, so they might not be able to simply "go away". That said, it seems likely that even after they were all converted to ITVF's, that it may be necessary to go in an entirely new direction for the purposes of what would otherwise be joining a crap ton of them together in a single query.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 18, 2018 at 7:07 am
jcelko212 32090 - Thursday, May 17, 2018 7:01 PMsgmunson - Thursday, May 17, 2018 2:56 PMGBimberg - Thursday, May 17, 2018 2:24 PMThey are multi-statement.
You would be far better served by making them inline table valued functions instead. As Luis and Lynn have already indicated, multi-statement functions can blow performance out of the water rather quickly. Put any sizable number of rows through such functions and you've got a genuine mess on your hands. If you can post these functions, we may be able to help you convert them to inline table valued ones.
I'll go one step further.. People who are new to a declarative language, like SQL, tended to write it as if it was one of their old procedural languages. This means they tend to write badly performing code such as UDFs because it looks like subroutines, or COBOL paragraphs or whatever they originally had. This is true with natural languages too – use the syntax and word order of your original language in your the learning phase.
I'm willing to bet that with this huge number of UDFs, most of them can probably replaced with queries, subqueries or views.
Joe, enough of bashing COBOL programmers!!! You are so stuck in the past and thinking that those of us who made a living writing COBOL have absolutely NO IDEA how to write (or develop) good, high performing, scalable T-SQL code.
Just go away. You aren't helpful.
May 18, 2018 at 7:41 am
sgmunson - Thursday, May 17, 2018 2:56 PMGBimberg - Thursday, May 17, 2018 2:24 PMThey are multi-statement.
You would be far better served by making them inline table valued functions instead. As Luis and Lynn have already indicated, multi-statement functions can blow performance out of the water rather quickly. Put any sizable number of rows through such functions and you've got a genuine mess on your hands. If you can post these functions, we may be able to help you convert them to inline table valued ones.
Thanks, but there are over 4000 of them so I am going to need to do a major overhaul.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply