时间戳

首页 > 解决方案 > 使用左连接 SQL Server 时如何提高搜索速度？

sql - 使用左连接 SQL Server 时如何提高搜索速度？

问题描述

在 SQL Server 中，我从两个查询数据VIEW以获取OLDPID-1，-2 的记录：

SELECT 
    T1.*, T2.LEAF 
FROM 
    (SELECT * 
     FROM VIEW_OBJECT_TREE_DATA  
     WHERE OLDPID IN (-1, -2)) T1
LEFT JOIN 
    VIEW_OBJECT_TREE_DATA_GROUP T2 ON T1.NODEID = T2.NODEPID 
WHERE 
    T1.STATE = 1

但是需要3-4秒才能得到结果。

如何修改此 SQL 查询以提高其速度？

VIEW_OBJECT_TREE_DATAOLDPID、OLDID 和 NAME 列有 450000 条记录。VIEW_OBJECT_TREE_DATA_GROUP有 NODEPID 和 LEAF，有 65000 条记录。

下面是一些视图和函数的sql：VIEW_OBJECT_TREE_DATA：

CREATE VIEW dbo.VIEW_OBJECT_TREE_DATA
AS
SELECT(SELECT[dbo].[FNNC_GET_TREE_GUID](0, OBJECT_ID)) AS NODEID,
                    (SELECT[dbo].[FNNC_GET_TREE_GUID](0, PARENT_ID)) AS NODEPID, 'MY_OBJECT_TABLE' AS[TABLE],   
                OBJECT_ID AS OLDID, PARENT_ID AS OLDPID, OBJECT_NAME COLLATE DATABASE_DEFAULT AS NAME,   
                OBJECT_CODE COLLATE database_default AS CODE, OBJECT_TYPE COLLATE database_default AS TYPE,  
                OBJECT_STATE as STATE
FROM dbo.MY_OBJECT_TABLE
WHERE   OBJECT_STATE <> -1
UNION
SELECT(SELECT[dbo].[FNNC_GET_TREE_GUID](1, INDICATOR_ID)) AS NODEID,
                    (SELECT[dbo].[FNNC_GET_TREE_GUID](0, OBJECT_ID)) AS NODEPID, 'MY_INDICATOR_TABLE' AS[TABLE],   
                INDICATOR_ID AS OLDID, OBJECT_ID AS OLDPID, INDICATOR_NAME COLLATE DATABASE_DEFAULT AS NAME,   
                INDICATOR_CODE COLLATE database_default AS CODE, INDICATOR_TYPE COLLATE database_default AS TYPE,  
                INDICATOR_STATE AS STATE
FROM      dbo.MY_INDICATOR_TABLE
WHERE   INDICATOR_STATE <> -1

VIEW_OBJECT_TREE_DATA_GROUP：

CREATE VIEW VIEW_OBJECT_TREE_DATA_GROUP  
AS
SELECT NODEPID,COUNT(0) AS LEAF FROM VIEW_OBJECT_TREE_DATA GROUP BY NODEPID

功能：

USE[MY_DATABASE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION[dbo].[FNNC_GET_TREE_GUID](@TYPE INT, @ID INT)
RETURNS UNIQUEIDENTIFIER
AS
BEGIN

    RETURN CAST(CAST(@TYPE AS binary(4))+CAST(@ID AS varbinary(28)) AS UNIQUEIDENTIFIER)
END

标签： sqlsql-server

解决方案

您可以直接加入这两个视图，而无需使用子查询。

select TD.*, DG.LEAF 
from VIEW_OBJECT_TREE_DATA as TD
     left join VIEW_OBJECT_TREE_DATA_GROUP as DG on DG.NODEPID = TD.NODEID
where TD.OLDPID in (-1, -2) and
      TD.STATE = 1

尽管没有看到这些视图和执行计划，但无法知道是什么让你慢了下来。

推荐阅读