SQLTeam.com | Weblogs | Forums

Help on non sargable query + group by


#1

HI.

select

sum (ISNULL(TransI_decNoOfItems,0)) AS Quantity
,sum(CAST(ISNULL(TransI_curValueEach,0)*ISNULL(TransI_decNoOfItems,0) AS MONEY))  AS GrossValue
 ,ISNULL(BH.BookingH_strPickupWorkstn,I.Workstation_strCode) as workstationcode
 ,BH.BookingH_intNextBookingNo
 ,sum( CAST(ISNULL(TransI_curValueEach,0)*ISNULL(TransI_decNoOfItems,0) -
	            ((ISNULL(TransI_curSTaxEach,0)+
		          ISNULL(TransI_curSTaxEach2,0)+
		          ISNULL(TransI_curSTaxEach3,0)+
		          ISNULL(TransI_curSTaxEach4,0))*ISNULL(TransI_decNoOfItems,0)) AS MONEY))  AS NetValue

 from tblTrans_Inventory I
  INNER JOIN VISTA.dbo.tblItem IT
	    ON IT.Item_strItemId = I.Item_strItemId
    INNER JOIN VISTA.dbo.tblSalesTax 
        ON tblSalesTax.STax_strCode = IT.STax_strCode

    LEFT JOIN ZZ_tblTrans_Inventory_EXT BI
        ON  I.TransI_lgnNumber   = BI.TransI_lgnNumber
        AND I.TransI_intSequence = BI.TransI_intSequence
    LEFT JOIN tblBooking_Header BH ON BH.TransC_lgnNumber = I.TransI_lgnNumber
	LEFT JOIN tblItem_Class IC
					ON	IC.Class_strCode = IT.Class_strCode 

                LEFT JOIN tblWorkstation W
					ON	W.Workstation_strCode = ISNULL(BI.TransI_strPickupWorkstn,I.Workstation_strCode)

--where I.TransI_strType = 'S' --if like so we do not get refunded items
          where IT.Item_strBookingFee <> 'Y'                              --Exclude Booking Fees
          AND ISNULL(BI.TransI_strPickupWorkstn,'') NOT LIKE '%UNRED%' 		

		
and 
CASE WHEN ISNULL(I.TransI_decNoOfItems,0) < 0 THEN I.TransI_dtmRealTransTime --Refund
                 ELSE CASE WHEN BH.BookingH_intNextBookingNo IS NULL THEN I.TransI_dtmRealTransTime ELSE I.TransI_dtmDateCollected END
            END between '20170902 06:00:00' and '20171011 05:59:59'
	

			and CASE 
	        WHEN ISNULL(TransI_decNoOfItems,0) < 0 THEN 'C'  --Refund
	        ELSE CASE TransI_strStatus WHEN 'R' THEN 'R' ELSE 'V' END
	        END  = 'V' --Transaction Status C:Refund, V:Valid, R:Refunded			

			-- remove inline recipre
			and TransI_curValueEach > 0
			group by BH.BookingH_strPickupWorkstn,ISNULL(BH.BookingH_strPickupWorkstn,I.Workstation_strCode),BH.BookingH_intNextBookingNo

Hi.
First a quick question.
Am I correct to group by ISNULL(BH.BookingH_strPickupWorkstn,I.Workstation_strCode) as per the select statement?
When I group by only by I.Workstation_strCode I get the same results but I do not group on a broad range right now.

OK Next. I get A table scan on tbltrans_inventory and a missing query.
As I cannot temper with the database, I cannot insert any indexes, so I was hoping to somehow make the cast better.

An problem may be the like '%unread%' but unfortunately I cannot remove the beginning % . However when I remove the like , I get the same execution table scan.

I also removed the casts on the select clause and the execution plan breaks down to index scans on tbltrans_inventory.

But from what I can see the problem seems to originate from the 2 case on the and statement.

and 
CASE WHEN ISNULL(I.TransI_decNoOfItems,0) < 0 THEN I.TransI_dtmRealTransTime --Refund
                 ELSE CASE WHEN BH.BookingH_intNextBookingNo IS NULL THEN I.TransI_dtmRealTransTime ELSE I.TransI_dtmDateCollected END
            END between '20170902 06:00:00' and '20171011 05:59:59'
	

			and CASE 
	        WHEN ISNULL(TransI_decNoOfItems,0) < 0 THEN 'C'  --Refund
	        ELSE CASE TransI_strStatus WHEN 'R' THEN 'R' ELSE 'V' END
	        END  = 'V' --Transaction Status C:Refund, V:Valid, R:Refunded		

Lastly, let me say that the query does not take a long time to execute but I guess this may have to do with the new servers we have installed a month ago. So this is not actually a very huge problem but I would appreciate some thoughts, as we will be running this also on older servers from time to time.

Thanks.

Images: