September 18, 2015 at 11:32 pm
Is there a better way to handle this sort of thing? It just doesn't feel right putting a SELECT in the WHERE, but it does exactly what I want it to do.
select col1, col2
from MyTable
where MyTable.TheDate = (
select max(OtherDate) from OtherTable where otherCol = MyTable.Something
)
.
September 19, 2015 at 12:08 am
If your goal is to show all the sales from the latest sale date, then no.
September 19, 2015 at 2:05 am
BSavoie (9/18/2015)
Is there a better way to handle this sort of thing? It just doesn't feel right putting a SELECT in the WHERE, but it does exactly what I want it to do.select col1, col2
from MyTable
where MyTable.TheDate = (
select max(OtherDate) from OtherTable where otherCol = MyTable.Something
)
Another option is to use a CTE to select the relevant rows from the OtherTable and then join the CTE to the MyTable. Without satisfying indices on both tables the CTE method is much faster, with the indices the performance is almost the same.
😎
Knowing that the above statement will be challenged, I'm including a simple test harness and the results of the test on my second gen i5 laptop, the test executed on SQL Server 1014 Dev Ed.
USE tempdb;
GO
SET NOCOUNT ON;
/* Test harness configuration */
DECLARE @SAMPLE_SIZE INT = 1000000;
DECLARE @START_DATE DATE = CONVERT(DATE,'20150101',112);
DECLARE @timer TABLE (T_TEXT VARCHAR(100) NOT NULL, T_TS DATETIME2(7) NOT NULL DEFAULT (SYSDATETIME()));
DECLARE @INT_BUCKET_01 INT = 0;
DECLARE @INT_BUCKET_02 INT = 0;
IF OBJECT_ID(N'dbo.MY_TABLE') IS NOT NULL DROP TABLE dbo.MY_TABLE;
IF OBJECT_ID(N'dbo.OTHER_TABLE') IS NOT NULL DROP TABLE dbo.OTHER_TABLE;
CREATE TABLE dbo.MY_TABLE
(
COL01 INT NOT NULL CONSTRAINT PK_DBO_MYTABLE_COL01 PRIMARY KEY CLUSTERED
,COL02 INT NOT NULL
,THE_DATE DATE NOT NULL
);
CREATE TABLE dbo.OTHER_TABLE
(
OTHER_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_OTHER_TABLE_OTHER_ID PRIMARY KEY CLUSTERED
,OTHER_DATE DATE NOT NULL
,OTHER_COL INT NOT NULL
);
/* Sample data generator */
INSERT INTO dbo.MY_TABLE(COL01,COL02,THE_DATE)
SELECT TOP (@SAMPLE_SIZE)
ROW_NUMBER() OVER
(
ORDER BY (SELECT NULL)
)
,ABS(CHECKSUM(NEWID())) % (@SAMPLE_SIZE / 20)
,DATEADD(DAY,CHECKSUM(NEWID()) % (@SAMPLE_SIZE / 20),@START_DATE)
FROM sys.all_columns SAC1
CROSS JOIN sys.all_columns SAC2;
INSERT INTO dbo.OTHER_TABLE(OTHER_DATE,OTHER_COL)
SELECT TOP (@SAMPLE_SIZE / 2)
DATEADD(DAY,CHECKSUM(NEWID()) % (@SAMPLE_SIZE / 100),@START_DATE)
,ABS(CHECKSUM(NEWID())) % (@SAMPLE_SIZE / 100 )
FROM sys.all_columns SAC1
CROSS JOIN sys.all_columns SAC2;
/* Test execution */
INSERT INTO @timer(T_TEXT) VALUES ('DRY RUN NO INDEX');
SELECT
@INT_BUCKET_01 = MT.COL01
,@INT_BUCKET_02 = MT.COL02
FROM dbo.MY_TABLE MT
CROSS APPLY dbo.OTHER_TABLE OT
WHERE MT.COL02 = OT.OTHER_COL;
INSERT INTO @timer(T_TEXT) VALUES ('DRY RUN NO INDEX');
INSERT INTO @timer(T_TEXT) VALUES ('METHOD 1 NO INDEX');
SELECT
@INT_BUCKET_01 = MT.COL01
,@INT_BUCKET_02 = MT.COL02
FROM dbo.MY_TABLE MT
WHERE MT.THE_DATE =
(
SELECT MAX(OTHER_DATE) FROM dbo.OTHER_TABLE OT WHERE MT.COL02 = OT.OTHER_COL
);
INSERT INTO @timer(T_TEXT) VALUES ('METHOD 1 NO INDEX');
INSERT INTO @timer(T_TEXT) VALUES ('METHOD 2 NO INDEX');
;WITH MAX_DATE_COL AS
(
SELECT
OT.OTHER_COL
,MAX(OT.OTHER_DATE) AS OTHER_DATE
FROM dbo.OTHER_TABLE OT
GROUP BY OT.OTHER_COL
)
SELECT
@INT_BUCKET_01 = MT.COL01
,@INT_BUCKET_02 = MT.COL02
FROM dbo.MY_TABLE MT
INNER JOIN MAX_DATE_COL MDC
ON MT.COL02 = MDC.OTHER_COL
AND MT.THE_DATE = MDC.OTHER_DATE
INSERT INTO @timer(T_TEXT) VALUES ('METHOD 2 NO INDEX');
/* uncomment to time the index creation */
--INSERT INTO @timer(T_TEXT) VALUES ('CREATE NCLIDX_DBO_MY_TABLE_COL02_THE_DATE_INCL_COL01 INDEX');
CREATE NONCLUSTERED INDEX NCLIDX_DBO_MY_TABLE_COL02_THE_DATE_INCL_COL01 ON dbo.MY_TABLE (COL02 ASC,THE_DATE ASC) INCLUDE (COL01);
--INSERT INTO @timer(T_TEXT) VALUES ('CREATE NCLIDX_DBO_MY_TABLE_COL02_THE_DATE_INCL_COL01 INDEX');
--INSERT INTO @timer(T_TEXT) VALUES ('CREATE NCLIDX_DBO_OTHER_TABLE_COL02_OTHER_DATE_OTHER_COL INDEX');
CREATE NONCLUSTERED INDEX NCLIDX_DBO_OTHER_TABLE_COL02_OTHER_DATE_OTHER_COL ON dbo.OTHER_TABLE (OTHER_DATE ASC,OTHER_COL ASC);
--INSERT INTO @timer(T_TEXT) VALUES ('CREATE NCLIDX_DBO_OTHER_TABLE_COL02_OTHER_DATE_OTHER_COL INDEX');
INSERT INTO @timer(T_TEXT) VALUES ('DRY RUN WITH INDEX');
SELECT
@INT_BUCKET_01 = MT.COL01
,@INT_BUCKET_02 = MT.COL02
FROM dbo.MY_TABLE MT
CROSS APPLY dbo.OTHER_TABLE OT
WHERE MT.COL02 = OT.OTHER_COL;
INSERT INTO @timer(T_TEXT) VALUES ('DRY RUN WITH INDEX');
INSERT INTO @timer(T_TEXT) VALUES ('METHOD 1 WITH INDEX');
SELECT
@INT_BUCKET_01 = MT.COL01
,@INT_BUCKET_02 = MT.COL02
FROM dbo.MY_TABLE MT
WHERE MT.THE_DATE =
(
SELECT MAX(OTHER_DATE) FROM dbo.OTHER_TABLE OT WHERE MT.COL02 = OT.OTHER_COL
);
INSERT INTO @timer(T_TEXT) VALUES ('METHOD 1 WITH INDEX');
INSERT INTO @timer(T_TEXT) VALUES ('METHOD 2 WITH INDEX');
;WITH MAX_DATE_COL AS
(
SELECT
OT.OTHER_COL
,MAX(OT.OTHER_DATE) AS OTHER_DATE
FROM dbo.OTHER_TABLE OT
GROUP BY OT.OTHER_COL
)
SELECT
@INT_BUCKET_01 = MT.COL01
,@INT_BUCKET_02 = MT.COL02
FROM dbo.MY_TABLE MT
INNER JOIN MAX_DATE_COL MDC
ON MT.COL02 = MDC.OTHER_COL
AND MT.THE_DATE = MDC.OTHER_DATE
INSERT INTO @timer(T_TEXT) VALUES ('METHOD 2 WITH INDEX');
/* Aggregate and display the results */
;WITH DRY_RUN AS
(
SELECT
T.T_TEXT
,DATEDIFF(MICROSECOND,MIN(T_TS),MAX(T.T_TS)) AS DURATION
FROM @timer T
WHERE T.T_TEXT = 'DRY RUN NO INDEX'
GROUP BY T.T_TEXT
)
,TIMINGS AS
(
SELECT
T.T_TEXT
,DATEDIFF(MICROSECOND,MIN(T_TS),MAX(T.T_TS)) AS DURATION
FROM @timer T
GROUP BY T.T_TEXT
)
SELECT
TS.T_TEXT
,TS.DURATION
,TS.DURATION - DR.DURATION AS DDIFF
FROM TIMINGS TS
CROSS APPLY DRY_RUN DR
ORDER BY TS.DURATION ASC;
Results
T_TEXT DURATION DDIFF
--------------------- ----------- -----------
METHOD 2 WITH INDEX 175010 -2556146
METHOD 1 WITH INDEX 178010 -2553146
METHOD 2 NO INDEX 405023 -2326133
METHOD 1 NO INDEX 1130065 -1601091
DRY RUN WITH INDEX 1266073 -1465083
DRY RUN NO INDEX 2731156 0
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply