Speed up query that handles auditing and versioning

Question

I have the following tables:

tag_name_audit table

tag_name table

tag_adit table

tag table

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;