December 3, 2008 at 5:11 pm
My application has a SQL scalar function CAP_PARM that returns a value from a table using the users userid SUSER_SNAME(SUSER_SID()) as a lookup and a parameter value passed into the function. It's used as a method of providing parameters for views. e.g.
SELECT server, otherstuff
from atable
where server = dbo.cap_parm('servername')
Firstly we realised that with the above construct the function would be executed once for every row in atable so we changed it to
SELECT server, otherstuff
from atable
where server = (select dbo.cap_parm('servername'))
which greatly increased performance as now the function is called only once, effectively becomming a constant which is what we wanted.
We have been using this technique for several years now but today came across this issue.
We had a user that wanted to concatenate the value from three different parameters into a single string.
SELECT 'CPU % Busy - ' + (SELECT dbo.CAP_PARM('Parameters\Server')) + ' - ' + (SELECT dbo.CAP_PARM('Parameters\1 Start Date')) + ' to ' + (SELECT dbo.CAP_PARM('Parameters\2 End Date'))
Which should result in something that looks like
CPU % Busy - AUPROD552 - 10/Mar/2008 to 17/Apr/2008
However when this query is executed it never completes, eventually failing with a message "8623: The query processor ran out of internal resources and could not produce the query plan..." It took about 2 hours to get to this point.
After a lot of messing around I realised that if I changed the query to
SELECT 'CPU % Busy - ' + dbo.cap_parm('Parameters\Server') + ' - ' + dbo.cap_parm('Parameters\1 Start Date') + ' to ' + dbo.cap_parm('Parameters\2 End Date')
It all worked fine.
The original query only fails when the third Select is added i.e. the following works fine.
SELECT 'CPU % Busy - ' + (SELECT dbo.CAP_PARM('Parameters\Server')) + ' - ' + (SELECT dbo.CAP_PARM('Parameters\1 Start Date'))
When I tried to view the estimated execution plan in management Studio it couldn't be displayed, I gave up waiting after an hour.
I suspect that SQL gets into some sort of triangular loop when the 3rd select is added.
So, in this case I have a solution, simply don't wrap the function calls in (Select...) but I'm curious at to why it fails with the (Select...),
Finally my question... why does it fail?
Cheers..
Adrian.
December 4, 2008 at 6:29 am
I'm not sure, but the first question I'd have is, what does the execution plan of the scalar function look like, all by it's lonesome?
"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
December 4, 2008 at 2:18 pm
The following query generates the selectselect.jpg execution plan
select * from (Select dbo.cap_parm('SAMPLES\PARAMETERS\Month') as a) xxx
Whereas this
Select dbo.cap_parm('SAMPLES\PARAMETERS\Month')
generates the select.jpg execution plan
They both appear identical
December 4, 2008 at 4:30 pm
The function definition:
GO
/****** Object: UserDefinedFunction [dbo].[CAP_PARM] Script Date: 12/05/2008 10:25:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[CAP_PARM](@PARM_NAME varchar(8000))
RETURNS varchar(8000) AS
BEGIN
declare @RETVALUE varchar(8000) declare @username varchar(200)
set @username = (select SUSER_SNAME(SUSER_SID()))
set @RETVALUE =
(select PARM_VALUE
from CAP_REPORTOBJECTS as a
left join
CAP_PARAMETERS_USER as b
on a.ro_id = b.parm_id
where upper(a.RO_FOLDER + '\' + a.RO_NAME) = upper(@PARM_NAME) and
upper(b.PARM_USER) = upper(@username))
set @retvalue =
isnull(@RETVALUE,(select PARM_VALUE
from CAP_REPORTOBJECTS as a
left join
CAP_PARAMETERS as b
on a.ro_id = b.parm_id
where upper(a.RO_FOLDER + '\' + a.RO_NAME) = upper(@PARM_NAME)))
return (@RETVALUE)
END
December 5, 2008 at 5:45 am
adrian (12/4/2008)
The following query generates the selectselect.jpg execution planselect * from (Select dbo.cap_parm('SAMPLES\PARAMETERS\Month') as a) xxx
Whereas this
Select dbo.cap_parm('SAMPLES\PARAMETERS\Month')
generates the select.jpg execution plan
They both appear identical
Yeah, from this script alone, they will. You're not doing anything that would change the general behavior. with the SELECT... vs. SELECT * FROM (SELECT...).
Looking at the execution plan and the query, two things. First, using the UPPER function will prevent good clean index seeks. That's probably why you're getting an index scan. Do you have a case sensitive collation on your database? If not, you don't need those UPPER functions. Also, combining two fields in the where clause with the + sign is also going to prevent good index use. You might want to make a calculated field so that you store those together and then you can index that.
Second, With the structure you have, you're running both queries every time. Instead of ISNULL, what about putting in an IF statement to check to see if @retvalue IS NULL. If it is, then run the second query. You would then want to consider breaking both queries into seperate procedures so that you get a good plan more consistently, but that's a different problem.
But even more to the point, why aren't you simply selecting from these tables in a single, set based step instead of going to a function call? You have a table that has the parameters, stored by user or not. Get the data you need and then format out in your client. You'll get much better behavior of the queries.
For the future, if you want to post the whole plan, this shows how: http://www.jumpstarttv.com/uploading-an-execution-plan-to-sqlservercentralcom_385.aspx
"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
December 5, 2008 at 6:24 pm
Hi there,
Thanks for your reply.
Do you have a case sensitive collation on your database? If not, you don't need those UPPER functions
We do try to enforce a case insensitive collation but some of our clients want case sensitivity.... so we are stuck with the UPPER.
Also, combining two fields in the where clause with the + sign is also going to prevent good index use. You might want to make a calculated field so that you store those together and then you can index that.
Agreed, however both these tables are tiny... less than a few hundred rows, performance is not an issue nor will it become one in the future. As to the calculated field I also agree, very early on in my development process I realised that I should have kept the RO_FOLDER and RO_NAME name columns together in one but it's way to messy to change right now and again not necessary as there is no substantial performance hit. I have it on my development list to change this in the next major release.
Second, With the structure you have, you're running both queries every time. Instead of ISNULL, what about putting in an IF statement to check to see if @retvalue IS NULL. If it is, then run the second query.
I'm a little confused here I was under the impression that ISNULL would only execute the (select statement if @RETVALUE was actually NULL. Are you saying that the Select is executed even if @RETVALUE is not null?
But even more to the point, why aren't you simply selecting from these tables in a single, set based step instead of going to a function call?
The reason for a function call is to make it easier for my users. My application is a generic reporting tool where users can create their own queries, charts documents etc. each of these can call the "parameter" which provides filtering etc. I agree that the resultant queries coul dbe better if I exposed everything in my database to the users and just had them construct everything for them selves but then that would defeat the purpose of the application. And that is to remove all that stuff (or as much as possible) from the user whilst still providing a generic facility.
I still don't understand why the two queries behave differently, This works fine.
SELECT 'CPU % Busy - ' + (SELECT dbo.CAP_PARM('Parameters\Server')) + ' - ' + (SELECT dbo.CAP_PARM('Parameters\1 Start Date'))
But this fails to build a plan,
SELECT 'CPU % Busy - ' + (SELECT dbo.CAP_PARM('Parameters\Server')) + ' - ' + (SELECT dbo.CAP_PARM('Parameters\1 Start Date')) + ' to ' + (SELECT dbo.CAP_PARM('Parameters\2 End Date'))
Are you saying that if I implement all your suggestions (except eliminating the function) the problem will go away because while my code may be a little inefficient I don't think it would stop the creation of an execution plan.
Still confused.
December 6, 2008 at 4:25 am
You're getting an error, as you stated above:
However when this query is executed it never completes, eventually failing with a message "8623: The query processor ran out of internal resources and could not produce the query plan..." It took about 2 hours to get to this point.
It is possible to make things overly complex and have SQL Server fail. User defined functions are a notorious performance and execution plan choke point. You're putting together odd constructions for your queries (I'm a little surprised it ever worked) and you've got limitations and methods and other choke points you've placed on yourself. All this is combining to make an untenable query.
As you've already seen, changing the construct of the query to a more traditional approach largely fixed the problem. It's really pretty simple, the more you work in the manner that SQL Server is accustomed to, the better off you are. If you don't already have a copy, I'd suggest getting Itzik Ben-Gan's excellent book Inside SQL Server 2005 TSQL Querying. It'll help explain how SQL Server processes queries.
The last keynote on Friday at the 2008 PASS Summit was by this professor, whose name escapes me at the moment. It was amazing and informative. If you can get a recorded copy of it, it's worth watching. Anyway, he had a quote that's stuck with me, "Optimizers are fragile."
"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
December 6, 2008 at 4:12 pm
hi again,
this is becoming something of an academic exercise for me, I'd really like to understand what's occurring here, and thanks to your feedback I'm learning a lot too... which is always good.
I've made a few changes to simplify the function (I know you don't like the function but there is no alternative, I need this to be a function). Here is the function as it stands now.
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[CAP_PARM](@PARM_NAME varchar(8000))
RETURNS varchar(8000)
AS
BEGIN
declare @username varchar(200)
set @username = (select SUSER_SNAME(SUSER_SID()))
RETURN (select PARM_VALUE
from CAP_REPORTOBJECTS as a
left join
CAP_PARAMETERS_USER as b
on a.ro_id = b.parm_id
where b.PARM_USER = @username AND a.RO_FOLDERNAME = @PARM_NAME)
END
I've removed the second step altogether, just to see what happens, I've added a RO_FOLDERNAME column to the CAP_REPORTOBJECTS table and created an index on that column. I've removed all the UPPER function calls. Yet still I'm getting the same problem, if I concatenate two of these calls together using the (Select dbo.cap_parm(....)) syntax it works find, when I concatenate on a third it fails to build a plan.
I've included the execution plan (thanks for the tip there too).
this one works
SELECT 'CPU % Busy - ' + (SELECT dbo.CAP_PARM('samples\Parameters\System')) + ' - ' + (SELECT dbo.CAP_PARM('samples\Parameters\System'))
this one fails to build a plan
SELECT 'CPU % Busy - ' + (SELECT dbo.CAP_PARM('samples\Parameters\System')) + ' - ' + (SELECT dbo.CAP_PARM('samples\Parameters\System'))+ ' - ' + (SELECT dbo.CAP_PARM('samples\Parameters\System'))
this one (without the (SELECT sbo.cap_parm(...)) syntax works ) the execution plan of this one looks identical to the first example above.
SELECT 'CPU % Busy - ' + dbo.CAP_PARM('samples\Parameters\System') + ' - ' + dbo.CAP_PARM('samples\Parameters\System') + ' - ' + dbo.CAP_PARM('samples\Parameters\System')
So, now I think lets make the function a very simple select.
SET ANSI_NULLS ON
ALTER FUNCTION [dbo].[CAP_PARM](@PARM_NAME varchar(8000))
RETURNS varchar(8000)
AS
BEGIN
RETURN (select 'fred')
END
This has exactly the same results at the two previous more complicated functions.
What about if I remove the select from within the function
SET ANSI_NULLS ON
ALTER FUNCTION [dbo].[CAP_PARM](@PARM_NAME varchar(8000))
RETURNS varchar(8000)
AS
BEGIN
RETURN 'fred'
END
same result, fails to build a plan when the third concatenation is added.
Do we put this down to a bug in SQL? From my reading of the SELECT and UDF syntax rules this should work.
December 7, 2008 at 7:41 am
I'm not sure. I do know that the construct where you're using 'SELECT... (SELECT..) + (SELECT...)' is a bit odd and not how I would have done it. As you know, simply running the SELECT directly against the function works fine. I hesitate to call it a bug since you're operating in a less than standard way. However, either way, I'd suggest you post this as a possible bug at connect.microsoft.com. The Microsoft engineers will probably have a little more insight into why the nested SELECT is causing problems than I do. I just know that I wouldn't do it like that and I'm not surprised it's giving you a bit of a problem.
"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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply