patternsqlMinor
Is there a way to make this select retrieve the same results with a single seek?
Viewed 0 times
thissametheseekwithmakewayretrievesingleresults
Problem
Is it possible to retrieve the same data as the following with a single seek or scan, either by modifying the query or influencing the optimizer's strategy?
Code and schema similar to this are currently on SQL Server 2014.
Repro script. Setup:
Query:
Tear down:
I think this is probably not modeled ideally. I'm trying to get more information from the developer about how the schema was chosen but am curious if there's a TSQL trick I'm overlooking as it will be easier to change the query than the schema.
Code and schema similar to this are currently on SQL Server 2014.
Repro script. Setup:
USE tempdb;
GO
IF OBJECT_ID('dbo.TestUpload', 'U') IS NOT NULL
DROP TABLE dbo.TestUpload;
CREATE TABLE dbo.TestUpload(
JobRunId bigint NOT NULL,
ThingAName nvarchar(255) NOT NULL,
ThingAType nvarchar(255) NOT NULL,
ThingAGranularity nvarchar(255) NOT NULL,
ThingBName nvarchar(255) NOT NULL,
ThingBType nvarchar(255) NOT NULL,
ThingBGranularity nvarchar(255) NOT NULL
);
CREATE CLUSTERED INDEX IX_JobRunId ON dbo.TestUpload (JobRunId);
GO
INSERT INTO dbo.TestUpload (JobRunId, ThingAName, ThingAType, ThingAGranularity, ThingBName, ThingBType, ThingBGranularity)
VALUES (1, 'A', 'B', 'C', 'D', 'E', 'F');
GO 10
INSERT INTO dbo.TestUpload (JobRunId, ThingAName, ThingAType, ThingAGranularity, ThingBName, ThingBType, ThingBGranularity)
VALUES (1, 'D', 'E', 'F', 'A', 'B', 'C');
GO 10Query:
DECLARE @JobRunID bigint = 1;
SELECT JobRunId,
ThingAName AS Name,
ThingAType AS [Type],
ThingAGranularity AS Granularity
FROM dbo.TestUpload
WHERE JobRunId = @JobRunID
UNION
SELECT JobRunId,
ThingBName AS Name,
ThingBType AS [Type],
ThingBGranularity AS Granularity
FROM dbo.TestUpload
WHERE JobRunId = @JobRunID;Tear down:
IF OBJECT_ID('dbo.TestUpload', 'U') IS NOT NULL
DROP TABLE dbo.TestUpload;I think this is probably not modeled ideally. I'm trying to get more information from the developer about how the schema was chosen but am curious if there's a TSQL trick I'm overlooking as it will be easier to change the query than the schema.
Solution
I'd try this but I have no idea if it will be more efficient. You need the
DISTINCT to remove duplicates, so the UNION ALL might be more appropriate, no need for two distinct operations:SELECT DISTINCT
JobRunId = @JobRunID,
d.*
FROM dbo.TestUpload
CROSS APPLY
( SELECT
ThingAName AS Name,
ThingAType AS [Type],
ThingAGranularity AS Granularity
UNION -- or UNION ALL
SELECT
ThingBName,
ThingBType,
ThingBGranularity
) AS d
WHERE JobRunId = @JobRunID ;UNION ALL plan:UNION plan:Code Snippets
SELECT DISTINCT
JobRunId = @JobRunID,
d.*
FROM dbo.TestUpload
CROSS APPLY
( SELECT
ThingAName AS Name,
ThingAType AS [Type],
ThingAGranularity AS Granularity
UNION -- or UNION ALL
SELECT
ThingBName,
ThingBType,
ThingBGranularity
) AS d
WHERE JobRunId = @JobRunID ;Context
StackExchange Database Administrators Q#142139, answer score: 7
Revisions (0)
No revisions yet.