August 22, 2008 at 5:18 am
hello!
i have a table valued function which has very low performance.
SELECT * FROM MyFunction(ParamValue1, ParamValue2, ParamValue3)
takes about 16 minutes.
when i run 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
it takes only 30 seconds.
i tried to recompile the function - no positive result.
can anybody help me?
thanks a lot
wolfgang
ps: i am a german - so don't worry about my english
August 22, 2008 at 6:01 am
Hello,
If you code it as an SP, does it still take 16 minutes to execute? Even with the recompile option?
If the SP is faster, can you compare the SP and UDF execution plans? Any obvious differences?
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
August 22, 2008 at 6:27 am
is it a multi-statement table valued function? They don't have statistics so they resolve as 1 row queries. If you're returning more than one row, then the execution plan is bad and performance suffers.
Can you post an execution plan.
Also, I agree with the previous post. If you can write it as a stored proc and it runs well, why muck about with the UDF?
"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 6:31 am
i made a sp but it also took 16 minutes.
August 22, 2008 at 6:34 am
Can you post the execution plan?
"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 7:45 am
Please post the function code listing also.
[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 8:00 am
ALTER FUNCTION [dbo].[puma_rep_UnterEinheiten](@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))
RETURNS @report TABLE(child_org_id int,GJ int,hg smallint,summe bigint) AS
BEGIN
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
INSERT INTO @report
SELECT childorg,sum_GJ,sum_hg,sum(sum_wert) as sum_summe
FROM puma_getE5OrgsOnChildorgs(@baseorg)
JOIN T_MASSNAHMEN on mas_org_id= orgid
JOIN T_SUMMEN on sum_mas_id = mas_id
where mas_id in (
select heb_mas_id from T_MASSNAHMENHEBEL
JOIN T_REPORT_HEBEL on rheb_hgr_id = heb_hgr_id
WHERE rheb_transact_id = @transId
)
and (@filter = 0 or mas_id in (
SELECT heb_mas_id from T_MASSNAHMENHEBEL mh
JOIN T_HEBELGRUPPEN h0 on mh.heb_hgr_id = h0.hgr_id
left JOIN T_HEBELGRUPPEN h1 on h0.hgr_parent_id = h1.hgr_id
left JOIN T_HEBELGRUPPEN h2 on h1.hgr_parent_id = h2.hgr_id
WHERE (h0.hgr_id = @filter or h1.hgr_id = @filter or h2.hgr_id = @filter or h2.hgr_parent_id = @filter)
))
and sum_E2HB_org_id=0 -- Hauptbaum
AND msum_nach_umsatz= case mas_mart_id when 2 then @nachUmsatz when 3 then @nachUmsatz else 'y' end
AND mas_abschlussmonat = @abschlussmonat
AND sum_monat=@aktmonat
AND sum_wae_id= case @waeid when 0 then waeid else @waeid end
AND mas_mart_id in (@mart1,@mart2,@mart3,@mart4)
AND mas_verworfen='n'
AND mas_isDauerhaft in(@dauer1,@dauer2)
and mas_konsEbene <= @baseEbene
and mas_konsebene <=@konsol
GROUP BY childorg,sum_GJ,sum_hg,msum_nach_umsatz
END
ELSE -- ohne Hebel, schlsselung NB->HB
BEGIN
SELECT @ebene = org_ebene FROM T_ORGEINHEITEN WHERE org_id=@baseorg
if @ebene=2 -- nur für verwendung der Funktion als GJ-Bericht!
BEGIN
INSERT INTO @report
SELECT childorg,sum_GJ,sum_hg,sum(sum_wert) as sum_summe
FROM puma_getE5OrgsOnChildorgs(@baseorg) e5
JOIN T_MASSNAHMEN on mas_org_id= e5.orgid
JOIN T_SUMMEN on sum_mas_id = mas_id
where mas_id in (
select heb_mas_id from T_MASSNAHMENHEBEL
JOIN T_REPORT_HEBEL on rheb_hgr_id = heb_hgr_id
WHERE rheb_transact_id = @transId
)
and (@filter = 0 or mas_id in (
SELECT heb_mas_id from T_MASSNAHMENHEBEL mh
JOIN T_HEBELGRUPPEN h0 on mh.heb_hgr_id = h0.hgr_id
left JOIN T_HEBELGRUPPEN h1 on h0.hgr_parent_id = h1.hgr_id
left JOIN T_HEBELGRUPPEN h2 on h1.hgr_parent_id = h2.hgr_id
WHERE (h0.hgr_id = @filter or h1.hgr_id = @filter or h2.hgr_id = @filter or h2.hgr_parent_id = @filter)
))
and sum_E2HB_org_id=0 -- Hauptbaum
AND msum_nach_umsatz= case mas_mart_id when 2 then @nachUmsatz when 3 then @nachUmsatz else 'y' end
AND mas_abschlussmonat = @abschlussmonat
AND sum_monat=@aktmonat
AND sum_wae_id= case @waeid when 0 then e5.waeid else @waeid end
AND mas_mart_id in (@mart1,@mart2,@mart3,@mart4)
AND mas_verworfen='n'
AND mas_isDauerhaft in(@dauer1,@dauer2)
and mas_konsEbene <= @baseEbene
and mas_konsebene <=@konsol
GROUP BY childorg,sum_GJ,sum_hg,msum_nach_umsatz
UNION
SELECT sum_e2hb_org_id,sum_GJ,sum_hg,sum(sum_wert) as sum_summe
FROM T_MASSNAHMEN
JOIN T_SUMMEN on sum_mas_id = mas_id
JOIN T_ORGEINHEITEN as HB_E2 on sum_E2HB_org_id=HB_E2.org_id
where mas_id in (
select heb_mas_id from T_MASSNAHMENHEBEL
JOIN T_REPORT_HEBEL on rheb_hgr_id = heb_hgr_id
WHERE rheb_transact_id = @transId
)
and (@filter = 0 or mas_id in (
SELECT heb_mas_id from T_MASSNAHMENHEBEL mh
JOIN T_HEBELGRUPPEN h0 on mh.heb_hgr_id = h0.hgr_id
left JOIN T_HEBELGRUPPEN h1 on h0.hgr_parent_id = h1.hgr_id
left JOIN T_HEBELGRUPPEN h2 on h1.hgr_parent_id = h2.hgr_id
WHERE (h0.hgr_id = @filter or h1.hgr_id = @filter or h2.hgr_id = @filter or h2.hgr_parent_id = @filter)
))
and sum_e2hb_org_id=@baseorg
AND sum_monat=@aktmonat
AND sum_wae_id= case @waeid when 0 then hb_e2.org_wae_id else @waeid end
AND msum_nach_umsatz= case mas_mart_id when 2 then @nachUmsatz when 3 then @nachUmsatz else 'y' end
AND mas_abschlussmonat = @abschlussmonat
--AND mas_isHB='n'
AND mas_mart_id in (@mart1,@mart2,@mart3,@mart4)
AND mas_verworfen='n'
AND mas_isDauerhaft in(@dauer1,@dauer2)
and mas_konsEbene <= @baseEbene
and mas_konsebene <=@konsol
GROUP BY sum_e2hb_org_id, sum_GJ,sum_hg,msum_nach_umsatz
END
else
if @ebene=1
BEGIN
INSERT INTO @report
SELECT childorg,sum_GJ,sum_hg,sum(sum_wert) as sum_summe
FROM puma_getE5OrgsOnChildorgs(@baseorg)
JOIN T_MASSNAHMEN on mas_org_id= orgid
JOIN T_SUMMEN on sum_mas_id = mas_id
where mas_id in (
select heb_mas_id from T_MASSNAHMENHEBEL
JOIN T_REPORT_HEBEL on rheb_hgr_id = heb_hgr_id
WHERE rheb_transact_id = @transId
)
and (@filter = 0 or mas_id in (
SELECT heb_mas_id from T_MASSNAHMENHEBEL mh
JOIN T_HEBELGRUPPEN h0 on mh.heb_hgr_id = h0.hgr_id
left JOIN T_HEBELGRUPPEN h1 on h0.hgr_parent_id = h1.hgr_id
left JOIN T_HEBELGRUPPEN h2 on h1.hgr_parent_id = h2.hgr_id
WHERE (h0.hgr_id = @filter or h1.hgr_id = @filter or h2.hgr_id = @filter or h2.hgr_parent_id = @filter)
))
and sum_E2HB_org_id=0 -- Hauptbaum
AND msum_nach_umsatz= case mas_mart_id when 2 then @nachUmsatz when 3 then @nachUmsatz else 'y' end
AND mas_abschlussmonat = @abschlussmonat
AND sum_monat=@aktmonat
AND sum_wae_id= case @waeid when 0 then waeid else @waeid end
AND mas_mart_id in (@mart1,@mart2,@mart3,@mart4)
AND mas_verworfen='n'
AND mas_isDauerhaft in(@dauer1,@dauer2)
and mas_konsEbene <= @baseEbene
and mas_konsebene <=@konsol
GROUP BY childorg,sum_GJ,sum_hg,msum_nach_umsatz
UNION
SELECT HB_E2.org_id,sum_GJ,sum_hg,sum(sum_wert) as sum_summe
FROM T_MASSNAHMEN
JOIN T_SUMMEN on sum_mas_id = mas_id
JOIN T_ORGEINHEITEN as HB_E2 on sum_E2HB_org_id=HB_E2.org_id
where mas_id in (
select heb_mas_id from T_MASSNAHMENHEBEL
JOIN T_REPORT_HEBEL on rheb_hgr_id = heb_hgr_id
WHERE rheb_transact_id = @transId
)
and (@filter = 0 or mas_id in (
SELECT heb_mas_id from T_MASSNAHMENHEBEL mh
JOIN T_HEBELGRUPPEN h0 on mh.heb_hgr_id = h0.hgr_id
left JOIN T_HEBELGRUPPEN h1 on h0.hgr_parent_id = h1.hgr_id
left JOIN T_HEBELGRUPPEN h2 on h1.hgr_parent_id = h2.hgr_id
WHERE (h0.hgr_id = @filter or h1.hgr_id = @filter or h2.hgr_id = @filter or h2.hgr_parent_id = @filter)
))
and HB_E2.org_parent_id=@baseorg
AND sum_monat=@aktmonat
AND sum_wae_id= case @waeid when 0 then HB_E2.org_wae_id else @waeid end
AND msum_nach_umsatz= case mas_mart_id when 2 then @nachUmsatz when 3 then @nachUmsatz else 'y' end
AND mas_abschlussmonat = @abschlussmonat
--AND mas_isHB='n'
AND mas_mart_id in (@mart1,@mart2,@mart3,@mart4)
AND mas_verworfen='n'
AND mas_isDauerhaft in(@dauer1,@dauer2)
and mas_konsEbene <= @baseEbene
and mas_konsebene <=@konsol
GROUP BY HB_E2.org_id,sum_GJ,sum_hg,msum_nach_umsatz
END
ELSE -- Ebene = 0
BEGIN
INSERT INTO @report
SELECT childorg,sum_GJ,sum_hg,sum(sum_wert) as sum_summe
FROM puma_getE5OrgsOnChildorgs(@baseorg)
JOIN T_MASSNAHMEN on mas_org_id= orgid
JOIN T_SUMMEN on sum_mas_id = mas_id
where mas_id in (
select heb_mas_id from T_MASSNAHMENHEBEL
JOIN T_REPORT_HEBEL on rheb_hgr_id = heb_hgr_id
WHERE rheb_transact_id = @transId
)
and (@filter = 0 or mas_id in (
SELECT heb_mas_id from T_MASSNAHMENHEBEL mh
JOIN T_HEBELGRUPPEN h0 on mh.heb_hgr_id = h0.hgr_id
left JOIN T_HEBELGRUPPEN h1 on h0.hgr_parent_id = h1.hgr_id
left JOIN T_HEBELGRUPPEN h2 on h1.hgr_parent_id = h2.hgr_id
WHERE (h0.hgr_id = @filter or h1.hgr_id = @filter or h2.hgr_id = @filter or h2.hgr_parent_id = @filter)
))
and sum_E2HB_org_id=0 -- Hauptbaum
AND msum_nach_umsatz= case mas_mart_id when 2 then @nachUmsatz when 3 then @nachUmsatz else 'y' end
AND mas_abschlussmonat = @abschlussmonat
AND sum_monat=@aktmonat
AND sum_wae_id= case @waeid when 0 then waeid else @waeid end
AND mas_verworfen='n'
AND mas_isDauerhaft in(@dauer1,@dauer2)
and mas_konsEbene <= @baseEbene
and mas_konsebene <=@konsol
AND mas_mart_id in (@mart1,@mart2,@mart3,@mart4)
GROUP BY childorg,sum_GJ,sum_hg,msum_nach_umsatz
UNION
SELECT HB_E1.org_id,sum_GJ,sum_hg,sum(sum_wert) as sum_summe
FROM T_MASSNAHMEN
JOIN T_SUMMEN on sum_mas_id = mas_id
JOIN T_ORGEINHEITEN as HB_E2 on sum_E2HB_org_id=HB_E2.org_id
JOIN T_ORGEINHEITEN As HB_E1 on HB_E2.org_parent_id=HB_E1.org_id
where mas_id in (
select heb_mas_id from T_MASSNAHMENHEBEL
JOIN T_REPORT_HEBEL on rheb_hgr_id = heb_hgr_id
WHERE rheb_transact_id = @transId
)
and (@filter = 0 or mas_id in (
SELECT heb_mas_id from T_MASSNAHMENHEBEL mh
JOIN T_HEBELGRUPPEN h0 on mh.heb_hgr_id = h0.hgr_id
left JOIN T_HEBELGRUPPEN h1 on h0.hgr_parent_id = h1.hgr_id
left JOIN T_HEBELGRUPPEN h2 on h1.hgr_parent_id = h2.hgr_id
WHERE (h0.hgr_id = @filter or h1.hgr_id = @filter or h2.hgr_id = @filter or h2.hgr_parent_id = @filter)
))
and HB_E1.org_parent_id=@baseorg
AND sum_monat=@aktmonat
AND sum_wae_id= case @waeid when 0 then HB_E1.org_wae_id else @waeid end
AND msum_nach_umsatz= case mas_mart_id when 2 then @nachUmsatz when 3 then @nachUmsatz else 'y' end
AND mas_abschlussmonat = @abschlussmonat
AND mas_mart_id in (@mart1,@mart2,@mart3,@mart4)
AND mas_verworfen='n'
AND mas_isDauerhaft in(@dauer1,@dauer2)
and mas_konsEbene <= @baseEbene
and mas_konsebene <=@konsol
GROUP BY HB_E1.org_id,sum_GJ,sum_hg,msum_nach_umsatz
END
END
RETURN
END
August 22, 2008 at 8:01 am
how can i post the execution plan?
August 22, 2008 at 8:19 am
amicusoft (8/22/2008)
how can i post the execution plan?
Create the execution plan in a Query Window (Ctrl-L). Then rgot to the results pane and right-click, pick "Save exectution Plan as..". In the Save As dialog, make sure that the file type is "Execution Plan(.sqlplan)".
Then attach it to your reply. Do NOT try to embed it into the text, the forum security filters eat any embedded html/xml.
[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 8:41 am
i am not allowed to upload .sqlplan-files.
should i change the extension to .txt?
August 22, 2008 at 8:52 am
Zip the file.
"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 9:10 am
here is the zip.
plan 1: 0%
plan 2: 93%
plan 3: 2%
plan 4: 2%
plan 5: 2%
August 22, 2008 at 9:59 am
amicusoft (8/22/2008)
i am not allowed to upload .sqlplan-files.should i change the extension to .txt?
?!?
Sure, try it.
[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 10:37 am
i put the .sqlplan-files in the uploaded zip (a few posts ago)
August 22, 2008 at 11:14 am
The painful plan is very large and difficult to read. The one thing that stands out is that it looks like you're performing the same joins over and over again within the query. That might be the first thing to focus on. Can you post the code?
BTW, this is multi-statement UDF right? Does it need to be?
"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 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply