dcsimg
Results 1 to 6 of 6

Thread: making this more sargable?

  1. #1

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,505

    making this more sargable?

    Code:
    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
    Slow as hell.

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,838

    Re: making this more sargable?

    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".

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,505

    Re: making this more sargable?

    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.
    Last edited by sapator; Dec 28th, 2018 at 06:13 AM.
    Slow as hell.

  4. #4
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,838

    Re: making this more sargable?

    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?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  5. #5

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,505

    Re: making this more sargable?

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

  6. #6
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,838

    Re: making this more sargable?

    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!)

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width