Query optimise

SQL Server Administration
, ,
1

want to optimize the query, for every record two aggregate sub-query is executing which cause performance. plz guide how to optimize this:

select g.GuardianId,
CASE WHEN (select COUNT(*) from tbl_nfctransaction n where n.RefrenceID=g.StudentId and n.tDate=@Date)>0 THEN 1
ELSE 0
END as checkinStatus,
CASE WHEN (select top 1 NfcCardNumber from tbl_nfctransaction where n.RefrenceID=g.StudentId and n.tDate=@Date)='-1' THEN 1
ELSE 0
END as isManualCheckin

from tbl_Guardians g

2

Would help if you used aliases for the tables to know which column belongs to which table

3

corrected the query....plz check

4

corrected the query....plz check

5 
;
WITH cte AS
(
         SELECT   g.guardianid,
                  Count(*) AS cnt
         FROM     tbl_guardians g   JOIN     tbl_nfctransaction n
               ON    n.refrenceid = g.studentid    AND n.tdate = @Date
         GROUP BY g.guardianid )
SELECT g.guardianid,
       CASE
              WHEN cnt > 0 THEN 1
              ELSE 0
       END AS checkinstatus 
FROM cte
6

You need an index on tbl_nfctransaction ( tDate, RefrenceID ) include ( NfcCardNumber )

Then:


select g.GuardianId,
CASE WHEN EXISTS(select 1 from tbl_nfctransaction n where n.RefrenceID=g.StudentId and n.tDate=@Date) THEN 1 
ELSE 0
END as checkinStatus,
CASE WHEN (select top (1) NfcCardNumber from tbl_nfctransaction n where n.RefrenceID=g.StudentId and n.tDate=@Date)='-1' THEN 1
ELSE 0
END as isManualCheckin

from tbl_Guardians g
7 
select g.GuardianId,
       CASE WHEN n.refrenceID is null THEN 0 
       ELSE 1
       END as checkinStatus,
	   CASE WHEN r.NfcCardNumber ='-1' THEN 1
	   ELSE 0
	   END as isManualCheckin
from tbl_Guardians g
left join (select top (1) NfcCardNumber , RefrenceID
from tbl_nfctransaction n where  n.tDate=@Date) r
on r.RefrenceID=g.StudentId

Would this work with only joining once to the nfc table? seems suspect

What are you trying to capture with this one?

CASE WHEN (select top 1 NfcCardNumber 
from tbl_nfctransaction 
where n.RefrenceID=g.StudentId and n.tDate=@Date)='-1'

Seems so random with no order by