MotivateMan1394 (4/25/2015)
HiHow can I rewrite this in better performance :
select *, ( SELECT TOP 1 Code FROM T2 WHERE T2.Id=T1.Id ORDER BY Id DESC ) AS T2_Code
from T1
(Top 1 is problem for me)
Top 1 doesn't have to be a problem or rather all depends, consider these samples
😎
USE tempdb;
GO
SET NOCOUNT ON;
/********************************************************************
Sample data set
********************************************************************/
DECLARE @SAMPLE_SIZE INT = 10000;
DECLARE @CODE_COUNT INT = 100;
IF OBJECT_ID(N'dbo.TBL_SAMPLE_T2') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_T2;
IF OBJECT_ID(N'dbo.TBL_SAMPLE_T1') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_T1;
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
, NUMS(N) AS (SELECT TOP (@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
SELECT
ISNULL(CONVERT(INT,NM.N,0),0) AS T1_ID
,REPLACE(NEWID(),'-','') AS T1_TEXT
INTO dbo.TBL_SAMPLE_T1
FROM NUMS NM;
ALTER TABLE dbo.TBL_SAMPLE_T1 ADD CONSTRAINT PK_DBO_TBL_SAMPLE_T1_T1_ID PRIMARY KEY CLUSTERED (T1_ID ASC);
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
, NUMS(N) AS (SELECT TOP (@SAMPLE_SIZE * @CODE_COUNT) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
SELECT
ISNULL(NM.N,0) AS T2_ID
,ISNULL(CONVERT(INT,(ABS(CHECKSUM(NEWID())) % @SAMPLE_SIZE) + 1,0),1) AS T1_ID
,SUBSTRING(REPLACE(NEWID(),'-',''),1,8) AS T2_CODE
INTO dbo.TBL_SAMPLE_T2
FROM NUMS NM;
ALTER TABLE dbo.TBL_SAMPLE_T2 ADD CONSTRAINT PK_DBO_TBL_SAMPLE_T2_T2_ID PRIMARY KEY CLUSTERED (T2_ID ASC);
ALTER TABLE dbo.TBL_SAMPLE_T2 ADD CONSTRAINT FK_DBO_TBL_SAMPLE_T2_T1_ID_DBO_TBL_SAMPLE_T1_T1_ID FOREIGN KEY (T1_ID) REFERENCES dbo.TBL_SAMPLE_T1( T1_ID );
/********************************************************************
Test harness
********************************************************************/
DECLARE @INT_BUCKET INT = 0;
DECLARE @CHR_BUCKET CHAR(10) = '';
RAISERROR(N'---------------------------------------------------------------------
SUBQUERY METHOD NO INDEX
---------------------------------------------------------------------',0,0) WITH NOWAIT;
SET STATISTICS IO,TIME ON;
SELECT
@INT_BUCKET = T1.T1_ID
,@CHR_BUCKET = (SELECT
TOP(1) T2.T2_CODE
FROM dbo.TBL_SAMPLE_T2 T2
WHERE T1.T1_ID = T2.T1_ID
ORDER BY T2.T2_ID DESC) --AS T2_CODE
FROM dbo.TBL_SAMPLE_T1 T1;
SET STATISTICS TIME,IO OFF;
RAISERROR(N'---------------------------------------------------------------------
CROSS APPLY METHOD NO INDEX
---------------------------------------------------------------------',0,0) WITH NOWAIT;
SET STATISTICS IO,TIME ON;
SELECT
@INT_BUCKET = T1.T1_ID
,@CHR_BUCKET = TX.T2_CODE
FROM dbo.TBL_SAMPLE_T1 T1
CROSS APPLY (
SELECT
T2.T1_ID
,MAX(T2.T2_ID) AS T2_ID
FROM dbo.TBL_SAMPLE_T2 T2
GROUP BY T2.T1_ID
) AS X
CROSS APPLY dbo.TBL_SAMPLE_T2 TX
WHERE T1.T1_ID = X.T1_ID
AND X.T2_ID = TX.T2_ID;
SET STATISTICS TIME,IO OFF;
RAISERROR(N'---------------------------------------------------------------------
PP WINDOW SET NO INDEX
---------------------------------------------------------------------',0,0) WITH NOWAIT;
SET STATISTICS IO,TIME ON;
;WITH OrderedSet AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY t2.T1_ID
ORDER BY t2.T2_CODE asc
,t2.T2_ID desc
) AS T2_RID
,T2.T1_ID
,t2.T2_CODE
FROM dbo.TBL_SAMPLE_T2 t2
)
SELECT
@INT_BUCKET = t1.T1_ID
,@CHR_BUCKET = OS.T2_CODE
FROM dbo.TBL_SAMPLE_T1 t1
INNER JOIN OrderedSet OS
ON t1.T1_ID = OS.T1_ID
WHERE OS.T2_RID = 1;SET STATISTICS TIME,IO OFF;
CREATE UNIQUE NONCLUSTERED INDEX UNQNCLIDX_DBO_TBL_SAMPLE_T2_T1_ID_T2_ID_INCL_T2_CODE ON dbo.TBL_SAMPLE_T2
( T1_ID ASC, T2_ID DESC )
INCLUDE
( T2_CODE);
RAISERROR(N'---------------------------------------------------------------------
SUBQUERY METHOD WITH INDEX
---------------------------------------------------------------------',0,0) WITH NOWAIT;
SET STATISTICS IO,TIME ON;
SELECT
@INT_BUCKET = T1.T1_ID
,@CHR_BUCKET = (SELECT
TOP(1) T2.T2_CODE
FROM dbo.TBL_SAMPLE_T2 T2
WHERE T1.T1_ID = T2.T1_ID
ORDER BY T2.T2_ID DESC) --AS T2_CODE
FROM dbo.TBL_SAMPLE_T1 T1;
SET STATISTICS TIME,IO OFF;
RAISERROR(N'---------------------------------------------------------------------
CROSS APPLY METHOD WITH INDEX
---------------------------------------------------------------------',0,0) WITH NOWAIT;
SET STATISTICS IO,TIME ON;
SELECT
@INT_BUCKET = T1.T1_ID
,@CHR_BUCKET = TX.T2_CODE
FROM dbo.TBL_SAMPLE_T1 T1
CROSS APPLY (
SELECT
T2.T1_ID
,MAX(T2.T2_ID) AS T2_ID
FROM dbo.TBL_SAMPLE_T2 T2
GROUP BY T2.T1_ID
) AS X
CROSS APPLY dbo.TBL_SAMPLE_T2 TX
WHERE T1.T1_ID = X.T1_ID
AND X.T2_ID = TX.T2_ID;
SET STATISTICS TIME,IO OFF;
RAISERROR(N'---------------------------------------------------------------------
PP WINDOW SET WITH INDEX
---------------------------------------------------------------------',0,0) WITH NOWAIT;
SET STATISTICS IO,TIME ON;
;WITH OrderedSet AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY t2.T1_ID
ORDER BY t2.T2_CODE asc
,t2.T2_ID desc
) AS T2_RID
,T2.T1_ID
,t2.T2_CODE
FROM dbo.TBL_SAMPLE_T2 t2
)
SELECT
@INT_BUCKET = t1.T1_ID
,@CHR_BUCKET = OS.T2_CODE
FROM dbo.TBL_SAMPLE_T1 t1
INNER JOIN OrderedSet OS
ON t1.T1_ID = OS.T1_ID
WHERE OS.T2_RID = 1;SET STATISTICS TIME,IO OFF;
Output
---------------------------------------------------------------------
SUBQUERY METHOD NO INDEX
---------------------------------------------------------------------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
Table 'TBL_SAMPLE_T2'. Scan count 1, logical reads 546391, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TBL_SAMPLE_T1'. Scan count 1, logical reads 63, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 6614 ms, elapsed time = 6642 ms.
---------------------------------------------------------------------
CROSS APPLY METHOD NO INDEX
---------------------------------------------------------------------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 9 ms.
Table 'TBL_SAMPLE_T2'. Scan count 1, logical reads 34731, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TBL_SAMPLE_T1'. Scan count 1, logical reads 63, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 250 ms.
---------------------------------------------------------------------
PP WINDOW SET NO INDEX
---------------------------------------------------------------------
SQL Server parse and compile time:
CPU time = 3 ms, elapsed time = 3 ms.
Table 'TBL_SAMPLE_T1'. Scan count 5, logical reads 184, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TBL_SAMPLE_T2'. Scan count 5, logical reads 4139, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3806 ms, elapsed time = 1016 ms.
---------------------------------------------------------------------
SUBQUERY METHOD WITH INDEX
---------------------------------------------------------------------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
Table 'TBL_SAMPLE_T2'. Scan count 10000, logical reads 31945, physical reads 0, read-ahead reads 9, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TBL_SAMPLE_T1'. Scan count 1, logical reads 63, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 32 ms, elapsed time = 36 ms.
---------------------------------------------------------------------
CROSS APPLY METHOD WITH INDEX
---------------------------------------------------------------------
SQL Server parse and compile time:
CPU time = 12 ms, elapsed time = 12 ms.
Table 'TBL_SAMPLE_T2'. Scan count 1, logical reads 34367, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TBL_SAMPLE_T1'. Scan count 1, logical reads 63, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 187 ms, elapsed time = 196 ms.
---------------------------------------------------------------------
PP WINDOW SET WITH INDEX
---------------------------------------------------------------------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 3 ms.
Table 'TBL_SAMPLE_T1'. Scan count 5, logical reads 184, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TBL_SAMPLE_T2'. Scan count 5, logical reads 3779, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3168 ms, elapsed time = 857 ms.
Edit: added Phil's method