making this more sargable?

[sapator]

SELECT	OrderH_strVistaBookingId AS BookingId,
			OrderH_intVistaBookingNumber As BookingNumber,
			OrderH_strCard As CCNumber,
			OrderH_strCinemaId As CinemaCode,
			COALESCE(CI.Name, C.Cinema_StrName) As CinemaName,
			OrderH_strCustomerId As MemberId,
			OrderH_intOrderValueinCents As TotalAmount,
			OrderH_dtmInitiated As OrderDate,
			ETicketTemplateFolder = P.ETicketTemplateFolder,
			PB.Code
	FROM	VISTAIT.dbo.tblOrderHistory OH
			JOIN VISTAIT.dbo.tblCinema C ON OH.OrderH_strCinemaId = C.Cinema_strID
			LEFT JOIN Cinema CI ON CI.Code = C.Cinema_strID
			LEFT JOIN Partners P ON P.ClientID = OH.OrderH_strClientId
			LEFT JOIN PartnerTransactions PT ON REPLACE(PT.SessionId,'-','') = OH.OrderH_strUserId AND IsCancel = 0 
			LEFT JOIN PartnersBranches PB ON PB.Id = PT.PartnerBranchId
	WHERE	((@UserSessionId IS NULL AND @MemberId IS NOT NULL AND CONVERT(VARCHAR,OrderH_strCustomerId) = @MemberId) OR
				(@UserSessionId IS NOT NULL AND @MemberId IS NULL AND @BookingId = OH.OrderH_strVistaBookingId and OH.OrderH_strCinemaId = @Cinemaid) )
            AND OH.OrderH_strOrderMoved = 'Y' AND OH.OrderH_strCancelled = 'N'
			AND (@Minutes = -1 
                    OR EXISTS(SELECT * FROM VISTAIT.dbo.tblOrderTicketHistory OTH 
                            WHERE OTH.OrderH_intID = OH.OrderH_intID AND OrderTH_dtmSessionDateTime >= @Date)
                    OR EXISTS(SELECT * FROM VISTAIT.dbo.tblOrderInventoryHistory OIH 
                            INNER JOIN VISTAIT.dbo.tblOrderHistory OIH_O ON OIH_O.OrderH_intID = OIH.OrderH_intID 
                            WHERE ISNULL(OIH.OrderIH_strParentTTypeCode,'') = ''
                            AND OIH.OrderH_intID = oh.OrderH_intID
                            AND CONVERT(DATE, OIH_O.OrderH_dtmInitiated) >=  CONVERT(DATE, DATEADD(dd, -@DaysToPickup, GETDATE())) )
                )
            AND NOT EXISTS (SELECT * FROM VISTAIT.dbo.ZZ_OrdersPickedUp WHERE ZZ_OrdersPickedUp.OrderH_intID = OH.OrderH_intID)

HELP!!!!



Kiddin. Mssql 2008. So I have this blast from the past. I can't really index the database so I'm trying to make this as sargable as I can. First I did remove the conversions to date but the execution plan did not change that much(did not remove the varchar conversion). I'm not sure about the isnull and COALESCE . Also I'm guessing the select * in the EXISTS clause might slowing this down but not sure, and the OrderH_intID compares are integers.
Also not sure about OH.OrderH_strOrderMoved = 'Y' AND OH.OrderH_strCancelled = 'N' , the execution plan suggest to index these but is this such a drawback? There just 1 character and not with a "like" clause


Any thought? Thanks

[szlamany]

SELECT * in EXISTS is ignored - EXISTS only runs the WHERE clause logic.

When presented with a complex WHERE clause like this - with OR's and what not - I usually change the attack plan to instead build a TEMP TABLE with KEY's to be pulled.

I build this table initially with the FASTEST way to "get" the majority of the "keys in consideration". There will be more keys then I need at this point.

I then remove keys - DELETE - from this TEMP TABLE using other parts of the original WHERE clause that are more clunky - with ISNULL's and sister tables joins, for instance.

The goal is to eliminate from consideration those aspects of the WHERE clause that are expensive - eliminate them from the "whole population".

[sapator]

Hi.
Not sure what to temp and what not to temp here.
I could try to build a tempt table with the initial joins for a finite amount of time and see how it goes but I think I have tried that in the past (a year or so ago) and it actually made things worst.
I don't think I have the proper SQL knowledge to break this down so easily (as I'm actually a .net programmer, well, errr, tell that to the company, but still) as you suggest but I would try to give it a shoot when I have time. A kick start would be appreciated though as I'm not sure getting a temp as mention before is what you have in mind.
Thanks.

Edit: LEFT JOIN PartnerTransactions that was joining with about a million rows and was hitting the db like a hammer. So although I don't really have a problem now, would be glad to know how I could temp the db.

[szlamany]

You can identify the problem JOIN easily by commenting out ONE join at a time and seeing when the execute-time changes dramatically.

Of course you would need to eliminate from the SELECT LIST any of those columns that are in the joined table that you comment out.

For sake of a quick discussion here, let's assume that its that PartnerTransactions table that you are hitting millions of times.

Can you comment out that table now and see how the query runs? I do see that another JOIN relies on it - so they are both going...

How many ACTUAL ROWS does the final query pull from this table?

[sapator]

Hi.
I'll see to it next year.Have a great new year!

[szlamany]

My Italian half says Buon Capodanno!

My Czech half says Šťastný nový rok!

I wonder if those Czech words have Greek roots??

My Czech half would more proudly declare Veselého Silvestra! for New Years Eve since St Silvester is a patron saint of Prague (I hear they have one hell of a party in Praha on New Years Eve!)