CODEWITHSUNDEEP
algorithmsql-server-2008stored-procedures
TheKobra
TheKobra

Reputation: 911

Slow stored procedure, searches through all tables in a database, what is a better algorithm?

My stored procedure takes 6 parameters and given those parameters it looks through all the tables in a database and tells you which tables have the specified data.

For example: 

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

This would return a list of all the tables that have ALL of that data, aka any table that has Incident_Nr = '123', Agency = '', and Module_ID is anything but empty.

The parameter NULL will mean that the column can or cannot exist in that table, aka it doesn't matter.

The parameter '' will mean that the column has to exist, but has to be empty.

The parameter '%' will mean that the column has to exist, but cannot be empty.

If the parameter is anything but what is listed above like 'ABC' then it must return a column with that exactly.

My stored procedure has no use for parameters like 'A%C', but it can take them.

Now the reason I need this is that "my" database is terribly unorganized and I need to know how certain tables relate to each other, without going into detail, how would I speed up this stored procedure?

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

Upvotes: 0

Views: 382

Answers (2)

Jeff B
Jeff B

Reputation: 545

Try running the SQL (not the stored proc but the SQL inside of it) in SQL Management Studio with the execution plan turned on. This will start to give you an idea where some of the slow down is.

A couple of observations: Where you do if ( exists ( select * from ....) and @x is not null - for grins try if ( @x is not null ) first, then the if exists. I doubt it will help much but it could. Instead of doing if ( exists ( select *... how about if ( exists ( select 1... Yes that is the number one. If the tables are very wide (lots of large fields) that may be slowing you down. Especially if there are any text fields.

I have to commend you on attempting to deal with a bad situation. I have said in the past 'It's really easy to design a really bad database. Designing a good one - that takes talent.'

Good luck.

Upvotes: 0

Filip
Filip

Reputation: 2358

The first thing that should alarm you is that you have six tables with (at least partially) identical schemas. If you have six tables that all have the same columns, the usual thing to do is to make one table and store your data there.

If this is impossible for some reason (which I doubt), you can use an indexed view, which is the MSSQL name for materialized views. Depending on your typical workload, this might help you increase your performance. The way it works is the view is precomputed - removing the need to access the tables every time a query occurrs. The indexing doesn't hurt, either.

Upvotes: 1

Related Questions