Hi,
I finally got that query. It's:
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 bigint,@P2 nvarchar(5),@P3 datetime2,@P4 datetime2,@P5 datetime2,@P6 datetime2,@P7 bigint,@P8 nvarchar(5)',N'SELECT T1.AGREEMENT,T1.ITEMCODE,T1.ACCOUNTCODE,T1.ITEMRELATION,T1.ACCOUNTRELATION,T1.QUANTITYAMOUNTFROM,T1.FROMDATE,T1.TODATE,T1.AMOUNT,T1.CURRENCY,T1.PERCENT1,T1.PERCENT2,T1.DELIVERYTIME,T1.SEARCHAGAIN,T1.PRICEUNIT,T1.RELATION,T1.QUANTITYAMOUNTTO,T1.UNITID,T1.MARKUP,T1.ALLOCATEMARKUP,T1.MODULE,T1.INVENTDIMID,T1.CALENDARDAYS,T1.GENERICCURRENCY,T1.DEL_PRICEINCLVAT,T1.TASTATUS,T1.QUOTATIONID,T1.PRIORITYPRICE,T1.DISREGARDLEADTIME,T1.MODIFIEDBY,T1.CREATEDDATETIME,T1.CREATEDBY,T1.RECVERSION,T1.PARTITION,T1.RECID,T2.INVENTDIMID,T2.INVENTBATCHID,T2.WMSLOCATIONID,T2.WMSPALLETID,T2.INVENTSERIALID,T2.INVENTLOCATIONID,T2.CONFIGID,T2.INVENTSIZEID,T2.INVENTCOLORID,T2.INVENTSITEID,T2.INVENTSTYLEID,T2.RECVERSION,T2.PARTITION,T2.RECID FROM PRICEDISCTABLE T1 CROSS JOIN INVENTDIM T2 WHERE (((T1.PARTITION=@P1) AND (T1.DATAAREAID=@P2)) AND (((T1.FROMDATE<@P3) OR (T1.FROMDATE=@P4)) AND ((T1.TODATE>@P5) OR (T1.TODATE=@P6)))) AND (((T2.PARTITION=@P7) AND (T2.DATAAREAID=@P8)) AND (T1.INVENTDIMID=T2.INVENTDIMID)) ORDER BY T1.RELATION,T1.ACCOUNTCODE,T1.ACCOUNTRELATION,T1.CURRENCY,T1.ITEMCODE,T1.ITEMRELATION,T1.UNITID,T1.QUANTITYAMOUNTFROM,T1.FROMDATE,T1.AGREEMENTHEADEREXT_RU OPTION(FAST 20)',@p5 output,@p6 output,@p7 output,5637144576,N'agl','2015-01-31 00:00:00','2015-01-31 00:00:00','2015-01-31 00:00:00','2015-01-31 00:00:00',5637144576,N'agl'
select @p1, @p2, @p5, @p6, @p7
When I execute it in SSMS I takes 2.45 and I get the following plan:
I'm not sure about the FAST 20 as I only have 6 cores in my server and only 4 are used by SQL. If I remove the FAST 20 and run the query in SSMS it executes in 11 seconds and produces the below plan:
Whilst this is a big improvement I cant help but feel the plan is still inefficient. Would anybody agree?
Cheers
Paul