Cross Applying Table Valued Function taking too much time

Question

;WITH cte AS
(
    SELECT DISTINCT 
        a.ID, a.code AS code, DATEPART(wk, INVCDATE) weekno, 
        (SELECT CONVERT(DATE, (SELECT CONVERT(DATEtime, v.invcdate)  - DATEPART(dw, CONVERT(varchar, CONVERT(DATE, v.invcdate)))) + 1)) MinDateOfWeek,
        (SELECT CONVERT(DATE, (SELECT CONVERT(DATEtime, v.invcdate) + (7 - DATEPART(dw, CONVERT(varchar, CONVERT(DATE, v.invcdate))))))) ADA
    FROM 
        dbo.abc a 
    INNER JOIN 
        dbo.xyz b ON a.ID = b.ID
    INNER JOIN 
        dbo.PLIST p ON a.ID = p.ID
    INNER JOIN 
        dbo.VISITS v ON p.ID2 = v.ID2
    INNER JOIN 
        dbo.UserList o ON o.ID = a.ID
)
SELECT 
    code, weekno, k.Id4, k.Id3, k.Id2, k.Id1, k.Id0 
FROM 
    cte 
CROSS APPLY 
    dbo.f_getmycount(ID, MinDateOfWeek, ADA) k

The above query is taking too much time.

I need suggestions that cross applying table valued function is efficient or not.

Any help is appreciated.

Thanks

What kind of function is `f_getmycount()`? A scalar function, inline table-valued function, or multi-statement table-valued function? Based on usage, I'd guess it's not scalar, but I've been surprised before. :) — Ben Thul, Feb 20 '17 at 07:23
Show the (reduced?) code of your function and provide some details about your tables and indexes. Did you try to analyse the execution plan already? — Shnugo, Feb 20 '17 at 07:40
Hi Ben, Its a table valued function. @Shnugo - I have tried applying indexes and changes are done as per the execution plan inputs. Still it is getting more time. — jitu, Feb 20 '17 at 09:24
Hi jitu, there are two flavors of TVF. One is good, the other one is bad. **Show the (reduced?) code of your function** — Shnugo, Feb 20 '17 at 11:16

score 0 · Answer 1 · edited May 23 '17 at 12:16

0

I found searching with Google, that inline table valued function is useful instead of multi-statement TVF. (below links)

Multi-statement Table Valued Function vs Inline Table Valued Function

Doing so, made the result look great.

Thanks

edited May 23 '17 at 12:16

Community

1
1

answered Feb 20 '17 at 16:05

jitu

25
2

Cross Applying Table Valued Function taking too much time

1 Answers1