A section of SQL optimization

Pre-optimization Code

Select *,row_number () over (order by wrongcount Desc) as Rowidfrom (select Quba_idint,quba_number, sel ECT top 1 sqre_adddatetime from Tbstudentstudyquestionrecords where Sqre_qubaid=quba_idint and sqre_studentid=  Sqre_answerjudge= ' wrong ' ORDER by sqre_adddatetime Desc) as Sqre_adddatetime, (select top 1              Sqre_studyfromtype from Tbstudentstudyquestionrecords where Sqre_qubaid=quba_idint and sqre_studentid=200 and sqre_answerjudge= ' wrong ' ORDER by sqre_adddatetime Desc) as Sqre_studyfromtype,count (Quba_idint) as Wrongcou Nt,count (distinct expo_knowpointidint) as Knpocount, (select top 1 sqre_mainid from tbSt Udentstudyquestionrecords where Sqre_qubaid=quba_idint and sqre_studentid=200 and sqre_answerjudge= ' wrong ' or Der by sqre_adddatetime Desc) as Sqre_mainid from Tbstudentstudyquestionrecords left joins Tbquestionbank on Sqre_q Ubaid=quba_idint LefT join Tbquestiontype on Quty_id=quba_type left join Tbexamknowpoint on Expo_examidint=quba_idint where sqre_stude Ntid=200 and quba_subjectid=15 and sqre_answerjudge= ' wrong ' and quty_name<> ' comprehensive questions ' GROUP by Quba_idint,quba_number ) as T ORDER by Quba_idint

　　

Post-optimization code

Select T.*,sqre_adddatetime,sqre_studyfromtype,sqre_mainid,sqre_qubaid,row_number () over (order by wrongcount Desc) As RowId from      (select Quba_idint,quba_number,quty_name,count (quba_idint) as Wrongcount,count (distinct expo_ Knowpointidint) as Knpocount,max (sqre_id) as LastID from              Tbstudentstudyquestionrecords left              join Tbquestionbank on Sqre_qubaid=quba_idint left join Tbquestiontype on Quty_id=quba_type left join Tbexamknowpoint on Expo_E Xamidint=quba_idint             where sqre_studentid=200 and sqre_answerjudge= ' wrong ' and quba_subjectid=15 and QuTy_Name< > ' Comprehensive question ' GROUP by Quba_idint,quba_number,quty_name ' as-             T left              join Tbstudentstudyquestionrecords on t.lastid= sqre_id    

　

It's not 1 seconds to see the optimized execution time.

Optimization ideas, the first SQL because there are three actually check is the same statement, but because the subquery can not check three columns, it was written before.

So think about using left join to optimize, take out part first, then remove part and then connect.

A section of SQL optimization