none
optimize of Select query RRS feed

  • Question

  • Hi ,

    I have few queries to optimize. I used sql sentry tool but not getting result. Still query is slow.

    Below is the query

    --------------------------------------

    1.SELECT top 1000
    [Extent2].[TotalKioskBags] AS [TotalKioskBags],
    [Extent1].[SBDTransactionID] AS [SBDTransactionID],
    [Extent1].[TransactionStartTime] AS [TransactionStartTime],
    [Extent1].[TransactionEndTime] AS [TransactionEndTime],
    [Extent1].[TransactionDateTime] AS [TransactionDateTime],
    [Extent3].[CarrierCode] AS [CarrierCode],
    [Extent2].[SBDTransactionPassengerID] AS [SBDTransactionPassengerID],
    [Extent2].[FirstName] AS [FirstName],
    [Extent2].[LastName] AS [LastName],
    [Extent3].[FlightNumber] AS [FlightNumber],
    [Extent3].[STD] AS [STD],
    [Extent3].[DeparturePortCode] AS [DeparturePortCode],
    [Extent1].[PNR] AS [PNR],
    [Extent4].[Delivered] AS [Delivered],
    [Extent4].[BagTagNumber] AS [BagTagNumber],
    [Extent1].[TransactionType] AS [TransactionType],
    [Extent1].[MachineName] AS [MachineName]
    FROM [dbo].[SBDTransaction] AS [Extent1]
    INNER JOIN [dbo].[SBDTransactionPassengers] AS [Extent2] ON [Extent1].[SBDTransactionID] = [Extent2].[SBDTransactionID]
    INNER JOIN [dbo].[SBDTransactionFlights] AS [Extent3] ON [Extent1].[SBDTransactionID] = [Extent3].[SBDTransactionID]
    INNER JOIN [dbo].[SBDTransactionBags] AS [Extent4] ON [Extent2].[SBDTransactionPassengerID] = [Extent4].[SBDTransactionPassengerID]
    WHERE ([Extent1].[TransactionDateTime] >= '2017-03-21 00:00:00')
    AND ([Extent1].[TransactionDateTime] <= '2017-04-21 00:00:00')
    AND ([Extent3].[DeparturePortCode] = 'OOL')
    AND ([Extent3].[CarrierCode] = 'JQ')

    I have created non-cluster Index using sql sentry tool. Previously it took 21 sec to execute , after adding Non cluster Index it took 19 sec. So there is not much difference. Still its very slow.

    Sql sentry tool

    ---------------------

    2.SELECT
    1 AS [C1],
    [Extent1].[KioskTransactionID] AS [KioskTransactionID],
    [Extent1].[ResultDescription] AS [ResultDescription],
    [Extent1].[TransactionDateTime] AS [TransactionDateTime],
    [Extent1].[HasReturnFlight] AS [HasReturnFlight],
    [Extent1].[StartTime] AS [StartTime],
    [Extent1].[EndTime] AS [EndTime],
    [Extent1].[TransactionType] AS [TransactionType],
    [Extent1].[CarrierCode] AS [CarrierCode],
    [Extent1].[PortCode] AS [PortCode],
    [Extent1].[KioskID] AS [KioskID],
    [Extent1].[RequestedBagCount] AS [RequestedBagCount],
    [Extent2].[FlightNumber] AS [FlightNumber],
    [Extent3].[KioskTransactionPassengerID] AS [KioskTransactionPassengerID],
    [Extent4].[KioskTransactionBagsID] AS [KioskTransactionBagsID]
    FROM [dbo].[KioskTransactions] AS [Extent1]
    INNER JOIN [dbo].[KioskTransactionFlights] AS [Extent2] ON [Extent1].[KioskTransactionID] = [Extent2].[KioskTransactionID]
    INNER JOIN [dbo].[KioskTransactionsPassengers] AS [Extent3] ON [Extent1].[KioskTransactionID] = [Extent3].[KioskTransactionID]
    LEFT OUTER JOIN [dbo].[KioskTransactionBags] AS [Extent4] ON [Extent1].[KioskTransactionID] = [Extent4].[KioskTransactionID]
    WHERE
    (
    NOT (
    (N'Failure' = [Extent1].[TransactionResult]) AND ([Extent1].[TransactionResult] IS NOT NULL)
    )
    )
    AND
    (
    [Extent1].[TransactionDateTime] >= '2017-05-02')
    AND ([Extent1].[TransactionDateTime] <= '2017-06-01')
    AND (
    ([Extent1].[CarrierCode] = 'JQ')
    )
    AND (
    ([Extent1].[PortCode] = 'OOL')
    )

    SQL Plan

    ----------------------------------

    3.SELECT top 1000
    1 AS [C1],
    [Filter1].[STD1] AS [STD],
    [Filter1].[CarrierCode1] AS [CarrierCode],
    [Filter1].[DeparturePort] AS [DeparturePort],
    [Filter1].[FlightNumber1] AS [FlightNumber],
    CASE WHEN ([Filter1].[HasReturnFlight] IS NULL) THEN cast(0 as bit) ELSE [Filter1].[HasReturnFlight] END AS [C2],
    [Filter1].[BagNumber] AS [BagNumber],
    [Filter1].[KioskTransactionPassengerID1] AS [KioskTransactionPassengerID],
    [Extent6].[SBDTransactionID] AS [SBDTransactionID],
    [Extent6].[SBDTransactionPassengerID] AS [SBDTransactionPassengerID],
    [Extent6].[SBDTransactionBagsID] AS [SBDTransactionBagsID]
    FROM (
    SELECT [Extent1].[CarrierCode] AS [CarrierCode1], [Extent1].[FlightNumber] AS [FlightNumber1], [Extent1].[STD] AS [STD1], [Extent1].[DeparturePort] AS [DeparturePort], [Extent2].[CarrierCode] AS [CarrierCode2], [Extent2].[PortCode] AS [PortCode], [Extent2].[HasReturnFlight] AS [HasReturnFlight], [Extent3].[KioskTransactionPassengerID] AS [KioskTransactionPassengerID1], [Extent4].[BagNumber] AS [BagNumber], [Extent5].[SBDTransactionID] AS [SBDTransactionID]
    FROM [dbo].[KioskTransactionFlights] AS [Extent1]
    INNER JOIN [dbo].[KioskTransactions] AS [Extent2] ON [Extent1].[KioskTransactionID] = [Extent2].[KioskTransactionID]
    INNER JOIN [dbo].[KioskTransactionsPassengers] AS [Extent3] ON [Extent1].[KioskTransactionID] = [Extent3].[KioskTransactionID]
    INNER JOIN [dbo].[KioskTransactionBags] AS [Extent4] ON [Extent3].[KioskTransactionPassengerID] = [Extent4].[KioskTransactionPassengerID]
    LEFT OUTER JOIN [dbo].[SBDTransactionFlights] AS [Extent5] ON ([Extent1].[STD] = [Extent5].[STD]) AND ([Extent1].[CarrierCode] = [Extent5].[CarrierCode]) AND ([Extent1].[FlightNumber] = [Extent5].[FlightNumber]) AND ([Extent1].[DeparturePort] = [Extent5].[DeparturePortCode])
    WHERE NOT ((N'Failure' = [Extent2].[TransactionResult]) AND ([Extent2].[TransactionResult] IS NOT NULL))
    AND ([Extent1].[STD] >= '2017-05-28 00:00:00') AND ([Extent1].[STD] <= '2017-05-29 00:00:00')
    ) AS [Filter1]
    LEFT OUTER JOIN [dbo].[SBDTransactionBags] AS [Extent6] ON [Filter1].[SBDTransactionID] = [Extent6].[SBDTransactionID]
    WHERE (([Filter1].[CarrierCode2] = 'JQ') OR (([Filter1].[CarrierCode2] IS NULL) AND ('JQ' IS NULL)) OR ('JQ' IS NULL) OR (( CAST(LEN('JQ') AS int)) = 0)) AND (([Filter1].[PortCode] = '') OR (([Filter1].[PortCode] IS NULL) AND ('' IS NULL)) OR ('' IS NULL) OR (( CAST(LEN('') AS int)) = 0)) AND ([Extent6].[Delivered] = 1 OR [Extent6].[Delivered] IS NULL)

    SQL Plan

    ------------------------

    Please help me out.

    Thanks,

    Meena K

    Friday, June 9, 2017 7:02 AM

All replies

  • I only looked at the first query. I noticed this:

    FROM [dbo].[SBDTransaction] AS [Extent1]
    INNER JOIN [dbo].[SBDTransactionPassengers] AS [Extent2] ON [Extent1].[SBDTransactionID] = [Extent2].[SBDTransactionID]
    INNER JOIN [dbo].[SBDTransactionFlights] AS [Extent3] ON [Extent1].[SBDTransactionID] = [Extent3].[SBDTransactionID]

    I don't know the tables, but I assume that SBDTransactionID is the primary key in SBDTransaction. How many rows can there be per SBDTransactionID in SBDTransactionPassengers? In SBDTransactionFlights?

    While the graphics say something, it is better to upload the XML for the actual query plans somewhere (Dropbox, Google drive etc) and post the link, since there is a wealth of information in the XML.

    AND ([Extent3].[DeparturePortCode] = 'OOL')

    AND ([Extent3].[CarrierCode] = 'JQ')

    I assume that you replaced the original variables with literals to be able to tune the queries from SSMS? Beware that there is quite a difference to work with constants instead of variables, as constants permits the optimizer to make shortcuts which is not possible with parameters. This applies particularly to the other queries.

    Friday, June 9, 2017 8:13 AM
  • Try following things:

    1. Create clustered index on all tables

    2. Create some non-clustered indexes on columns which are used in the WHERE clauses

    3. In the last execution plan, there is an index suggestion(Warning icon in the SELECT column). Consider creating that

    4. Update statistics

    https://docs.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql

    5. Make sure you retrieve required columns only in the WHERE clause

    6. Rebuild indexes to avoid fragmentation

    https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes

    Friday, June 9, 2017 11:38 AM
  • Hi,

    Have attached XML files. Below is the drop box link

    https://www.dropbox.com/s/mdywsr86bsxl7st/Script1_Xml.txt?dl=0

    Thanks,

    Meena

    • Edited by ken Myer Tuesday, June 13, 2017 5:15 AM
    Tuesday, June 13, 2017 5:09 AM
  • I will see if I can find the time tonight to look at the plans. However, you did not answer this question:

    I don't know the tables, but I assume that SBDTransactionID is the primary key in SBDTransaction. How many rows can there be per SBDTransactionID in SBDTransactionPassengers? In SBDTransactionFlights?

    This is very important for me to know.

    Tuesday, June 13, 2017 7:30 AM
  • But that was only the estimated plan. And the row counts in the estimated plan does not match up the execution times you indicate, so I think we need to see the actual execution plan to get better idea.

    I did notice one funny thing: the SBDTransaction comes into the plan fairly late, and since is the one with a filter that could be selective that does not seem too good.

    What happens if you remove the TOP 1000? (You could test with SELECT INTO a temp table to avoid drowning SSMS in result rows.)

    Tuesday, June 13, 2017 8:36 PM
  • Hi,

    Sorry for that.Below is the answer to your question.

    In SBDTransaction tables, SBDTransactionID has  IX_SBDTransaction_SBDTransactionID (Unique ,Non-clustered) not primary key.

    For SBDTransactionPassengers we have multiple row and SBDTransactionFlights also have multiple rows.

    This is what I understood from Apps team.

    Let me know if you need any other info.

    Thanks,

    Ms

    Wednesday, June 14, 2017 1:30 AM
  • Sorry I haven't looked at the xml query plans to see the volume of data you're dealing with per table, however have you considered filtering some of the data into a temp table / table variable prior to being joined on in the main queries?

    Example of this for the first query might look like the below:

    --Adjust data types as needed
    DECLARE @tv_trans TABLE
    (
        [SBDTransactionID] INT PRIMARY KEY NOT NULL
       ,[TransactionStartTime] DATETIME
       ,[TransactionEndTime] DATETIME
       ,[TransactionDateTime] DATETIME
       ,[PNR] VARCHAR(50)
       ,[TransactionType] INT
       ,[MachineName] VARCHAR(50)
    );
    
    INSERT INTO @tv_trans
    SELECT [SBDTransactionID]
          ,[TransactionStartTime]
          ,[TransactionEndTime]
          ,[TransactionDateTime]
          ,[PNR]
          ,[TransactionType]
          ,[MachineName]
    FROM [dbo].[SBDTransaction]
    WHERE [TransactionDateTime] >= '2017-03-21 00:00:00'
          AND [TransactionDateTime] <= '2017-04-21 00:00:00';
    
    SELECT
        [Extent2].[TotalKioskBags]            AS [TotalKioskBags]
       ,[Extent1].[SBDTransactionID]          AS [SBDTransactionID]
       ,[Extent1].[TransactionStartTime]      AS [TransactionStartTime]
       ,[Extent1].[TransactionEndTime]        AS [TransactionEndTime]
       ,[Extent1].[TransactionDateTime]       AS [TransactionDateTime]
       ,[Extent3].[CarrierCode]               AS [CarrierCode]
       ,[Extent2].[SBDTransactionPassengerID] AS [SBDTransactionPassengerID]
       ,[Extent2].[FirstName]                 AS [FirstName]
       ,[Extent2].[LastName]                  AS [LastName]
       ,[Extent3].[FlightNumber]              AS [FlightNumber]
       ,[Extent3].[STD]                       AS [STD]
       ,[Extent3].[DeparturePortCode]         AS [DeparturePortCode]
       ,[Extent1].[PNR]                       AS [PNR]
       ,[Extent4].[Delivered]                 AS [Delivered]
       ,[Extent4].[BagTagNumber]              AS [BagTagNumber]
       ,[Extent1].[TransactionType]           AS [TransactionType]
       ,[Extent1].[MachineName]               AS [MachineName]
    FROM @tv_trans AS [Extent1]  --Replace dbo.SBDTransaction with filtered data
        INNER JOIN [dbo].[SBDTransactionPassengers] AS [Extent2] ON [Extent1].[SBDTransactionID] = [Extent2].[SBDTransactionID]
        INNER JOIN [dbo].[SBDTransactionFlights] AS [Extent3] ON [Extent1].[SBDTransactionID] = [Extent3].[SBDTransactionID]
        INNER JOIN [dbo].[SBDTransactionBags] AS [Extent4] ON [Extent2].[SBDTransactionPassengerID] = [Extent4].[SBDTransactionPassengerID]
    WHERE ([Extent3].[DeparturePortCode] = 'OOL')
          AND ([Extent3].[CarrierCode] = 'JQ');


    Wednesday, June 14, 2017 3:55 AM
  • Sorry for that.Below is the answer to your question.

    In SBDTransaction tables, SBDTransactionID has  IX_SBDTransaction_SBDTransactionID (Unique ,Non-clustered) not primary key.

    For SBDTransactionPassengers we have multiple row and SBDTransactionFlights also have multiple rows.

    I suspected that. And then I say that the problem is that the query is most likely incorrectly written.

    If you have

    FROM A
    JOIN B ON A.ID = B.ID
    JOIN C ON A.ID = C.ID

    And for a given ID in A, there are three rows in B and five rows in B, you get fifteen rows back altogether with all possible combos of A and C rows. There are situations where this make sense, but I can see any of the patterns where this fits in here.

    They have masked the row explosion with the TOP 1000, but under the covers you probably get an insane number of rows because of the cartesian join. (Or did you add the "top 1000"; I notice that "top" is in lowercase and LINQ is usually good at putting the keywords in uppercase.)

    So I think you should take this back to the apps team and ask what this is all about.

    Wednesday, June 14, 2017 7:26 AM
  • Sorry I haven't looked at the xml query plans to see the volume of data you're dealing with per table, however have you considered filtering some of the data into a temp table / table variable prior to being joined on in the main queries?

    No, he can't, because this query is not authored in T-SQL, but is generated by LINQ for Entity Framework. In any case, as I noted in my other post, the query is likely to be flawed anyway.

    Wednesday, June 14, 2017 7:27 AM
  • Hi Gurus,

    Then how to fix this query slowness issue?

    I have created all index maintenance jobs and try to drop and recreate index. Still there is slowness issue. This query takes 19sec to execute.

    Do I need to check other parameters like partition tables , degree of parallelism set to default?

    One more problem with server slowness also. This server is used to Reporting purpose and for archive data.

    Memory:16384MB

    CPU:8 core 2.6GHZ and Opteron 6282 SE

    Any suggestions?

    Thanks,

    MK


    • Edited by ken Myer Thursday, June 15, 2017 12:07 AM
    Thursday, June 15, 2017 12:05 AM
  • Then how to fix this query slowness issue?

    Crafting a correct query could be a start. I understand that you are not part of the group that implements the application, you have just gotten the unfortunate task of trying to speed up their bad code.

    I note though, that the amount of memory, 16 GB, is very low by today standards.

    Thursday, June 15, 2017 7:21 AM