Outer apply with OR conditions and XML Readers are slowing my query

RvRijbroek 2017-09-10 11:27:22

Edit2: I'm running MS SQL 2012 express

Hi guys,

I'm trying to speed up this query used to fill a planboard inside my application.
For that i'm using objects and Dates to create a sort of matrix.
For each combo of objects and dates (the latter based on the provided parameters from the client app) i'm searching plandcards (tbl_planning) along with their attributes (projects and Bestellingen [=orders]).

The slow part is in
1) the Bestellingen (PurchaseOrders and Lines) probably due to the OR conditions based on the date.
2) the XML that combines the attributes in one string (which contains HTML, and must be unencoded for use in the clientapplication).

Do you guys have any idea how to increase performance of this beast?

Many thanks.
Ron

Update 1: code snippet for 1):
Executed for each Object/Date/tbl_planning combination.

OUTER APPLY (
                                    SELECT
                                        Bestellingen = COUNT(DISTINCT po.Id)
                                        ,BestellingenBesteld = COUNT(DISTINCT CASE WHEN po.Besteld = -1 THEN po.Id ELSE NULL END)
                                    FROM
                                        po.tbl_PurchaseOrders AS po
                                        INNER JOIN po.tbl_PurchaseOrderLines AS pol ON
                                            po.Id = pol.POid
                                        INNER JOIN pa.tbl_Products AS pdct ON
                                            pol.ProductId = pdct.Id
                                        INNER JOIN pa.tbl_ProductGroups AS pg ON
                                            pdct.ProductGroupId = pg.Id
                                    WHERE
                                        (
                                            pol.Leverdatum = d.Date
                                            OR
                                            (d.date = p.DateStart AND pol.Leverdatum = DATEADD(dd, -1, p.DateStart)) -- Day before, but only if startdate
                                            OR
                                            (d.WeekDay2 = 1 AND d.date = p.DateStart AND pol.Leverdatum = DATEADD(dd, -3, p.DateStart)) -- Friday before startdate if startdate is monday
                                        )
                                        AND
                                        po.CDDOSSIER = pj.CDDOSSIER
                                        AND
                                        po.Administratie = pj.Administratie
                                        AND
                                        (
                                            pg.POtype IN (SELECT fs.Data FROM dbo.fn_Split(o.POtypes,',') AS fs)
                                            OR
                                            pdct.ProductGroupId IN (SELECT fs.Data FROM dbo.fn_Split(o.ProductGroups,',') AS fs)
                                        )
 
                                ) AS b