June 19, 2018 at 3:53 am
We have inherited the following (obfuscated) code, and are looking to improve performance. MyAudit has over 35 million rows, and MyTable has 0.5 million rows. The query takes 3-4 minutes to complete due to index scans. There is no index on the ValidUntil column of MyAudit, but even with an index present there is no improvement.
The app developers have requested that ValidUntil remain nullable. Is there a way to make this SARGable and more performant without making ValidUntil indexed and non-nullable? Or is there a more performant way to write this without using ISNULL?
In addition, the execution plan shows an index scan on MyAudit.MyTableID, even though the non-clustered index IX_MyAudit_MyTableID is in place. We have tried adding MyAuditID as an included column, but this does not appear to help. How can we remove this index scan?
USE tempdb;
GO
IF OBJECT_ID('MyTable') IS NOT NULL
DROP TABLE dbo.MyTable;
CREATE TABLE dbo.MyTable (
MyTableID int NOT NULL IDENTITY(1,1),
ValidUntil datetime NULL,
CreatedOn datetime NOT NULL,
AuditTimestamp datetime NULL,
CONSTRAINT PK_MyTable_MyTableID PRIMARY KEY CLUSTERED (MyTableID)
);
CREATE NONCLUSTERED INDEX IX_MyTable_Dates ON dbo.MyTable (CreatedOn, AuditTimestamp);
GO
CREATE TABLE dbo.MyAudit (
MyAuditID int NOT NULL IDENTITY(1,1),
MyTableID int NOT NULL,
CONSTRAINT PK_MyAudit_MyAuditID PRIMARY KEY CLUSTERED (MyAuditID),
CONSTRAINT FK_MyAudit_MyTableID FOREIGN KEY (MyTableID) REFERENCES dbo.MyTable (MyTableID)
);
GO
CREATE NONCLUSTERED INDEX IX_MyAudit_MyTableID ON dbo.MyAudit (MyTableID);
GO
DECLARE @ValidityDays int = 30;
SELECT
MA.MyAuditID
FROM dbo.MyAudit MA /*35 million rows*/
INNER JOIN dbo.MyTable MT /*0.5 million rows*/ ON MT.MyTableID = MA.MyTableID
WHERE ISNULL(MT.ValidUntil, (MT.CreatedOn + @ValidityDays)) < DATEADD(dd, -2, CONVERT(date, GETUTCDATE()));
GO
June 19, 2018 at 4:12 am
Please post an "actual" (not "estimated") execution plan as a .sqlplan attachment.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 19, 2018 at 4:19 am
You could try making your WHERE clause SARGable:
SELECT
MA.MyAuditID
FROM dbo.MyAudit MA /*35 million rows*/
INNER JOIN dbo.MyTable MT /*0.5 million rows*/
ON MT.MyTableID = MA.MyTableID
WHERE MT.ValidUntil < DATEADD(dd, -2, CONVERT(date, GETUTCDATE()))
OR MT.CreatedOn < DATEADD(dd, -(2 + @ValidityDays), CONVERT(date, GETUTCDATE()))
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 19, 2018 at 7:04 am
I just tried your query with 0.5 million in MyTable and 30 million in MyAudit. And it ran in 8 seconds on my work desktop. Maybe you need better hardware?
use tempdb
go
IF OBJECT_ID('MyAudit') IS NOT NULL
DROP TABLE dbo.MyAudit
GO
IF OBJECT_ID('MyTable') IS NOT NULL
DROP TABLE dbo.MyTable;
CREATE TABLE dbo.MyTable (
MyTableID int NOT NULL IDENTITY(1,1),
ValidUntil datetime NULL,
CreatedOn datetime NOT NULL,
AuditTimestamp datetime NULL,
CONSTRAINT PK_MyTable_MyTableID PRIMARY KEY CLUSTERED (MyTableID)
);
GO
INSERT INTO [dbo].[MyTable]([ValidUntil],[CreatedOn],[AuditTimestamp])
SELECT DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0),
DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0),
DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) T(C)
CROSS APPLY (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS A(B)
CROSS APPLY (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS C(D)
CROSS APPLY (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS E(F)
CROSS APPLY (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS G(F)
GO
CREATE NONCLUSTERED INDEX IX_MyTable_Dates ON dbo.MyTable (CreatedOn, AuditTimestamp);
GO
CREATE TABLE dbo.MyAudit (
MyAuditID int NOT NULL IDENTITY(1,1),
MyTableID int NOT NULL,
CONSTRAINT PK_MyAudit_MyAuditID PRIMARY KEY CLUSTERED (MyAuditID)
);
GO
INSERT INTO [dbo].[MyAudit]
([MyTableID])
SELECT [MyTableID]
FROM dbo.MyTable
CROSS APPLY (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS G(F)
CROSS APPLY (VALUES (0),(0),(0)) AS E(F);
GO
ALTER TABLE [dbo].[MyAudit] WITH CHECK ADD CONSTRAINT [FK_MyAudit_MyTableID] FOREIGN KEY([MyTableID])
REFERENCES [dbo].[MyTable] ([MyTableID])
GO
ALTER TABLE [dbo].[MyAudit] CHECK CONSTRAINT [FK_MyAudit_MyTableID]
GO
CREATE NONCLUSTERED INDEX IX_MyAudit_MyTableID ON dbo.MyAudit (MyTableID);
IF OBJECT_ID('tempdb.dbo.#temp') is not null
DROP TABLE #temp
GO
DECLARE @StartDate as datetime
SET @StartDate = GETDATE()
DECLARE @ValidityDays int = 30;
SELECT MA.MyAuditID
INTO #temp
FROM dbo.MyAudit MA /*35 million rows*/
INNER JOIN dbo.MyTable MT /*0.5 million rows*/ ON MT.MyTableID = MA.MyTableID
WHERE ISNULL(MT.ValidUntil, (MT.CreatedOn + @ValidityDays)) < DATEADD(dd, -2, CONVERT(date, GETUTCDATE()));
PRINT CONCAT('Duration=', DATEDIFF(ss,@StartDate ,GETDATE()),' seconds')
GO
select count(*) from [dbo].[MyAudit]
select count(*) from [dbo].MyTable
select count(*) from #temp
June 19, 2018 at 12:45 pm
Also, consider clustering dbo.MyTable on (ValidUntil, CreatedOn) or adding ValidUntil to the nonclus index. [You don't have to cluster tables by identity, and it can actually be quite harmful to performance. Really that's true.] Or at least add ValidUntil to the nonclus index.
Similarly you should consider clustering the Audit table on ( MyTableID, MyAuditID ).
DECLARE @ValidityDays int = 30;
SELECT
MA.MyAuditID
FROM dbo.MyAudit MA /*35 million rows*/
INNER JOIN (
SELECT MyTableID
FROM dbo.MyTable /*0.5 million rows*/
WHERE ValidUntil < DATEADD(DAY, -2, CAST(GETUTCDATE() AS date)) OR
(ValidUntil IS NULL AND CreatedOn < DATEADD(DAY, -2 -@ValidityDays, CAST(GETUTCDATE() AS date)))
) AS MT ON MT.MyTableID = MA.MyTableID
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 20, 2018 at 4:02 am
Thank you all for your replies, apologies but it does appear that some of our obfuscation has resulted in the above script not accurately reproducing the same issue. In addition, the underlying table structures are now being considered for alteration by the app developers, so this problem has been put on hold until further clarification can be gained as time is having to be diverted elsewhere.
I would like to present a working solution to this post, but it may have to be delayed a few days or so until we have clarification on how the app developers wish to proceed!
Thanks again all.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply