December 1, 2008 at 11:03 am
Hello,
I have a View that is created from a small (no more then 500 rows) table, but highly accessed table, and the view is highly accessed as well. Due to some new business requirements, I had to place a lot of CASES in the VIEW, with up to 3 nest levels no some fields. The table the view reads data from is highlly volatible, and changes constantly. My question is whether I send to functions all the values I use on each CASE and place the CASEs inside the function, and call the function from the view, or I leave the CASEs on the view.
Does it make a difference performance wise? I'm worried, since the view is accessed so many times. The function has a pre-compilled execution plan and it makes it faster to go through all the logic then in the view?
Thanks so much for the help.
Pascal
December 1, 2008 at 11:17 am
You'd have to examine the execution plans but my hunch would be just put them in the view.
December 1, 2008 at 11:19 am
What information should I see on the execution plan? CPU and IO times?
December 1, 2008 at 11:25 am
To really help, we would need to see the structure for the table, view, and the code for the function(s) used in the view. Also some test data might help with testing if needed.
You may want to take some time to read the article I reference below in my signature block regarding asking for help.
December 1, 2008 at 11:28 am
Generally putting a user-defined function in your SELECT list will not perform well because the function has to be evaluated for each row, thus causing RBAR.
As Steve suggests you would need to test, but I would be surprised if the function performed better.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 1, 2008 at 11:39 am
Lynn,
First of all, I'm sorry for not posting all the table contents, but it's a 100 column table, so the view and the functions are pretty huge. I just wanted a general guideline on how to really evaluate, from the execution plan of the view or from somewhere else, what solution would give the best performance. I can do and execute the test, so it won't be much trouble.
Thanks for your time.
Pascal
December 1, 2008 at 12:19 pm
Is it possible to just add another column to the table with the value you want displayed? Or perhaps create another table and perform a join in your view?
Regards,
Michelle Ufford
SQLFool.com - Adventures in SQL Tuning
December 1, 2008 at 12:55 pm
Hello Michelle,
Not in my case, since the rows that need change would have to be updated every second. A small example of the function that I'm generating for my benchmarks:
ALTER FUNCTION WOShiftNRunT (@ProductionEvType INT, @EnableMultiWO INT, @RSClassification INT, @RSClassColumn INT, @IDResource INT, @SequenceCnt1 SMALLINT, @SequenceCnt2 SMALLINT, @WOShiftT INT, @RSShiftDtStart DATETIME)
RETURNS INT
AS
BEGIN
DECLARE @pReturn INT
SELECT @pReturn =
CASE @ProductionEvType
WHEN 20 THEN
CASE @EnableMultiWO
WHEN 2 THEN dbo.fc_rate (@idresource, @sequencecnt1 ,@sequencecnt2, GETDate(), @RSClassColumn)
WHEN 3 THEN CASE @RSClassification
WHEN @RSClassColumn THEN @WOShiftT + DateDiff(s,@RSShiftDtStart,GETDate())
ELSE @WOShiftT
END
ELSE CASE @RSClassification
WHEN @RSClassColumn THEN @WOShiftT + DateDiff(s,@RSShiftDtStart,GETDate())
ELSE @WOShiftT
END
END
ELSE
CASE @RSClassification
WHEN @RSClassColumn THEN @WOShiftT + DateDiff(s,@RSShiftDtStart,GETDate())
ELSE @WOShiftT
END
END
RETURN @pReturn
END
As you can see, most of the calculations envolve GETDATE(), so that's not a viable solutions. All these CASEs I took from my main View, and they're used 6 types inside the view. Now I have a view with the CASEs and a view calling the function that has the CASE inside it.
I would just like to know what would be the best way to see which is better, performance wise. I'll read the CPU value on the Profiller. Is there a better parameter?
Tks
Pascal
December 1, 2008 at 1:03 pm
I'd probably start with CPU and duration. Happy profiling! 🙂
Regards,
Michelle Ufford
SQLFool.com - Adventures in SQL Tuning
December 1, 2008 at 1:29 pm
To share the results of my tests, I developed one view, VWRetentiv that has the CASEs directly into it, and a VWRetentiv2 that calls the functions that has the CASEs. I wrote a Proc that executed each view 100 times, and it was confirmed. The CPU time for VWRetentiv2 (with the functions) was 5 times bigger when compared with VWRetentiv (without the functions), 187 CPU cycles vs. 1032 CPU cycles. The amount of Reads was the same, and the Duration was also larger for VWRetentiv2, 5447 mili vs 5849 mili.
Thanks for the help
Pascal
December 1, 2008 at 3:26 pm
Thank you for sharing the results! I think it'd be great if more people did that... it would make the forum a better resource for people who may have similar questions / problems. 🙂
Regards,
Michelle Ufford
SQLFool.com - Adventures in SQL Tuning
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply