3
\$\begingroup\$

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.)

\$\endgroup\$
2
\$\begingroup\$

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;
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.