December 30, 2022 at 12:52 pm
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,'') <> '')
December 30, 2022 at 5:30 pm
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);
December 30, 2022 at 7:31 pm
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