8

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.

enter image description here

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 10

Query:

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.

7

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 ALL plan

UNION plan:

UNION plan

4

Use cross apply to unpivot columns into rows

SELECT --DISTINCT most probably
  JobRunId,
  ut.Name, 
  ut.[Type], 
  ut.Granularity
FROM dbo.TestUpload
CROSS APPLY (
  SELECT ThingBName AS Name, 
  ThingBType AS [Type], 
  ThingBGranularity AS Granularity
  UNION ALL 
  SELECT ThingAName AS Name, 
  ThingAType AS [Type], 
  ThingAGranularity AS Granularity
  ) ut
WHERE JobRunId = @JobRunID
  • 1
    No, I thought the same but this wouldn't work. Not even with UNION as there are duplicates that have to be removed. – ypercubeᵀᴹ Jun 24 '16 at 11:19
  • You mean sort is unavoidable? – Serg Jun 24 '16 at 12:21
  • 1
    I mean that DISTINCT is required. See James' data. There's a row with A,B,C,-,-,- and another with -,-,-,A,B,C. The resulting rows one from A data and the other from B data are identical and have to be removed. Even if the UNION ALL is replace with UNION, this can't be avoided. (and with "this" I don't mean "sort", I mean the "distinct" operation. It might be doen with sort or hash, whatever method the optimizer can and chooses to.) – ypercubeᵀᴹ Jun 24 '16 at 12:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.