August 22, 2008 at 11:54 am
Grant: I think that the UDF listing is back on page one...
amicusoft: I seems very likely that the run-time difference here is due to "parameter-sniffing". You have lots of parameter controlled IF..ELSE statements and many cases of "filter-switches": WHERE clauses like "AND (@filter=0 OR (...)), which are fodder for parameter-sniffing problems.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 22, 2008 at 12:00 pm
Then what's on page 2?
Regardless, Page 2 needs some help.
I'd still like to see the code.
"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 22, 2008 at 1:56 pm
please remember the first posting.
the statements are the same, only the way i call the statements is different.
instead of
SELECT * FROM MyFunktion(ParameterValue1...)
i builded a script like
DECLARE @Param1 INT
DECLARE @Param2 INT
DECLARE @Param3 INT
SET @Param1 = ParamValue1
SET @Param2 = ParamValue2
SET @Param3 = ParamValue3
DECLARE @Result TABLE ...
....
( SQL-text equals to original function)
....
SELECT * FROM @Result
the function-call takes 16 minutes, the script-call only 30 seconds.
the core-statements are the same and i recompiled the function. so i think there has to be a bug/feature in sql server 2005 which slows down the function-call.
isn't this right?
August 22, 2008 at 4:59 pm
amicusoft (8/22/2008)
please remember the first posting.the statements are the same, only the way i call the statements is different.
....
the function-call takes 16 minutes, the script-call only 30 seconds.
the core-statements are the same and i recompiled the function. so i think there has to be a bug/feature in sql server 2005 which slows down the function-call.
That is exactly how the parameter-sniffing problem happens. Whether it is a bug or not, is a matter of opinion, however, it is a well-know problem with SQL Server.
In these forums alone you can probably find over 100 case of people complaing that their SQL code "works fine interactively, but runs very slowly in a stored procedure". And almost every one of them turned out to be parameter-sniffing.
It is an attempt by SQL Server to optimize the way that SQL code works in a stored procedure by saving it's query plan from when you compiled it, so that it does not have to recompile it. Since it does not know what parameters to apply to the query plan optimizations, someitmes it guess based on defaults and other constant variable values in the stored procedure. Although this works most of the time, sometimes it results in it saving and executing a plan that is very bad for the parameters that you actually end up passing it.
Procedures that use complex IF structures to switch between large queries or that use parameters for "WHERE switches" are particulariy suceptible to this. Your function has both of these.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 22, 2008 at 5:21 pm
thank you very much! 🙂
now i think i understand the problem - the first step to resolve it.
sincerely yours
wolfgang
August 22, 2008 at 10:27 pm
Two things... Your IN statements and you UNION statement s probably what's killing you.
Try EXISTS instead of IN, and try using UNION ALL of there can be no duplicates between the two SELECT statements.
N 56°04'39.16"
E 12°55'05.25"
August 23, 2008 at 8:06 am
RBarryYoung has indicated the advantages and disadvantages of paramater sniffing and cached query plans. There are two design items that I recommend.
1. Variables that are used in a WHERE should NOT have the value set within the stored procedure but should only reference passed paramaters.
2. As is is very expensive to recompiling the query plan of a stored procedure that has multiple or complex SQL statements when only certain of the statements will actually run, these statements should be moved to a seperate stored procedures.
Here is how I would restructure this into 5 procedures where X is executed from the client, is the only procedure that has any variables and X executes procedure x_Welt_0 , x_ebene_2, x_ebene_1 or x_ebene_0 as appropriate.
Procedures x_Welt_0 , x_ebene_2, x_ebene_1 or x_ebene_0 should have passed parameters, but no variables declared. It might be necessary to use the "with recompile" option on these procedures depending on the data distribution.
CREATE PROCEDURE X
(@baseorg int,@welt bit,@nachUmsatz char(1),@transid int,@abschlussmonat int,@mart1 smallint, @mart2 smallint, @mart3 smallint, @mart4 smallint, @konsol smallint,@waeid smallint,@filter int,@wirkung char(1))
as
set nocount on
DECLARE @ebene smallint, @aktmonat int
DECLARE @baseEbene smallint
select @baseEbene = org_ebene from t_orgeinheiten where org_id = @baseorg
declare @dauer1 char(1)
declare @dauer2 char(1)
set @dauer1 = 'x'
set @dauer2 = 'x'
if @wirkung = 'e' or @wirkung = 'b' set @dauer1 = 'n'
if @wirkung = 'd' or @wirkung = 'b' set @dauer2 = 'y'
SET @aktmonat = @abschlussmonat
if @abschlussmonat= 0
SELECT @aktmonat = abschlussmonat FROM T_SONSTIGES
if @welt=0
BEGIN
exec x_Welt_0 @p = ....
END
ELSE
BEGIN
SELECT @ebene = org_ebene FROM T_ORGEINHEITEN WHERE org_id=@baseorg
if @ebene=2
BEGIN
exec x_ebene_2 @p = ....
ELSE
BEGIN
if@ebene=1
BEGIN
exec x_ebene_1 @p = ....
END
ELSE -- Ebene = 0
BEGIN
exec x_ebene_0 @p = ....
END
END
END
SQL = Scarcely Qualifies as a Language
August 23, 2008 at 9:01 am
I agree with Carl, breaking this function apart into a master function with separate subfunctions is your highest return.
You already have almost all of the code, you just need to cut it apart and past it back together differently.
FYI: I do think that it is OK to pass parameters from the master-function to the sub-functions, so long as they are used just as straight WHERE conditions (column=@parameter) and not as IF switches or WHERE switches (@parameter=0 OR...). Do all of the switching in the master-function, that way the sub-functions can keep their stored query plans pristine.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 20, 2011 at 4:11 am
I agree that it is parameter sniffing, and adding the declare etc to the query makes it run faster.
My next question is, how do I do this in a view or table-valued function. A stored procedure is not an option here as we need to be able to select the columns from our application.
July 21, 2011 at 9:15 am
1) and (@filter = 0 or mas_id in (
That is a horrible construct for the optimizer. split this big mess into two separate pieces of code, one of which is explicitly coded for @filter = 0 and another which is explicitly coded for @filter <> 0
2) I didn't look closely but it seems like you might be able to combine many of those iterative hits on the tables from those very similar looking SELECT statements
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply