query speed

  • I'm trying to get record counts before doing some data archive, but query performance slow.

    any help with syntax to speed up?



    -- Internal variables.
    DECLARE @Error AS int = 0
    DECLARE @RowCount AS int = 0
    Declare @Interval int = N'-3'

    -- Count the no. of records that will be inserted into Quality_History table.
    SELECT @RowCount = COUNT(*)
    FROM Quality (NOLOCK)
    WHERE (Quality_Date <= DATEADD(DAY, @Interval,GETDATE()) ORQuality_Date IS NULL) AND
    Quality_Container_ID not IN (SELECT Attribute_Value
    FROM Equip_Attribute (NOLOCK)
    WHERE Attribute = N'Current Spool' AND
    ISNULL(Attribute_Value,'') <> '')
  • I think the query can be simplified to this:

    SELECT @RowCount = COUNT(*)
    FROM Quality q (NOLOCK)
    WHERE (q.Quality_Date <= DATEADD(DAY, @Interval,GETDATE())
    OR q.Quality_Date IS NULL)
    FROM Equip_Attribute e (NOLOCK)
    WHERE e.Attribute = N'Current Spool'
    AND e.Attribute_Value = q.Quality_Container_ID);

    An index would help if you haven't already got one:

    CREATE INDEX IX_Equip_Attribute_Attribute_Value_Attribute ON Equip_Attribute(Attribute_Value, Attribute);


  • Just curious - but how and why would you archive 'Quality History' if there is no Quality_Date associated with that row?  I would assume that not having a quality date yet means it hasn't gone through some process (yet).

    I guess my question really is - how do you know that a null date means it can be archived vs it hasn't yet been tested?


    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

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

Viewing 3 posts - 1 through 2 (of 2 total)

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