suggestions on making more efficient and faster

  • I have a piece of code that I would like some suggestions on how to make it faster\ more efficient. The Quality and  Quality_Detail

    are properly Indexed, but are Large tables.

     

    Thanks.

    BEGIN TRY-- Revision 1 - Added
    SELECT @Total_Readings = SUM(CAST(QD.Value AS float))
    FROM Quality Q INNER JOIN Quality_Detail QD ON (Q.Quality_ID = QD.Quality_ID)
    WHERE Q.Quality_Container_ID = @Container_ID
    AND Q.Quality_Test_ID = @Current_Test_ID
    AND Q.Quality_Date >= DATEADD(DAY,-2,@Current_DateTime)
    AND Q.Quality_Date <= @Current_DateTime
    AND QD.Value <> N'0'
    AND Q.Quality_ID NOT IN (SELECT DISTINCT QA.Quality_ID
    FROM Quality Q INNER JOIN Quality_Attribute QA ON (Q.Quality_ID = QA.Quality_ID)
    WHERE Q.Quality_Container_ID = @Container_ID AND
    Q.Quality_Test_ID = @Current_Test_ID AND
    Q.Quality_Date >= DATEADD(DAY,-2,@Current_DateTime) AND
    Q.Quality_Date <= @Current_DateTime AND
    QA.Attribute = 'Comment' AND
    ISNULL(QA.Attribute_Value,'') <> '' AND
    QA.Attribute_Value LIKE 'In%Status%')
    END TRY
  • possible way - but without a real execution plan of this with one of the "bad performance" queries we don't know if it is the best option

    replace 

    AND Q.Quality_ID NOT IN (SELECT DISTINCT QA.Quality_ID
    FROM Quality Q INNER JOIN Quality_Attribute QA ON (Q.Quality_ID = QA.Quality_ID)
    WHERE Q.Quality_Container_ID = @Container_ID AND
    Q.Quality_Test_ID = @Current_Test_ID AND
    Q.Quality_Date >= DATEADD(DAY,-2,@Current_DateTime) AND
    Q.Quality_Date <= @Current_DateTime AND
    QA.Attribute = 'Comment' AND
    ISNULL(QA.Attribute_Value,'') <> '' AND
    QA.Attribute_Value LIKE 'In%Status%')

    with

    AND not exists (SELECT 1
    FROM Quality Q INNER JOIN Quality_Attribute QA ON (Q.Quality_ID = QA.Quality_ID)
    WHERE Q.Quality_Container_ID = @Container_ID AND
    Q.Quality_Test_ID = @Current_Test_ID AND
    Q.Quality_Date >= DATEADD(DAY,-2,@Current_DateTime) AND
    Q.Quality_Date <= @Current_DateTime AND
    QA.Attribute = 'Comment' AND
    ISNULL(QA.Attribute_Value,'') <> '' AND
    QA.Attribute_Value LIKE 'In%Status%'
    AND Q.Quality_ID = QA.Quality_ID
    )
  • Not exists performs better than not in ? or is it the select 1 versus select distinct ..

    Thanks for reply

     

  • We need to see the DDL for the two tables, including all index definitions.

    Btw, if the tables really were "properly indexed", you wouldn't be having big performance issues on this type of query :smile:.

    Based on the extremely limited data here, it seems like Quality should be clustered first on ( Quality_Date ) and Quality_Detail should be clustered first on ( Quality_ID ).  The clustering indexes are the most vital for best overall performance.

    Of course would need to confirm that by looking at index usage stats.  When reviewing indexes, you should include missing index stats in the review as well.  We can get to those later if you'd like to do a more complete analysis.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • both potentially - depending on your data that distinct may be processing results with thousands of duplicated values which require a sort to get the distinct.

    exists will only see if there is at least one entry matching the criteria.

    Exists will also require (for performance) that correct indexes exist on the table.

  • Thanks for Updates..

    Will update with DDL shortly..

  • frederico_fonseca wrote:

    possible way - but without a real execution plan of this with one of the "bad performance" queries we don't know if it is the best option

    replace 

    AND Q.Quality_ID NOT IN (SELECT DISTINCT QA.Quality_ID
    FROM Quality Q INNER JOIN Quality_Attribute QA ON (Q.Quality_ID = QA.Quality_ID)
    WHERE Q.Quality_Container_ID = @Container_ID AND
    Q.Quality_Test_ID = @Current_Test_ID AND
    Q.Quality_Date >= DATEADD(DAY,-2,@Current_DateTime) AND
    Q.Quality_Date <= @Current_DateTime AND
    QA.Attribute = 'Comment' AND
    ISNULL(QA.Attribute_Value,'') <> '' AND
    QA.Attribute_Value LIKE 'In%Status%')

    with

    AND not exists (SELECT 1
    FROM Quality Q INNER JOIN Quality_Attribute QA ON (Q.Quality_ID = QA.Quality_ID)
    WHERE Q.Quality_Container_ID = @Container_ID AND
    Q.Quality_Test_ID = @Current_Test_ID AND
    Q.Quality_Date >= DATEADD(DAY,-2,@Current_DateTime) AND
    Q.Quality_Date <= @Current_DateTime AND
    QA.Attribute = 'Comment' AND
    ISNULL(QA.Attribute_Value,'') <> '' AND
    QA.Attribute_Value LIKE 'In%Status%'
    AND Q.Quality_ID = QA.Quality_ID
    )

    I think that won't work because the OP has aliased table Quality in the main part of the query as Q and also in the subquery for the IN

    They should really alias the table Quality in the subquery to be something other than Q (maybe Q2)

    SELECT @Total_Readings = SUM(CAST(QD.Value AS FLOAT))
    FROM Quality Q
    INNER JOIN Quality_Detail QD
    ON QD.Quality_ID = Q.Quality_ID
    AND QD.Value <> N'0'
    WHERE Q.Quality_Container_ID = @Container_ID
    AND Q.Quality_Test_ID = @Current_Test_ID
    AND Q.Quality_Date BETWEEN DATEADD(DAY, -2, @Current_DateTime) AND @Current_DateTime
    AND NOT EXISTS(SELECT *
    FROM Quality Q2
    INNER JOIN Quality_Attribute QA
    ON QA.Quality_ID = Q2.Quality_ID
    AND QA.Quality_ID = Q.Quality_ID
    AND QA.Attribute = 'Comment'
    AND ISNULL(QA.Attribute_Value,'') <> ''
    AND QA.Attribute_Value LIKE 'In%Status%'
    WHERE Q2.Quality_Container_ID = @Container_ID
    AND Q2.Quality_Test_ID = @Current_Test_ID
    AND Q2.Quality_Date BETWEEN DATEADD(DAY,-2,@Current_DateTime) AND @Current_DateTime
    );

     

  • Just a further clarification - DISTINCT is not needed here, because the IN operator doesn't care if there are duplicates.  It just looks for a matching value in the list.

    I would also look further at this statement ISNULL(QA.Attribute_Value, '') <> ''

    The assumption here is that there can be both NULL and empty string values and you want to eliminate either.  You can use QA.Attribute_Value <> '' or QA.Attribute_Value > '' and achieve the same result.

    And - investigate why you need to join to the Quality table again.  Is it really necessary - or could you just look to the Quality_Attribute table based on Quality.Quality_ID?  It seems to me that this should work:

       AND NOT EXISTS(SELECT *
    FROM Quality_Attribute QA
    WHERE QA.Quality_ID = Q.Quality_ID
    AND QA.Attribute = 'Comment'
    AND QA.Attribute_Value <> ''
    AND QA.Attribute_Value LIKE 'In%Status%')

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • I'll try to run some testing with the suggestions given.

    Thanks ALL!!!

  • DDL for Quality Table.

    CREATE TABLE [dbo].[Quality](
    [Quality_ID] [int] IDENTITY(26692927,1) NOT NULL,
    [Quality_Test_ID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Quality_DateTime] [datetime] NULL,
    [Quality_Employee_ID] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Quality_Equip_ID] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Quality_Container_ID] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Quality_WO_ID] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Quality_Part_No] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Quality_Min] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Quality_Tgt_Min] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Quality_Tgt] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Quality_Tgt_Max] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Quality_Max] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Result] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Quality_Date] AS (CONVERT([date],[Quality_DateTime],(0))),
    CONSTRAINT [PK_Quality] PRIMARY KEY CLUSTERED
    (
    [Quality_ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    CREATE NONCLUSTERED INDEX [IX_Quality] ON [dbo].[Quality]
    (
    [Quality_Equip_ID] ASC,
    [Quality_Test_ID] ASC,
    [Quality_Container_ID] ASC
    )
    INCLUDE ( [Quality_DateTime],
    [Result]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    SET ANSI_PADDING ON
    GO

    CREATE NONCLUSTERED INDEX [IX_Quality_1] ON [dbo].[Quality]
    (
    [Quality_Container_ID] ASC,
    [Quality_Test_ID] ASC
    )
    INCLUDE ( [Result]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [IX_Quality_2] ON [dbo].[Quality]
    (
    [Quality_Date] ASC
    )
    INCLUDE ( [Quality_ID],
    [Quality_Test_ID],
    [Quality_DateTime],
    [Quality_Equip_ID],
    [Quality_Container_ID],
    [Result]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  • DDL for Quality_Detail..

     

     

    CREATE TABLE [dbo].[Quality_Detail](
    [Quality_ID] [int] NOT NULL,
    [Value_Count] [int] NOT NULL,
    [Value] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Result] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    CONSTRAINT [PK_Quality_Detail] PRIMARY KEY CLUSTERED
    (
    [Quality_ID] ASC,
    [Value_Count] ASC
  • What is the DDL for the Quality_Attribute table?

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Attribute DDL

    CREATE TABLE [dbo].[Quality_Attribute](
    [Quality_ID] [int] NOT NULL,
    [Attribute] [nvarchar](100) NOT NULL,
    [Attribute_Value] [nvarchar](100) NULL,
    [Attribute_DateTime] [datetime] NULL,
    CONSTRAINT [PK_Quality_Attribute] PRIMARY KEY CLUSTERED
    (
    [Quality_ID] ASC,
    [Attribute] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
  • Hopefully this should improve things.  As I noted before, it's very likely that re-clustering the Quality table would give you even more performance gains, as it would prevent a full scan of the Quality table.  How much you gain depends on how long of a history is kept in the Quality table.

        SELECT @Total_Readings = SUM(CAST(QD.Value AS float)) 
    FROM Quality Q
    INNER JOIN Quality_Detail QD ON (Q.Quality_ID = QD.Quality_ID)
    WHERE Q.Quality_Container_ID = @Container_ID
    AND Q.Quality_Test_ID = @Current_Test_ID
    AND Q.Quality_Date >= DATEADD(DAY,-2,@Current_DateTime)
    AND Q.Quality_Date <= @Current_DateTime
    AND QD.Value <> N'0'
    AND NOT EXISTS (SELECT 1
    FROM Quality_Attribute QA
    WHERE Q.Quality_ID = QA.Quality_ID AND
    QA.Attribute = 'Comment' AND
    QA.Attribute_Value LIKE 'In%Status%')

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Thanks... Will test that code out... Quality table has 100+ million records as does detail and Attribute has 400+ million

     

    Do you see anything that would improve this snippet of code using the same tables?

    Many Thanks for suggestions..

     

    SELECT@Alarm_Count = COUNT(*)
    FROMdbo.Quality AS q
    LEFT JOINdbo.Quality_Attribute AS qa ON qa.Quality_ID = q.Quality_ID
    AND qa.Attribute = 'Comment'
    AND qa.Attribute_Value LIKE 'In%Status%'
    WHEREq.Quality_Container_ID = @Container_ID
    AND q.Quality_Test_ID = @Current_Test_ID
    AND q.Quality_Date >= DATEADD(DAY, -2, @Current_DateTime)
    AND q.Quality_Date <= @Current_DateTime
    AND q.Result <> N'PASS'
    AND q.Result LIKE @Alarm_Type
    AND qa.Quality_ID IS NULL;

Viewing 15 posts - 1 through 15 (of 39 total)

You must be logged in to reply to this topic. Login to reply