October 27, 2010 at 1:34 pm
Does creating a Table Value Function add to the execution time of the statement?
I created some T-SQL then dumped it into a Table Value Function. It took no time to run as straight up code, but took 4-5 seconds to run once I created the TVF. Its the exact same code.
October 27, 2010 at 2:52 pm
as usual, it depends....
an inline table value function should perform the same...a multi statement TVF often slows things down...
care tou show us the function to see if we can help improve it?
Lowell
October 27, 2010 at 3:41 pm
I am using a multi line TVF
Basically the user gives me a month and an office. I pull some data from the month that they give me and the previous month and merge them together into one table so you can see the numbers side by side. When I run it outside of a the TVF it takes 0 seconds after I create the TVF takes 4-5. Which is exceptable I just wasn't expecting it.
ALTER FUNCTION [dbo].[fn_BillBillingRecoginizedFullDifferences]
(
-- Add the parameters for the function here
@MonthDate date,
@office varchar(100)
)
RETURNS
@RecognizedRevenue Table
(
AgrPkey int,
Customer varchar(600),
CurrentMonthAmount money,
PreviousMonthAmount money ,
PriorPeriod bit,
Variance money
)
AS
BEGIN
set @MonthDate=dbo.ufn_GetFirstDayOfMonth(@MonthDate)
Declare @TempTable Table
(
AgrPkey int,
Customer varchar(600),
CurrentMonthAmount money,
PreviousMonthAmount money ,
PriorPeriod bit
)
Insert into
@TempTable
(AgrPkey,Customer,CurrentMonthAmount, PreviousMonthAmount, PriorPeriod)
SELECT BillBillingAgreement.AgrPKey, RTRIM(LTRIM(BillBillingAgreement.AgrName)), SUM(BillBillingAdjustments.BiaAmount) AS Total,0,0
FROM BillBillingAgreement INNER JOIN
BillBillingAdjustments ON BillBillingAgreement.BaiPKey = BillBillingAdjustments.BaiPKey
WHERE (BillBillingAgreement.BaiOffice = @office) AND (BillBillingAdjustments.BiaInvoiceApproved = 1) AND (BillBillingAdjustments.BiaStartDate <=@MonthDate)
and (BillBillingAdjustments.BiaEndDate >= @MonthDate) AND (BillBillingAdjustments.BiaBillingDate <=@MonthDate)
GROUP BY BillBillingAgreement.AgrPKey, BillBillingAgreement.AgrName
--Add Prior Periods
Merge into @TempTable as target
using(
SELECT BillBillingAgreement.AgrPKey, SUM(
Case When @MonthDate<=BiaEndDate Then
(DateDiff(m,BiaStartDate,@MonthDate))*BillBillingAdjustments.BiaAmount
Else
(DateDiff(m,BiaEndDate,BiaStartDate)+1)*BillBillingAdjustments.BiaAmount
end) AS TotaPP
FROM BillBillingAgreement INNER JOIN BillBillingAdjustments ON BillBillingAgreement.BaiPKey = BillBillingAdjustments.BaiPKey
WHERE (BillBillingAdjustments.BiaBillingDate = @MonthDate) AND (BillBillingAdjustments.BiaInvoiceApproved = 1) AND
(BillBillingAdjustments.BiaStartDate < BillBillingAdjustments.BiaBillingDate) and (BillBillingAgreement.BaiOffice = @office)
GROUP BY BillBillingAgreement.AgrPKey
) as source
on target.AgrPkey=source.AgrPkey
when matched then
update set target.CurrentMonthAmount=target.CurrentMonthAmount+source.TotaPP, target.PriorPeriod=1
;
Declare @TempTable2 Table
(
AgrPkey int,
Customer varchar(600),
CurrentMonthAmount money,
NextMonthAmount money,
PriorPeriod bit
)
Insert into
@TempTable2
(AgrPkey,Customer,CurrentMonthAmount,NextMonthAmount, PriorPeriod)
SELECT BillBillingAgreement.AgrPKey, RTRIM(LTRIM(BillBillingAgreement.AgrName)), SUM(BillBillingAdjustments.BiaAmount) AS Total,0,0
FROM BillBillingAgreement INNER JOIN
BillBillingAdjustments ON BillBillingAgreement.BaiPKey = BillBillingAdjustments.BaiPKey
WHERE (BillBillingAgreement.BaiOffice = @office) AND (BillBillingAdjustments.BiaInvoiceApproved = 1) AND (BillBillingAdjustments.BiaStartDate <=DATEADD(m,-1,@MonthDate))
and (BillBillingAdjustments.BiaEndDate >= DATEADD(m,-1,@MonthDate)) AND (BillBillingAdjustments.BiaBillingDate <=DATEADD(m,-1,@MonthDate))
GROUP BY BillBillingAgreement.AgrPKey, BillBillingAgreement.AgrName
--Add Prior Periods
Merge into @TempTable2 as target
using(
SELECT BillBillingAgreement.AgrPKey, SUM(
Case When DATEADD(m,-1,@MonthDate)<=BiaEndDate Then
(DateDiff(m,BiaStartDate,DATEADD(m,-1,@MonthDate)))*BillBillingAdjustments.BiaAmount
Else
(DateDiff(m,BiaEndDate,BiaStartDate)+1)*BillBillingAdjustments.BiaAmount
end) AS TotaPP
FROM BillBillingAgreement INNER JOIN BillBillingAdjustments ON BillBillingAgreement.BaiPKey = BillBillingAdjustments.BaiPKey
WHERE (BillBillingAdjustments.BiaBillingDate = DATEADD(m,-1,@MonthDate)) AND (BillBillingAdjustments.BiaInvoiceApproved = 1) AND
(BillBillingAdjustments.BiaStartDate < BillBillingAdjustments.BiaBillingDate) and (BillBillingAgreement.BaiOffice = @office)
GROUP BY BillBillingAgreement.AgrPKey
) as source
on target.AgrPkey=source.AgrPkey
when matched then
update set target.CurrentMonthAmount=target.CurrentMonthAmount+source.TotaPP, target.PriorPeriod=1
;
merge into @TempTable as Target
using (select AgrPkey,Customer,CurrentMonthAmount,NextMonthAmount,PriorPeriod from @TempTable2) as source
on target.AgrPkey=source.AgrPkey
When matched then
update set target.PreviousMonthAmount=source.CurrentMonthAmount
when not matched by target then
insert (AgrPkey,Customer,CurrentMonthAmount, PreviousMonthAmount, PriorPeriod)
values(source.AgrPkey, source.Customer, 0,source.CurrentMonthAmount, source.PriorPeriod)
;
Insert into
@RecognizedRevenue
(AgrPkey,Customer,PreviousMonthAmount,CurrentMonthAmount, PriorPeriod, Variance)
select AgrPkey,Customer,PreviousMonthAmount,CurrentMonthAmount, PriorPeriod
, (CurrentMonthAmount-PreviousMonthAmount) as Variance
from @TempTable
where (CurrentMonthAmount-PreviousMonthAmount)<>0
order by (CurrentMonthAmount-PreviousMonthAmount)
RETURN
END
October 28, 2010 at 2:50 pm
Ouch!
thanks for your help, this is the SQL Newbies forum correct?
Little background on myself, i'm all self taught and new to actually writing in T-SQL. So if i'm doing something wrong, rather then negatively bashing, I would appreciate more constructive advice or at least where I can find the information needed to do things correctly. I come to the SQL Central forums cause they are usually very helpful and informative.
October 28, 2010 at 3:18 pm
mbender (10/28/2010)
Ouch!thanks for your help, this is the SQL Newbies forum correct?
Little background on myself, i'm all self taught and new to actually writing in T-SQL. So if i'm doing something wrong, rather then negatively bashing, I would appreciate more constructive advice or at least where I can find the information needed to do things correctly. I come to the SQL Central forums cause they are usually very helpful and informative.
Mind if I ask a few questions?
Why are you using a TVF?
Are you just looking to get a table-like result set?
What's the destination for the data? Would it be acceptable to have the result in a table?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 28, 2010 at 3:21 pm
Sorry for that mbender. Joe Celko's the highly trained tourettes guy you usually leave locked in a back office at work and feed steaks and requirement documents in through the mail slot. Incredibly good, just noone wants to talk to him.
I haven't had a chance to tear down your code to review it, and probably won't for a bit, but didn't want our local barking dog to scare you off... well, not yet.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 28, 2010 at 3:22 pm
mbender (10/28/2010)
Ouch!thanks for your help, this is the SQL Newbies forum correct?
Little background on myself, i'm all self taught and new to actually writing in T-SQL. So if i'm doing something wrong, rather then negatively bashing, I would appreciate more constructive advice or at least where I can find the information needed to do things correctly. I come to the SQL Central forums cause they are usually very helpful and informative.
Never mind him mbender. He's just grumpy for some reason. 😀
Celko, play nice or go back to your dungeon! It's not Halloween yet!
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
October 28, 2010 at 3:23 pm
Joe,
Your online persona has a negative attitude, is obnoxious, and is borderline crass. Just chill it.
Better yet, let the others out here help out. We can work with these people, and show them better ways. You? You're just driving people away, and they'll never get any better.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 28, 2010 at 3:24 pm
I am no expert in T-SQL but here are couple of things I noticed.
1. Your variable table can cause performance issues. This is basically because variable table does not have statistics. How many rows of data are you inserting into this variable table?
2. I see that you are doing an order by in an insert statement. I do not think you need that.
3. Did you check if there is a change in plan when executing the statements and when executing using the function? It could be that the function might be having a different plan.
I am not sure what your table structure is or what indexes you have. Therefore I cant help you more than that. I am sure couple of others will post something that will be really helpful for you.
-Roy
October 28, 2010 at 3:24 pm
The reason i'm using a TVF is ya i need it to be in a table format. I'm trying to pull the data live, rather then storing it in a table. The destination for the data is an asp page. I'm open to any ideas. It just was odd that the execution went longer when putting it TVF, but again that's probably my in experience.
Thanks
October 28, 2010 at 3:26 pm
Currently are tables are not indexed.
October 28, 2010 at 3:29 pm
Are using Temp Tables a no no?
I've just started using them as work tables, and have really enjoyed it. Normally we would do all the work in the asp page, but that seemed to have performance issues. So I've really like the added performance and flexibility in doing it in SQL.
October 28, 2010 at 3:30 pm
You can use a subquery the same way you can the inline TVF. Well, mostly. At a guess we just need to thin your code down a bit.
Are your timing tests from your desktop or from a real server, if you don't mind me being nosy?
AND....
Celko, one other thing (sorry to kinda threadjack, mbender...)
You call a function to get the first day of a month, and it name violates ISO-11179 naming rules
NO. ONE. CARES. Except you. Cripes, get off the horse. Yay, ISO. Whoopee. Yay. Throw a party. Get over it. The rest of us are down here, not spending weeks reading a naming document that our company already has contradicting, and in place, conventions for.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 28, 2010 at 3:32 pm
mbender (10/28/2010)
Are using Temp Tables a no no?I've just started using them as work tables, and have really enjoyed it. Normally we would do all the work in the asp page, but that seemed to have performance issues. So I've really like the added performance and flexibility in doing it in SQL.
Temp tables are a definate performance optimizer, depending on usage. In general, if it runs faster with the temp then without, it's not a nono. 🙂 It's an option, but I wouldn't expect to see much better performance on it then the tvf except for what the temp table would join to being able to seek instead of scan because of optimization statistics and expectations.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 28, 2010 at 3:32 pm
Check the execution plan and see if there is any difference when executing as RAW SQL statements and when executing as TVF.
Temp tables can be used depending on the circumstances. But you are using a Variable table and that can cause performance issues if there lots of rows in it.
Indexes should be created on the underlying tables to make the execution faster. Heap tables are not the right way to go.
-Roy
Viewing 15 posts - 1 through 15 (of 52 total)
You must be logged in to reply to this topic. Login to reply