Hi, i have a query like Select Col1 , col2 , dbo.func1(col3), dbo.func2(col4) from table1 t1 inner join table2 t2 on t1.col = t2.col I'm plannig to use CROSS APPLY instead of calling those functions because as per my understanding CROSS APPLY calls the UDF only when the input value changes means when the input value is different from the last one it calls the UDF else it does not. For eg. if in 10 rows i have first 5 rows with same col3 val and rest 5 rows with same value then it'll call func1 only 2 times, i save on 8 function calls. What's my area of concern is that how's the performance of CROSS APPLY ie to save those calls i'm not killing the cpu etc.? Tthe actuall query is a lot bigger and returns approx 10,000 rows each time so a gain of 10-20 % is also welcome.
Cross apply is used for inner join to a valued function which return table and can take parameters. This is new for sql 2005 , and that join operation is not possible to do using regular JOIN syntax in sql 2000 , sql 7. [quote user="Arhamg"] Select Col1 , col2 , dbo.func1(col3), dbo.func2(col4) from table1 t1 inner join table2 t2 on t1.col = t2.col [/quote] func1 , func2 in your query are returning simple data type (one value ) not Table datatype So , keep your query as is , no need for cross apply []
Thnx for your reply Hassan. I want to use CROSS APPLY because currently i have some 5-6 func calls in my query and as I found out the function in CROSS APPLY is called only for distinct values and currently i have 5 * 8000 = 40000 func calls approx. in my query.So if i could bring it down by say 10-15% by using CROSS APPLY then it would great help for me.
pls, can you post your cross apply query to understand your approach, especially as i know that cross apply is used with table function or table result set