June 16, 2021 at 3:33 pm
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
June 16, 2021 at 3:58 pm
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
)
June 16, 2021 at 4:16 pm
Not exists performs better than not in ? or is it the select 1 versus select distinct ..
Thanks for reply
June 16, 2021 at 4:21 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 16, 2021 at 4:22 pm
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.
June 16, 2021 at 4:44 pm
Thanks for Updates..
Will update with DDL shortly..
June 16, 2021 at 5:27 pm
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
);
June 16, 2021 at 7:35 pm
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
“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
June 16, 2021 at 10:34 pm
I'll try to run some testing with the suggestions given.
Thanks ALL!!!
June 17, 2021 at 2:28 pm
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]
June 17, 2021 at 2:33 pm
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
June 17, 2021 at 5:57 pm
What is the DDL for the Quality_Attribute table?
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
June 17, 2021 at 6:31 pm
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
June 17, 2021 at 6:48 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 17, 2021 at 7:31 pm
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 38 total)
You must be logged in to reply to this topic. Login to reply