代码之家 › 专栏 › 技术社区 › Aakash Singh

查询加载数据花费的时间太长

inner-join stored-procedures sql-server

Aakash Singh · 技术社区 · 6 年前

我从一组带有join的表中选择数据,但执行此操作时花费了太多时间。 applicationusagelog 表包含批量数据。

   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

有人帮助减少加载时间。。。

2 回复 | 直到 6 年前

Jatin Patel 6 年前

尝试更改 NOT IN 具有的条件 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

如果中的表 不在 子查询很重。

Blueprint41 6 年前

最有可能的是在where子句中将createdon转换为datetime是罪魁祸首。在使用cast之后,数据库不能再使用索引来减少为日期范围设置的潜在结果集。这反过来又迫使数据库查看整个日期范围,这反过来又会导致大量非常慢的磁盘访问。

您应该将createdon的字段类型更改为日期格式(如datetime),并在不强制转换的情况下编写查询。如果之后仍然很慢,请在createdon字段上放置一个索引。