Speed up query that handles auditing and versioning

Question

I have the following tables:

tag_name_audit table enter image description here

tag_name table enter image description here

tag_adit table enter image description here

tag table enter image description here

The action that the query performs is auditing and version handling. For example i can have one set of data that has a stable version and an edit version. And you can release that last edit version and it becomes the last stable version.

I have the following query:

SELECT 
 t.id as u_id,
 IF(tn.state = 'P',tna.name,tn.name) as name,
 t.version,
 tn.state
 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 
 )
 WHERE tn.language = ?
 AND t.version IS NOT null
 AND tn.tag_parent IN (".$planIn.")

I'm curious if there are any better solutions writing it so that i can boost the performance of the query speed.

Showing rows 0 - 8 (9 total, Query took 0.0009 seconds.)

Answer 1

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;