Group by and also a sargable question.

[sapator]

Hi.

  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

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.


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.

[techgnome]

AND ISNULL(BI.TransI_strPickupWorkstn,'') NOT LIKE '%UNRED%'

"UNRED" or "UNREAD"?? the code has "UNRED" as in "not red" but your comments are "UNREAD" as in "I haven't read it" .... normally I'd chalk it up to a typo, but I doubt your code is typed in by hand, it looks copy/paste to me, which means it is probably like that in production. Not that it's a source of any issues, other than you're not getting results you should be getting.

That said...

Am I correct to group by ISNULL(BH.BookingH_strPickupWorkstn,I.Workstation_strCode) as per the select statement?

I believe so... it's going to be grouping by the results of the expression, so if it is a non-aggregated expression in the select, it should be part of the group by. The only time I've found that I don't need to include non-aggregated fields from the select in my group by is when they are static values: variables, hard-coded text or numbers, things that are not going to change from row to row.

I'm not sure about the rest.

-tg

[sapator]

Yes we use a pattern from the initial code and I put stuff in.

Unred as Unredeemed