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

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
Plan.pesession (36.0 MB)
avatar image By RvRijbroek 1 asked Sep 10 at 11:27 AM
more ▼
(comments are locked)
10|10000 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x641
x455
x17
x16

asked: Sep 10 at 11:27 AM

Seen: 19 times

Last Updated: Sep 10 at 11:32 AM