algorithm - 缓慢的存储过程，搜索数据库中的所有表，有什么更好的算法？

Question

我的存储过程有 6 个参数，给定这些参数，它会查看数据库中的所有表，并告诉您哪些表具有指定数据。

例如:

sp_cis_key_combo_snapshot_ListTables '123','',NULL,NULL,'%',NULL

这将返回包含所有该数据的所有表的列表，也就是具有 Incident_Nr = '123'、Agency = '' 且 Module_ID 不为空的任何表。

参数 NULL 表示该列可以存在或不存在于该表中，也就是无关紧要。

参数 '' 表示该列必须存在，但必须为空。

参数 '%' 表示该列必须存在，但不能为空。

如果参数不是上面列出的任何内容，例如 'ABC'，那么它必须返回一个与此完全相同的列。

我的存储过程对 'A%C' 这样的参数没有用处，但它可以接受它们。

现在我需要这个的原因是“我的”数据库非常杂乱无章，我需要知道某些表如何相互关联，但不深入细节，我将如何加速这个存储过程？

USE [RMS]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_cis_key_combo_snapshot_ListTables] (
@incident_nr VARCHAR(12),
@agency VARCHAR(4), 
@suffix1 VARCHAR(3), 
@suffix2 VARCHAR(3), 
@module_id VARCHAR(3), 
@type VARCHAR(2))

AS
BEGIN
SET NOCOUNT ON

DECLARE @tables TABLE (TABLE_NAME SYSNAME)
DECLARE @columns TABLE (COLUMN_NAME SYSNAME)

INSERT INTO @tables
SELECT [TABLE_NAME] = NAME
FROM sysobjects
WHERE type = 'U'
ORDER BY NAME

DECLARE @table_name SYSNAME
DECLARE @has_incident_nr_flag INT
DECLARE @has_agency_flag INT
DECLARE @has_suffix1_flag INT
DECLARE @has_suffix2_flag INT
DECLARE @has_module_id_flag INT
DECLARE @has_type_flag INT
DECLARE @total_flag INT
DECLARE @incident_nr_query VARCHAR(1000)
DECLARE @agency_query VARCHAR(1000)
DECLARE @suffix1_query VARCHAR(1000)
DECLARE @suffix2_query VARCHAR(1000)
DECLARE @module_id_query VARCHAR(1000)
DECLARE @type_query VARCHAR(1000)

start_loop:
SET @table_name = ''

SELECT TOP 1 @table_name = TABLE_NAME
FROM @tables

IF @table_name = ''
    GOTO exit_loop

DELETE
FROM @tables
WHERE TABLE_NAME = @table_name

INSERT INTO @columns
SELECT [COLUMN_NAME] = c.NAME
FROM sysobjects t
INNER JOIN syscolumns c ON c.id = t.id
WHERE t.NAME = @table_name
ORDER BY c.colid

SELECT @has_incident_nr_flag = 0, @has_agency_flag = 0, @has_suffix1_flag = 0, @has_suffix2_flag = 0, @has_module_id_flag = 0, @has_type_flag = 0, @total_flag = 0

IF (@incident_nr IS NOT NULL)
    SET @total_flag = @total_flag + 32

IF (@agency IS NOT NULL)
    SET @total_flag = @total_flag + 16

IF (@suffix1 IS NOT NULL)
    SET @total_flag = @total_flag + 8

IF (@suffix2 IS NOT NULL)
    SET @total_flag = @total_flag + 4

IF (@module_id IS NOT NULL)
    SET @total_flag = @total_flag + 2

IF (@type IS NOT NULL)
    SET @total_flag = @total_flag + 1

IF EXISTS (
        SELECT *
        FROM @columns
        WHERE column_name = 'Incident_Nr'
        )
    AND @incident_nr IS NOT NULL
    SET @has_incident_nr_flag = 32

IF EXISTS (
        SELECT *
        FROM @columns
        WHERE column_name = 'Agency'
        )
    AND @agency IS NOT NULL
    SET @has_agency_flag = 16

IF EXISTS (
        SELECT *
        FROM @columns
        WHERE column_name = 'Suffix_1'
        )
    AND @suffix1 IS NOT NULL
    SET @has_suffix1_flag = 8

IF EXISTS (
        SELECT *
        FROM @columns
        WHERE column_name = 'Suffix_2'
        )
    AND @suffix2 IS NOT NULL
    SET @has_suffix2_flag = 4

IF EXISTS (
        SELECT *
        FROM @columns
        WHERE column_name = 'Module_ID'
        )
    AND @module_id IS NOT NULL
    SET @has_module_id_flag = 2

IF EXISTS (
        SELECT *
        FROM @columns
        WHERE column_name = 'Type'
        )
    AND @type IS NOT NULL
    SET @has_type_flag = 1

SET @incident_nr_query = ' '
SET @agency_query = ' '
SET @suffix1_query = ' '
SET @suffix2_query = ' '
SET @module_id_query = ' '
SET @type_query = ' '

IF (@has_incident_nr_flag = 32)
    SET @incident_nr_query = 'AND Incident_Nr LIKE ' + '''' + @incident_nr + '''' + ' '

IF (@has_agency_flag = 16)
    SET @agency_query = 'AND Agency LIKE ' + '''' + @agency + '''' + ' '

IF (@has_suffix1_flag = 8)
    SET @suffix1_query = 'AND Suffix_1 LIKE ' + '''' + @suffix1 + '''' + ' '

IF (@has_suffix2_flag = 4)
    SET @suffix2_query = 'AND Suffix_2 LIKE ' + '''' + @suffix2 + '''' + ' '

IF (@has_module_id_flag = 2)
    SET @module_id_query = 'AND Module_ID LIKE ' + '''' + @module_id + '''' + ' '

IF (@has_type_flag = 1)
    SET @type_query = 'AND Type LIKE ' + '''' + @type + '''' + ' '

IF (@total_flag = @has_incident_nr_flag + @has_agency_flag + @has_suffix1_flag + @has_suffix2_flag + @has_module_id_flag + @has_type_flag)
BEGIN
    DECLARE @sql VARCHAR(1000)

    SET @sql = 'IF EXISTS (
                        SELECT *
                        FROM dbo.' + @table_name + ' 
                        WHERE 1=1 ' + @incident_nr_query + @agency_query + @suffix1_query + @suffix2_query + @module_id_query + @type_query + '
                        ) ' +  'BEGIN print ' + '''' + @table_name + '''' + ' END'

    EXEC (@sql)
END

DELETE @columns

GOTO start_loop

exit_loop:
END

Answer 1

首先要提醒您的是，您有六个表具有(至少部分)相同的架构。如果您有六个表，它们都有相同的列，那么通常要做的是创建一个表并将数据存储在那里。

如果由于某种原因(我对此表示怀疑)这是不可能的，您可以使用 indexed view ，这是物化 View 的 MSSQL 名称。根据您的典型工作负载，这可能会帮助您提高性能。它的工作方式是预先计算 View - 无需在每次发生查询时访问表。索引也没有坏处。