SQL Server Performance

Performance of CROSS APPLY

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Arhamg, Jul 31, 2008.

  1. Arhamg New Member

    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.
  2. moh_hassan20 New Member

    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 [;)]
  3. Arhamg New Member

    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.
  4. moh_hassan20 New Member

    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

Share This Page