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.

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

                                        Bestellingen = COUNT(DISTINCT po.Id)
                                        ,BestellingenBesteld = COUNT(DISTINCT CASE WHEN po.Besteld = -1 THEN po.Id ELSE NULL END)
                                        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
                                            pol.Leverdatum = d.Date
                                            (d.date = p.DateStart AND pol.Leverdatum = DATEADD(dd, -1, p.DateStart)) -- Day before, but only if startdate
                                            (d.WeekDay2 = 1 AND d.date = p.DateStart AND pol.Leverdatum = DATEADD(dd, -3, p.DateStart)) -- Friday before startdate if startdate is monday
                                        po.CDDOSSIER = pj.CDDOSSIER
                                        po.Administratie = pj.Administratie
                                            pg.POtype IN (SELECT fs.Data FROM dbo.fn_Split(o.POtypes,',') AS fs)
                                            pdct.ProductGroupId IN (SELECT fs.Data FROM dbo.fn_Split(o.ProductGroups,',') AS fs)
                                ) AS b