You should use a provided Aggregate
function sorted over a group by
statement.
Select
t.id as u_id,
(case tn.state when 'P' then SUBSTRING(tna.id,4,100) else tn.name end) as name,
t.version,
tn.state
FROM tag AS t
INNER JOIN tag_name AS tn ON tn.tag_parent = t.id
LEFT JOIN
( select max(CONCAT(format(id, '000'),name)) id,tag_name_parent from tag_name_audit group by tag_name_parent ) AS tna
ON tna.tag_name_parent = tn.id
AND tn.state = 'P'
AND tn.language = ?
AND t.version IS NOT null
AND tn.tag_parent IN (".$planIn.")
for 100
records your query rendered 13000700 NS
for my query and 295016900
for yours :O quite remarkable difference !
You can check my test cases and see by yourself:
declare @tag table(id int NOT NULL IDENTITY(1,1), version varchar(1) NULL)
declare @tag_name table(tag_parent int,id int NOT NULL IDENTITY(1,1),name varchar(5),state varchar(1))
declare @tag_name_audit table(tag_name_parent int,id int NOT NULL IDENTITY(1,1),name varchar(5))
declare @cnt int
declare @max int
set @cnt=1
set @max=100
WHILE @cnt <= @max
BEGIN
insert @tag(version) values (char(cast(DATEPART(nanosecond,SYSDATETIME())/1050 as int)%10+97))
WAITFOR DELAY '00:00:00.015';
insert @tag_name(name,tag_parent,state) values ('tn_'+char(cast(DATEPART(nanosecond,SYSDATETIME())/1050 as int)%10+97), cast( DATEDIFF(nanosecond,cast(GETDATE () as datetime2),SYSDATETIME())/90 as int)%@max+1,char(cast(DATEPART(nanosecond,SYSDATETIME())/990 as int)%2+ascii('P')))
WAITFOR DELAY '00:00:00.015';
insert @tag_name_audit(name,tag_name_parent) values ('tna_'+char(cast(DATEPART(nanosecond,SYSDATETIME())/1050 as int)%10+97), cast( DATEDIFF(nanosecond,cast(GETDATE () as datetime2),SYSDATETIME())/90 as int)%@max+1)
SET @cnt = @cnt + 1;
WAITFOR DELAY '00:00:00.015';
END;
select * from @tag order by id;
select * from @tag_name order by tag_parent;
select * from @tag_name_audit order by tag_name_parent;
DECLARE @t1 time;
DECLARE @t2 time;
DECLARE @t1_ time;
DECLARE @t2_ time;
SET @t1 = SYSUTCDATETIME() ;
Select
t.id as u_id,
(case tn.state when 'P' then SUBSTRING(tna.id,4,100) else tn.name end) as name,
t.version,
tn.state,
tn.id tn_id,
tna.id tna_id
FROM @tag AS t
INNER JOIN @tag_name AS tn ON tn.tag_parent = t.id
LEFT JOIN
( select max(CONCAT(format(id, '000'),name)) id,tag_name_parent from @tag_name_audit group by tag_name_parent) AS tna
ON tna.tag_name_parent = tn.id
AND tn.state = 'P'
AND t.version IS NOT null
SET @t2 = SYSUTCDATETIME () ;
SELECT DATEDIFF(NANOSECOND,@t1,@t2) AS elapsed_ms;
SET @t1_ = SYSUTCDATETIME() ;
SELECT
t.id as u_id,
(case tn.state when 'P' then tna.name else tn.name end) as name,
t.version,
tn.state,
tn.id tn_id,
tna.id tna_id
FROM @tag AS t
INNER JOIN @tag_name AS tn ON tn.tag_parent = t.id
LEFT JOIN @tag_name_audit AS tna ON tna.tag_name_parent = tn.id AND tn.state = 'P'
AND tna.id = (
SELECT MAX(id)
FROM @tag_name_audit
WHERE tag_name_parent = tn.id
)
AND t.version IS NOT null;
SET @t2_ = SYSUTCDATETIME () ;
SELECT DATEDIFF(NANOSECOND,@t1_,@t2_) AS elapsed_ms;