query speed

• Bruin

  SSCrazy

  Points: 2107

  More actions

  December 30, 2022 at 12:52 pm

  #4131859

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

  any help with syntax to speed up?

   

  Thanks.

  -- 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,'') <> '')

• Jonathan AC Roberts

  SSCoach

  Points: 18407

  More actions

  December 30, 2022 at 5:30 pm

  #4131979

  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)
     AND NOT EXISTS(SELECT * 
                      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);

   

• Jeffrey Williams

  SSC Guru

  Points: 90346

  More actions

  December 30, 2022 at 7:31 pm

  #4132034

  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