正文

如何优化此 SQL 查询

 2023-04-14  224

关键词：

【中文标题】如何优化此 SQL 查询【英文标题】：How can I optimise this SQL query 【发布时间】：2011-12-13 13:06:25 【问题描述】：

我正在编写一个软件，用于识别已放在网络服务器 (CMS) 上但不再需要且应该/可以删除的文件。

首先，我尝试手动重现所有必需的步骤。

我正在使用在 webroot 中执行的批处理脚本来识别服务器上的所有（相关）文件。然后，我将列表导入 SQL Server，表格如下所示：

id   filename
1    filename1.docx
2    files/file.pdf
3    files/filename2.docx
4    files/filename3.docx
5    files/file1.pdf
6    file2.pdf
7    file4.pdf

我还有一个 CMS 数据库（Alterian/Immediate CMC 6.X），它有 2 个存储页面内容的表：page_data 和 PageXMLArchive。

我想扫描数据库以查看第一个表中的文件是否在站点内容中的任何位置引用 - page_data 表中的 p_content 列和 PageXMLArchive 表中的 PageXML 列。

所以我有一个循环，它获取每个文件名并检查它是否在这些表中的任何一个中被引用，如果是则跳过它，如果不是则将其添加到临时表中。

在查询结束时显示临时表。

以下查询：

DECLARE @t as table (_fileName nvarchar(255))
DECLARE @row as int
DECLARE @result as nvarchar(255)

SET @row = 1


WHILE(@row <= (SELECT COUNT(*) FROM ListFileReport))
BEGIN
    SET @result = (SELECT [FileName] FROM ListFileReport WHERE id = @row)

    IF ((SELECT TOP(1) p_content FROM page_data WHERE p_content LIKE '%' + LTRIM(RTRIM(@result)) + '%') IS NULL) OR ((SELECT TOP(1) PageXML FROM PageXMLArchive WHERE PageXML LIKE '%' + LTRIM(RTRIM(@result)) + '%') IS NULL)
    BEGIN
        INSERT INTO @t (_fileName) VALUES(@result)
    END

    SET @row = @row + 1

END

select * from @t

不幸的是，由于我的 SQL 技能不佳，查询需要 2 多个小时才能执行并超时。

如何改进该查询，或更改它以实现类似的事情，而不必在 ntext 字段上运行 1000 条 WHERE x LIKE 语句？我无法对数据库进行任何更改，它必须保持不变（否则它将不受支持 - 对我们的客户来说很重要）。

谢谢

编辑： 目前我正在通过一次批处理几百个结果来解决这个问题。它有效，但需要很长时间。

编辑：

我可以利用全文搜索来实现这一点吗？如果有办法更改架构以实现预期结果，我愿意拍摄数据库快照并处理副本。

page_data 表：

USE [TD-VMB-01-STG]
GO

/****** Object:  Table [dbo].[page_data]    Script Date: 12/13/2011 13:19:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[page_data](
    [p_page_id] [int] NOT NULL,
    [p_title] [nvarchar](120) NULL,
    [p_link] [nvarchar](250) NULL,
    [p_content] [ntext] NULL,
    [p_parent_id] [int] NULL,
    [p_top_id] [int] NULL,
    [p_stylesheet] [nvarchar](50) NULL,
    [p_author] [nvarchar](50) NULL,
    [p_last_update] [datetime] NULL,
    [p_order] [smallint] NULL,
    [p_window] [nvarchar](10) NULL,
    [p_meta_keywords] [nvarchar](1000) NULL,
    [p_meta_desc] [nvarchar](2000) NULL,
    [p_type] [nvarchar](1) NULL,
    [p_confirmed] [int] NOT NULL,
    [p_changed] [int] NOT NULL,
    [p_access] [int] NULL,
    [p_errorlink] [nvarchar](255) NULL,
    [p_noshow] [int] NOT NULL,
    [p_edit_parent] [int] NULL,
    [p_hidemenu] [int] NOT NULL,
    [p_subscribe] [int] NOT NULL,
    [p_StartDate] [datetime] NULL,
    [p_EndDate] [datetime] NULL,
    [p_pageEnSDate] [int] NOT NULL,
    [p_pageEnEDate] [int] NOT NULL,
    [p_hideexpiredPage] [int] NOT NULL,
    [p_version] [float] NULL,
    [p_edit_order] [float] NULL,
    [p_order_change] [datetime] NOT NULL,
    [p_created_date] [datetime] NOT NULL,
    [p_short_title] [nvarchar](30) NULL,
    [p_authentication] [tinyint] NOT NULL,
 CONSTRAINT [aaaaapage_data_PK] PRIMARY KEY NONCLUSTERED 
(
    [p_page_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[page_data] ADD  CONSTRAINT [DF_page_data_p_order]  DEFAULT (0) FOR [p_order]
GO

ALTER TABLE [dbo].[page_data] ADD  CONSTRAINT [DF__Temporary__p_con__1CF15040]  DEFAULT (0) FOR [p_confirmed]
GO

ALTER TABLE [dbo].[page_data] ADD  CONSTRAINT [DF__Temporary__p_cha__1DE57479]  DEFAULT (0) FOR [p_changed]
GO

ALTER TABLE [dbo].[page_data] ADD  CONSTRAINT [DF__Temporary__p_acc__1ED998B2]  DEFAULT (1) FOR [p_access]
GO

ALTER TABLE [dbo].[page_data] ADD  CONSTRAINT [DF__Temporary__p_nos__1FCDBCEB]  DEFAULT (0) FOR [p_noshow]
GO

ALTER TABLE [dbo].[page_data] ADD  CONSTRAINT [DF__Temporary__p_edi__20C1E124]  DEFAULT (0) FOR [p_edit_parent]
GO

ALTER TABLE [dbo].[page_data] ADD  CONSTRAINT [DF__Temporary__p_hid__21B6055D]  DEFAULT (0) FOR [p_hidemenu]
GO

ALTER TABLE [dbo].[page_data] ADD  CONSTRAINT [DF_page_data_p_subscribe]  DEFAULT (0) FOR [p_subscribe]
GO

ALTER TABLE [dbo].[page_data] ADD  CONSTRAINT [DF_page_data_p_pageEnSDate]  DEFAULT (0) FOR [p_pageEnSDate]
GO

ALTER TABLE [dbo].[page_data] ADD  CONSTRAINT [DF_page_data_p_pageEnEDate]  DEFAULT (0) FOR [p_pageEnEDate]
GO

ALTER TABLE [dbo].[page_data] ADD  CONSTRAINT [DF_page_data_p_hideexpiredPage]  DEFAULT (1) FOR [p_hideexpiredPage]
GO

ALTER TABLE [dbo].[page_data] ADD  CONSTRAINT [DF_page_data_p_version]  DEFAULT (0) FOR [p_version]
GO

ALTER TABLE [dbo].[page_data] ADD  CONSTRAINT [DF_page_data_p_edit_order]  DEFAULT (0) FOR [p_edit_order]
GO

ALTER TABLE [dbo].[page_data] ADD  CONSTRAINT [DF_page_data_p_order_change]  DEFAULT (getdate()) FOR [p_order_change]
GO

ALTER TABLE [dbo].[page_data] ADD  CONSTRAINT [DF_page_data_p_created_date]  DEFAULT (getdate()) FOR [p_created_date]
GO

ALTER TABLE [dbo].[page_data] ADD  CONSTRAINT [DF_page_data_p_authentication]  DEFAULT ((0)) FOR [p_authentication]
GO

PageXMLArchive 表：

USE [TD-VMB-01-STG]
GO

/****** Object:  Table [dbo].[PageXMLArchive]    Script Date: 12/13/2011 13:20:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PageXMLArchive](
    [ArchiveID] [bigint] IDENTITY(1,1) NOT NULL,
    [P_Page_ID] [int] NOT NULL,
    [p_author] [nvarchar](100) NULL,
    [p_title] [nvarchar](400) NULL,
    [Version] [int] NOT NULL,
    [PageXML] [ntext] NULL,
    [ArchiveDate] [datetime] NOT NULL,
 CONSTRAINT [PK_PageXMLArchive] PRIMARY KEY CLUSTERED 
(
    [ArchiveID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[PageXMLArchive] ADD  CONSTRAINT [DF_PageXMLArchive_ArchiveDate]  DEFAULT (getdate()) FOR [ArchiveDate]
GO

【问题讨论】：

您能否在问题中包含您的 page_data 和 PageXMLArchive 表的结构？对于初学者，您应该将SELECT COUNT(*) 移动到WHILE 上方并将结果放入变量中。避免对每一行进行该查询。 @MarkBannister 添加了表结构。谢谢 @Johan 谢谢。将 SELECT COUNT(*) 移到 WHILE 循环之上并没有什么区别。刚刚运行查询。 :( 【参考方案1】：

你可以通过多种方式避免循环，这里有一个例子......

SELECT
  *
FROM
  ListFileReport
WHERE
  NOT EXISTS (
     SELECT *
     FROM   page_data
     WHERE  p_content LIKE '%' + LTRIM(RTRIM(ListFileReport.FileName)) + '%'
    )
  AND
  NOT EXISTS (
     SELECT *
     FROM   PageXMLArchive 
     WHERE  PageXML LIKE '%' + LTRIM(RTRIM(ListFileReport.FileName)) + '%'
    )

注意：这消除了循环，因此会产生巨大的改进。但它仍然必须为 ListFileReport 中的每个条目解析整个两个查找表，没有任何巧妙的算法，因为它们可能没有有用的索引。所以它仍然会像狗一样慢，它只会断一条腿而不是两条。

避免使用 LIKE 的唯一方法是解析 page_data 和 PageXMLArchive 表中的所有字段并创建引用文件列表。由于 HTML 和 XML 是非常结构化的，这可以做到，但我会寻找一个库或其他东西来为你做。

然后，您可以创建另一个表，其中包含所有文件，没有重复，并具有适当的索引。查询而不是使用 LIKE 会更快。我一点也不怀疑。但是编写或查找代码将是一件苦差事。

【讨论】：

谢谢。出于测试目的，我运行了查询（仅查询 page_data 表的一部分，将 PageXMLArchive 排除在外），我的查询在 5:04 分钟内完成，而您的查询需要 5:09 分钟。这仅在 page_data 表（1600 行）上。 PageXMLArchive 是 16000 行。将 COUNT 查询移出循环并立即运行。稍后会报告。【参考方案2】：

一个存储过程尤其有一个循环，其中select 和insert 混合会明显减慢查询速度。

如果你可以insert into @table select a, b from table 理想情况下，它将比单独插入每一行快数百万倍。

对于您的示例，可以执行以下操作：

insert into @t (_fileName) select ... from p_content join ...on .. where sth like %sth

如果不适用，请告诉我。

【讨论】：

我不认为这是导致延迟的插入。这是 SELECT * FROM X WHERE Y LIKE '%Z%' 在 2 个表中的 ntext 列上，总共 17000 行。此外，每一行的 ntext 字段将包含整个 XML/HTML 代码页面。

如何优化此 SQL 查询

】如何优化此SQL查询【英文标题】：HowcanIoptimisethisSQLquery【发布时间】：2011-12-1313:06:25【问题描述】：我正在编写一个软件，用于识别已放在网络服务器(CMS)上但不再需要且应该/可以删除的文件。首先，我尝试手动重现所有必需... 查看详情

我如何优化此查询以用于计算响应的 sql

】我如何优化此查询以用于计算响应的sql【英文标题】：Howcanioptimizethisqueryforsqlforcountingresponse【发布时间】：2019-12-2110:17:41【问题描述】：我有一个问题响应表，当我尝试通过查询来计算问题的响应数以创建图表时，加载需要65... 查看详情

如何使用 JOINS 和嵌套 SELECT 优化此 SQL 查询？

】如何使用JOINS和嵌套SELECT优化此SQL查询？【英文标题】：HowcanIoptimizethisSQLquerywithJOINSandnestedSELECT?【发布时间】：2016-10-0415:24:46【问题描述】：我的SQL查询需要大量时间来执行，因为事务表在某种程度上非常巨大。我正在寻找提... 查看详情

优化 SQL：如何重写此查询以提高性能？（使用子查询，摆脱 GROUP BY？）

】优化SQL：如何重写此查询以提高性能？（使用子查询，摆脱GROUPBY？）【英文标题】：OptimizeSQL:Howtorewritethisquerytoboostperformance?(Usesubqueries,getridofGROUPBY?)【发布时间】：2019-12-2900:51:10【问题描述】：我正在使用MySQL5.7.18-16。我使用... 查看详情

有关优化此多层（具有多层子查询）SQL 查询的提示

...含6层子查询的查询，目前的结构是这样的。我期待建议如何：减少层数而不重复相同的语句（例如，我可以将\'casewhenE& 查看详情

如何优化慢 SQL 查询

】如何优化慢SQL查询【英文标题】：HowtooptimiseslowSQLquery【发布时间】：2013-11-2510:42:17【问题描述】：我需要帮助来优化此查询。在存储过程中，这部分执行1小时（所有过程需要2来执行）。过程适用于大量数据。查询适用于两个... 查看详情

如何优化我的 Firebird SQL 查询？

】如何优化我的FirebirdSQL查询？【英文标题】：HowcanIoptimizemyFirebirdSQLquery?【发布时间】：2018-03-2318:57:27【问题描述】：数据库：火鸟3.0此查询用于选择符合特定条件的记录，以检测哪些DEVICEID的LAST_TICK比DEVICEID=INTEGERVALUE知道的其... 查看详情

如何优化这个永远需要的 sql 查询？

】如何优化这个永远需要的sql查询？【英文标题】：HowdoIoptimizethissqlquerythattakesforever?【发布时间】：2015-03-1215:41:07【问题描述】：我正在尝试优化此查询，因为它需要30秒才能执行：SELECTTOP100table1.*FROMtable1(NOLOCK)INNERJOINDB1..table2(N... 查看详情

如何优化 SQL 查询以减少运行时间？

】如何优化SQL查询以减少运行时间？【英文标题】：HowtooptimizetheSQLquerytoreducetheruntime?【发布时间】：2021-05-2406:49:40【问题描述】：下面是我正在使用的表结构示例。该表几乎包含2亿条记录。query_1返回col_1、col_2和col_3的不同组合... 查看详情

SQL Server 中的 SQL 查询优化

...行度，我检查了onthislink并行度数会影响查询的性能，我如何检查服务器的并行度数是多少？如何降低这个成本？我不知道如何才能降低此成查看详情

如何使用 COUNT 和 GROUP BY 优化 sql 查询

】如何使用COUNT和GROUPBY优化sql查询【英文标题】：HowtooptimizesqlquerywithCOUNTandGROUPBY【发布时间】：2017-10-0802:13:02【问题描述】：我有一个表cast大约有150万行，还有一个较小的表watched大约有1000-2000行。两个表共享一个名为movieId的... 查看详情

优化 SQL Server 聚合查询

...布时间】：2013-09-0405:50:13【问题描述】：我正在寻找有关如何优化此查询的想法。我已经评估了执行计划，但它没有为缺少索引提供任何想法，所以只是好奇编写查询是否更好（不同的策略）会导致更快/更轻的查询。SELECT[Place],... 查看详情

如何使用过滤器和分页优化 SQL Server 查询？

】如何使用过滤器和分页优化SQLServer查询？【英文标题】：HowtooptimizeSQLServerquerywithFiltersandPagination?【发布时间】：2018-02-0813:50:33【问题描述】：我需要此查询的优化建议（无权修改索引），出于安全原因，我省略了一些变量名... 查看详情

数据库牛人是如何进行sql优化的？

...整体系统性能，在本文中，我们将讨论SQL查询优化、它是如何完成的、最佳实践及其重要性。SQL查询优化是编写高效的SQL查询，并在执行时间和数据库表示方面提高查询性能的迭代过程，查询优化是几个关系数据库管理系统(RDBMS... 查看详情

在 sql 查询中使用 not in 时优化此 SQL 语句的最佳方法是啥？

】在sql查询中使用notin时优化此SQL语句的最佳方法是啥？【英文标题】：IsitthebestwaytooptimizethisSQLstatementwhenusingnotininsqlquery?在sql查询中使用notin时优化此SQL语句的最佳方法是什么？【发布时间】：2020-06-1016:13:17【问题描述】：名为... 查看详情

大型sql server查询性能优化

...此写了一个查询，但查询很长，加载数据需要太多时间。如何优化此查询？SQLQueryExecutionPlan【问题讨论】：没有人将筛选那个巨大的XML执行计划并给你一个答案。您应该先做一些跑腿工作，然后向我们征求查看详情

如何优化此查询

】如何优化此查询【英文标题】：HowdoIoptimizethisquery【发布时间】：2013-04-2623:41:29【问题描述】：Createtable#tmptble(RuleId,SubjectId,RID,Date)Insertinto#tmptble(RuleId,SubjectId,RID,Date)SelectRuleTable.RuleId,RuleTable.SubjectId,KeyTable.RID,Key 查看详情

如何优化 Join 查询？

】如何优化Join查询？【英文标题】：HowtooptimizetheJoinquery?【发布时间】：2020-06-1506:36:41【问题描述】：对SQL优化此查询有什么建议吗？如果我取消“ANDm.matrix_uuid=98414860655656981ANDc.courses_uuid=98481529319063564"这将需要1.182秒，但如果我... 查看详情

正文

如何优化此 SQL 查询

如何优化此 SQL 查询

我如何优化此查询以用于计算响应的 sql

如何使用 JOINS 和嵌套 SELECT 优化此 SQL 查询？

优化 SQL：如何重写此查询以提高性能？ （使用子查询，摆脱 GROUP BY？）

有关优化此多层（具有多层子查询）SQL 查询的提示

如何优化慢 SQL 查询

如何优化我的 Firebird SQL 查询？

如何优化这个永远需要的 sql 查询？

如何优化 SQL 查询以减少运行时间？

SQL Server 中的 SQL 查询优化

如何使用 COUNT 和 GROUP BY 优化 sql 查询

优化 SQL Server 聚合查询

如何使用过滤器和分页优化 SQL Server 查询？

数据库牛人是如何进行sql优化的？

在 sql 查询中使用 not in 时优化此 SQL 语句的最佳方法是啥？

大型sql server查询性能优化

如何优化此查询

如何优化 Join 查询？

优化 SQL：如何重写此查询以提高性能？（使用子查询，摆脱 GROUP BY？）