April 7, 2010 at 5:23 am
Hello all,
I'm aware Views should always be considered before trying Table-Valued function as they gain in performance in several aspects.
Nevertheless, please check this real world cenario that i'm dealing with and share some knowledge and guidance.
I have this generic view that was built in order to filter user/access to data. The view returns only specific rows depending on which user is invoking - windows based security.
That generic view is used in several other views in order to filter data. The main problem is that view is consuming a lot of time when query results are considerably huge.
That performance issue took me to TVF. My TVF output is exactly the same as the view and executes much faster than the view - select * from MyGenericView is slower than select * from dbo.MyTVF. Same returned rows.
This induced me to try TVF and change that generic view in order to call directly my TVF.
In fact, i gain a lot in query result time in some not very complex views, but with other views that by design and because of some joined tables (some of those tables have thousands of rows) were already slow, its query result time got worse.
Indexed views cannot help me because my generic view is non-deterministic (depends on the caller user) and has left outer joins... Temp tables or SProcedures won't solve my problem 'cause that generic view is invoked by several others and returned output must be dynamic as the caller user has access to more or less data.
Strange thing: Seems logic to me why TVF has poor performance in queries invoking tables with large amount of data, bu why query result time is worst (considerably worst) when compared with views???
Any thoughts on this one?
Thanks in advance.
April 7, 2010 at 6:18 am
You have explained your concept, but ... can you provide DDL and some execution statistics of your tests ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 7, 2010 at 7:09 am
Try using "Inline" Table Valued functions instead. The syntactical difference between a "Multi-line" Table Valued Function and an "Inline" table valued function is explained in Books Online.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 7, 2010 at 9:12 am
Thanks for quick answers.
About ITVF suggestion, it will not work, because my TVF has logic inside.
I'll try to provide further info as requested.
Here's the original t-sql of generic view:
WITH SubCategoriaUtilizador (SubCategoria)
AS
(SELECT dbo.stUtilizadorSubCategoria.SubCategoria
FROM dbo.stUtilizadorSubCategoria INNER JOIN
dbo.svUtilizadorLogin ON dbo.stUtilizadorSubCategoria.UtilizadorLogin = dbo.svUtilizadorLogin.UtilizadorLogin)
SELECT dbo.stSubCategoria.SubCategoria, dbo.stSubCategoria.Comprador
FROM dbo.stSubCategoria LEFT OUTER JOIN
SubCategoriaUtilizador ON
dbo.stSubCategoria.SubCategoria = SubCategoriaUtilizador.SubCategoria
WHERE ((SELECT COUNT(*) AS Expr1
FROM dbo.stUtilizadorSubCategoria AS stUtilizadorSubCategoria_1 INNER JOIN
dbo.svUtilizadorLogin AS svUtilizadorLogin_1 ON stUtilizadorSubCategoria_1.UtilizadorLogin = svUtilizadorLogin_1.UtilizadorLogin) = 0) OR
(NOT (SubCategoriaUtilizador.SubCategoria IS NULL))
Execution plan for the original view is attached.
Here's new generic view t-sql, invoking TVF:
SELECT SubCategoria, Comprador FROM dbo.GetSubCategoriaComprador ()
Execution plan for the changed view invoking TVF is attached.
Here's TVF itself:
ALTER FUNCTION [dbo].[GetSubCategoriaComprador] ()
RETURNS
@SubCategoriaComprador TABLE
(
SubCategoria int,
Comprador int
)
WITH SCHEMABINDING
AS
BEGIN
Insert Into @SubCategoriaComprador
SELECT dbo.stSubCategoria.SubCategoria, dbo.stSubCategoria.Comprador
FROM dbo.stUtilizadorSubCategoria INNER JOIN
dbo.svUtilizadorLogin ON dbo.stUtilizadorSubCategoria.UtilizadorLogin = dbo.svUtilizadorLogin.UtilizadorLogin INNER JOIN
dbo.stSubCategoria ON dbo.stUtilizadorSubCategoria.SubCategoria = dbo.stSubCategoria.SubCategoria
If (Select Count(*) From @SubCategoriaComprador) = 0
Begin
Insert Into @SubCategoriaComprador
SELECT dbo.stSubCategoria.SubCategoria, dbo.stSubCategoria.Comprador
FROM dbo.stSubCategoria
End
RETURN
END
Original generic view takes 00:00:07 to output 710 rows in EM.
"TVF view" in generic view takes 00:00:00 to output 710 rows in EM.
One particular view that joins this generic view (original version) and some tables with thousands of rows, took
00:01:00 to output 914 rows in EM - 1º run.
One particular view that joins this generic view (original version) and some tables with thousands of rows, took
00:00:40 to output 914 rows in EM - 2º run.
One particular view that joins this generic view (original version) and some tables with thousands of rows, took
00:00:39 to output 914 rows in EM - 3º run.
The same particular view that joins this generic view (in TVF version) and some tables with thousands of rows, took
00:10:07 to output 914 rows in EM - 1º run.
The same particular view that joins this generic view (in TVF version) and some tables with thousands of rows, took
00:05:46 to output 914 rows in EM - 2º run.
The same particular view that joins this generic view (in TVF version) and some tables with thousands of rows, took
00:05:40 to output 914 rows in EM - 3º run.
Execution Plan follows attached for each scenario.
As i mentioned in my first post, my "TVF view" has great results (better results, at least) than original generic view in other views that are not too heavy (in terms of joining tables with large amount of data). On the other hand, when it comes to views with heavy joins, my "TVF view" has worse performance than original generic view.
Thanks in advance!
April 7, 2010 at 2:23 pm
just a quick first impression on the original view
I would rewrite that one to avoid as much IO as possible ... so use (not) exists as much as possible
WITH cteSubCategoriaUtilizador ( SubCategoria )
AS (
SELECT USC.SubCategoria
FROM dbo.stUtilizadorSubCategoria USC
INNER JOIN dbo.svUtilizadorLogin UL
ON USC.UtilizadorLogin = UL.UtilizadorLogin
group by USC.SubCategoria -- added by ALZDBA
)
SELECT SC.SubCategoria
, SC.Comprador
FROM dbo.stSubCategoria SC
where exists ( select *
from cteSubCategoriaUtilizador cte
where cte.SubCategoria = SC.SubCategoria )
or not exists ( select *
from cteSubCategoriaUtilizador )
Can you give this a try ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 7, 2010 at 2:28 pm
pnmm (4/7/2010)
Thanks for quick answers.About ITVF suggestion, it will not work, because my TVF has logic inside.
Is there a limit on the kind of query you an put in the ITVF? It looks like your view is just a plain select. Is there something I'm missing?
I don't use functions for this sort of thing much, so I'm curious..
EDIT: I missed the CTE in the view. But, that is easily replaced with a derived table.
April 7, 2010 at 2:45 pm
pnmm (4/7/2010)
About ITVF suggestion, it will not work, because my TVF has logic inside.
Anything you can do in a view (not including indexed views), you can generally do in an iTVF.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 7, 2010 at 2:54 pm
Just for the heck of it, try this in-line TVF. I'd be interested to know if it works.
ALTER FUNCTION [dbo].[GetSubCategoriaComprador] ()
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
WITH cteSubCategoria as (
SELECT
usc.SubCategoria,
usc.Comprador
FROM
dbo.stUtilizadorSubCategoria usc
INNER JOIN dbo.svUtilizadorLogin ul
ON usc.UtilizadorLogin = ul.UtilizadorLogin
INNER JOIN dbo.stSubCategoria sc
ON usc.SubCategoria = sc.SubCategoria
)
select
csc.SubCategoria,
csc.Comprador
from
cteSubCategoria csc
union all
SELECT
dbo.stSubCategoria.SubCategoria,
dbo.stSubCategoria.Comprador
FROM
dbo.stSubCategoria
where
not exists (select count(*) from cteSubCategoria)
April 8, 2010 at 8:56 am
Thanks a lot guys!
So, check out my conclusions after trying your suggestions.
1º Comment:
Lamprey13 and Jeff Moden: You were right, i could re-write my MTVF in order to use ITVF instead.
2º Comment:
Lynn Pettis, thanks for your help, but your approach to ITVF should be rewritten in order to output rows for all scenarios as:
WITH cteSubCategoria as (
SELECT
usc.SubCategoria,
sc.Comprador
FROM
dbo.stUtilizadorSubCategoria usc
INNER JOIN dbo.svUtilizadorLogin ul
ON usc.UtilizadorLogin = ul.UtilizadorLogin
INNER JOIN dbo.stSubCategoria sc
ON usc.SubCategoria = sc.SubCategoria
)
select
csc.SubCategoria,
csc.Comprador
from
cteSubCategoria csc
union all
SELECT
dbo.stSubCategoria.SubCategoria,
dbo.stSubCategoria.Comprador
FROM
dbo.stSubCategoria
where
not exists (select count(*) from cteSubCategoria having COUNT(*) > 0)
My first impression was to assume that your approach was very similar to the one pointed out by ALZDBA... But not.
I tried both scripts in generic view and tested with that view with heavy joins and guess what:
The view scripted by ALZDBA takes in average 28 secs. to output 914 rows and your t-sql takes 55 secs. in average to output same rows. I didn't try it with ITVF, 'cause assumed that wouldn't improve results.
3º Comment:
So the winner is ALZDBA's approach. I gave up from ITVF and MTVF - i actually tried out ITVF and performance was exactly the same when using generic view instead.
Only one thing ALZDBA: i skypped that "group by" in my generic view on purpose. dbo.svUtilizadorLogin only returns one row (corresponds to login's invoking user) and dbo.stUtilizadorSubCategoria joined to dbo.svUtilizadorLogin will only return distinct subcategories for that invoking/caller user. I should have explained that in previous post.
Nevertheless, it works with very reasonable performance!!! Better than it was, actually!
Thanks once again!
April 8, 2010 at 9:13 am
If you can do with the views why are you looking out for using functions? View should be fasted option.
April 8, 2010 at 9:29 am
Hello vidya_pande,
Maybe you didn't read completely the thread from the beginning.
I once had a generic view with some performance issues and tried out TVF in order to improve query result time.
My TVF was actually faster than my generic view in most cases, but in other views with heavy joins TVF query result time was really poor, even worse than view.
After some guidance and tests i realized that the view was the best solution, with some minor changes in the where clause.
PM
April 8, 2010 at 10:41 am
pnmm (4/8/2010)
Thanks a lot guys!So, check out my conclusions after trying your suggestions.
1º Comment:
Lamprey13 and Jeff Moden: You were right, i could re-write my MTVF in order to use ITVF instead.
2º Comment:
Lynn Pettis, thanks for your help, but your approach to ITVF should be rewritten in order to output rows for all scenarios as:
WITH cteSubCategoria as (SELECT
usc.SubCategoria,
sc.Comprador
FROM
dbo.stUtilizadorSubCategoria usc
INNER JOIN dbo.svUtilizadorLogin ul
ON usc.UtilizadorLogin = ul.UtilizadorLogin
INNER JOIN dbo.stSubCategoria sc
ON usc.SubCategoria = sc.SubCategoria
)
select
csc.SubCategoria,
csc.Comprador
from
cteSubCategoria csc
union all
SELECT
dbo.stSubCategoria.SubCategoria,
dbo.stSubCategoria.Comprador
FROM
dbo.stSubCategoria
where
not exists (select count(*) from cteSubCategoria having COUNT(*) > 0)
My first impression was to assume that your approach was very similar to the one pointed out by ALZDBA... But not.
I tried both scripts in generic view and tested with that view with heavy joins and guess what:
The view scripted by ALZDBA takes in average 28 secs. to output 914 rows and your t-sql takes 55 secs. in average to output same rows. I didn't try it with ITVF, 'cause assumed that wouldn't improve results.
3º Comment:
So the winner is ALZDBA's approach. I gave up from ITVF and MTVF - i actually tried out ITVF and performance was exactly the same when using generic view instead.
Only one thing ALZDBA: i skypped that "group by" in my generic view on purpose. dbo.svUtilizadorLogin only returns one row (corresponds to login's invoking user) and dbo.stUtilizadorSubCategoria joined to dbo.svUtilizadorLogin will only return distinct subcategories for that invoking/caller user. I should have explained that in previous post.
Nevertheless, it works with very reasonable performance!!! Better than it was, actually!
Thanks once again!
Please remember, my rewrite was done in a vaccuum. I had no tables, sample data, or expected results with which to test my code. I can only hope it was close and that any changes you made were appropriate.
Thanks for the feedback, it is appreciated.
April 8, 2010 at 11:09 am
Please remember, my rewrite was done in a vaccuum. I had no tables, sample data, or expected results with which to test my code. I can only hope it was close and that any changes you made were appropriate.
Lynn Pettis, i've never said anything different and i'm grateful for your help. In fact is interesting to verify that even though ALZDBA's view suggestion has one "Or" clause and despite your t-sql version of the view seemed cleaner with one Union All, the outcome didn't followed as expected.
I suppose Unions are really huge performance leak and should be avoided at all costs...
Thanks!
PM
April 8, 2010 at 11:20 am
pnmm (4/8/2010)
Please remember, my rewrite was done in a vaccuum. I had no tables, sample data, or expected results with which to test my code. I can only hope it was close and that any changes you made were appropriate.
Lynn Pettis, i've never said anything different and i'm grateful for your help. In fact is interesting to verify that even though ALZDBA's view suggestion has one "Or" clause and despite your t-sql version of the view seemed cleaner with one Union All, the outcome didn't followed as expected.
I suppose Unions are really huge performance leak and should be avoided at all costs...
Thanks!
PM
It may not be the UNION ALL that is the problem. It could be the code you added to my iTVF. My original code simply tested for the existance of any data in the cte query. The only way to know for sure would be to evaluate the actual execution plans for the various options you have to meet your requirements and see if they can be tuned to perform better.
April 9, 2010 at 12:08 am
Thank you for the feedback.
It's nice to see the different solutions and how they actually performed in your case.
IMHO in many cases, queries will have a big performance gain if you tell them exactly what you know.
In this case, if you know your cte will and should only return 0 or a single value, specify the top (1).
If chances are there are more then 1 value, specify the group by, to minimise its working set, but that should be tested.
As shown you should use the cte twice in the where clause, because in the original version, you coded a count(*) but sqlserver will not know to use the same memory space to do this existance check.
When using the cte, it knows it is the same working set, and reuses that.
Off course using "set statistics io on, set statistics time on" is a good ref during your performance analysis.
It's always nice if your solution comes out best :w00t:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply