Query taking too much time to load data

Question

I am selecting data from a set of tables with join, but when I execute this it is taking too much time. applicationusagelog table has bulk data.

   SELECT  CONVERT(varchar(6),( AccessTime/3600))
            + ':' + RIGHT('0' + CONVERT(varchar(2), (AccessTime % 3600) / 60), 2)
            + ':' + RIGHT('0' + CONVERT(varchar(2), AccessTime % 60), 2) as AccessTime
            From
            (
                Select SUM(DATEPART(SECOND,LogTime)) as AccessTime
                From   applicationusagelog
                       Inner Join usermaster    On usermaster.EmployeeID=applicationusagelog.CreatedBy
                       Inner Join em_masteruser On em_masteruser.id=usermaster.OrganizationId
                Where  Cast(applicationusagelog.CreatedOn as date) Between '01-01-2018' And '04-04-2018' 
                And    em_masteruser.id='1' 
                And    applicationusagelog.ApplicationName not in
                        (
                            SELECT  master_domainapp.domainname 
                            From    master_domainapp
                                    Inner Join master_category_application On master_category_application.CategoryId=master_domainapp.CategoryId
                                    Inner Join em_masteruser On em_masteruser.id=master_domainapp.CreatedBy
                            Where  em_masteruser.id='1' 
                            And    master_category_application.CreatedBy='1'
                        )
            )x

someone help to reduce the loading time...

Answer 1

Try changing the NOT IN condition with a LEFT JOIN.

SELECT DISTINCT master_domainapp.domainname
INTO #domainnames
FROM master_domainapp
INNER JOIN master_category_application ON master_category_application.CategoryId = master_domainapp.CategoryId
INNER JOIN em_masteruser ON em_masteruser.id = master_domainapp.CreatedBy
WHERE em_masteruser.id = '1'
    AND master_category_application.CreatedBy = '1'

SELECT CONVERT(VARCHAR(6), (AccessTime / 3600)) + ':' + RIGHT('0' + CONVERT(VARCHAR(2), (AccessTime % 3600) / 60), 2) + ':' + RIGHT('0' + CONVERT(VARCHAR(2), AccessTime % 60), 2) AS AccessTime
FROM (
    SELECT SUM(DATEPART(SECOND, LogTime)) AS AccessTime
    FROM applicationusagelog
    INNER JOIN usermaster ON usermaster.EmployeeID = applicationusagelog.CreatedBy
    INNER JOIN em_masteruser ON em_masteruser.id = usermaster.OrganizationId
    LEFT JOIN #domainnames ON #domainnames.domainname = applicationusagelog.ApplicationName
    WHERE Cast(applicationusagelog.CreatedOn AS DATE) BETWEEN '01-01-2018'
            AND '04-04-2018'
        AND em_masteruser.id = '1'
        AND #domainnames.domainname IS NULL
    ) x

This may improve performance if the tables in the NOT IN sub query are heavy.