Need to speed up SQL Server SP that uses system metadata

Let me apologize in advance for the length of this question. I don't see how to ask it without giving all the definitions.

I've inherited a SQL Server 2005 database that includes a homegrown implementation of change tracking. Through triggers, changes to virtually every field in the database are stored in a set of three tables. In the application for this database, the user can request the history of various items, and what's returned is not just changes to the item itself, but also changes in related tables. The problem is that in some cases, it's painfully slow, and in some cases, the request eventually crashes the application. The client has also reported other users having problems when someone requests history.

The tables that store the change data are as follows:

CREATE TABLE [dbo].[tblSYSChangeHistory](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [date] [datetime] NULL,
    [obj_id] [int] NULL,
    [uid] [varchar](50) NULL

This table tracks the tables that have been changed. Obj_id is the value that Object_ID() returns.

CREATE TABLE [dbo].[tblSYSChangeHistory_Items](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [h_id] [bigint] NOT NULL,
    [item_id] [int] NULL,
    [action] [tinyint] NULL

This table tracks the items that have been changed. h_id is a foreign key to tblSYSChangeHistory. item_id is the PK of the changed item in the specified table. action indicates insert, delete or change.

CREATE TABLE [dbo].[tblSYSChangeHistory_Details](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [i_id] [bigint] NOT NULL,
    [col_id] [int] NOT NULL,
    [prev_val] [varchar](max) NULL,
    [new_val] [varchar](max) NULL

This table tracks the individual changes. i_id is a foreign key to tblSYSChangeHistory_Items. col_id indicates which column was changed, and prev_val and new_val indicate the original and new values for that field.

There's actually a fourth table that supports this architecture. tblSYSChangeHistory_Objects maps plain English descriptions of operations to particular tables in the database.

The code to look up the history for an item is incredibly convoluted. It's one branch of a very long SP. Relevant parameters are as follows:

@action varchar(50),
@obj_id bigint = 0,
@uid varchar(50) = '',
@prev_val varchar(MAX) = '',
@new_val varchar(MAX) = '',
@start_date datetime = '',
@end_date datetime = ''

I'm storing them to local variables right away (because I was able to significantly speed up another SP by doing so):

declare @iObj_id bigint,
        @cUID varchar(50),
        @cPrev_val varchar(max),
        @cNew_val varchar(max),
        @tStart_date datetime,
        @tEnd_date datetime

set @iObj_id = @obj_id
set @cUID = @uid
set @cPrev_val = @prev_val
set @cNew_val = @new_val
set @tStart_date = @start_date
set @tEnd_date = @end_date

And here's the code from that branch of the SP:

    create table #r (obj_id int, item_id int, l tinyint)
    create clustered index #ri on #r (obj_id, item_id)
    insert into #r 
        select object_id(obj_name), @iObj_id, 0  
            from dbo.tblSYSChangeHistory_Objects 
            where obj_type = 'U' and descr = cast(@cPrev_val AS varchar(150))
    declare @i tinyint, @cnt int
    set @i = 1
    while @i <= 4
    begin
        insert into #r 
            select obj_id, item_id, @i 
                from dbo.vSYSChangeHistoryFK a with (nolock) 
                where exists (select null from #r where obj_id = a.rel_obj_id and item_id = a.rel_item_id and l = @i - 1)
                and not exists (select null from #r where obj_id = a.obj_id and item_id = a.item_id)
        set @cnt = @@rowcount
        insert into #r 
            select rel_obj_id, rel_item_id, @i 
                from dbo.vSYSChangeHistoryFK a with (nolock) 
                where object_name(obj_id) not in (<this is a list of particular tables in the database>) 
                and exists (select null from #r where obj_id = a.obj_id and item_id = a.item_id and l between @i - 1 and @i)
                and not exists (select null from #r where obj_id = a.rel_obj_id and item_id = a.rel_item_id)
        set @i = case @cnt + @@rowcount when 0 then 100 else @i + 1 end
    end
    select date, obj_name, item, [uid], [action], 
        pkey, item_id, id, key_obj_id into #tCH_R 
        from dbo.vSYSChangeHistory a with (nolock) 
        where exists (select null from #r where obj_id = a.obj_id and item_id = a.item_id) 
        and (@cUID = '' or uid = @cUID) 
        and (@cNew_val = '' or [action] = @cNew_val)
    declare ch_item_cursor cursor for 
        select distinct pkey, key_obj_id, item_id 
            from #tCH_R 
            where item = '' and pkey <> ''
    open ch_item_cursor
    fetch next from ch_item_cursor 
        into @cPrev_val, @iObj_id, @iCol_id
    while @@fetch_status = 0
    begin
        set @SQLStr = 'select @val = ' + @cPrev_val + 
            ' from ' + object_name(@iObj_id) + ' with (nolock)' + 
            ' where id = @id'
        exec sp_executesql @SQLStr, 
            N'@val varchar(max) output, @id int', 
            @cNew_val output, @iCol_id
        update #tCH_R 
            set item = @cNew_val 
            where key_obj_id = @iObj_id 
            and item_id = @iCol_id
        fetch next from ch_item_cursor 
            into @cPrev_val, @iObj_id, @iCol_id
    end
    close ch_item_cursor
    deallocate ch_item_cursor
    select date, obj_name, 
        cast(item AS varchar(254)) AS item, 
        uid, [action], 
        cast(id AS int) AS id 
        from #tCH_R 
        order by id
    return

As you can see, the code uses a view. Here's that definition:

ALTER VIEW [dbo].[vSYSChangeHistoryFK]
AS
SELECT     i.obj_id, i.item_id, c1.parent_object_id AS rel_obj_id, i2.item_id AS rel_item_id
FROM         dbo.vSYSChangeHistoryItemsD AS i INNER JOIN
                      sys.foreign_key_columns AS c1 ON c1.referenced_object_id = i.obj_id AND c1.constraint_column_id = 1 INNER JOIN
                      dbo.vSYSChangeHistoryItemsD AS i2 ON c1.parent_object_id = i2.obj_id INNER JOIN
                      dbo.tblSYSChangeHistory_Details AS d1 ON d1.i_id = i.min_id AND d1.col_id = c1.referenced_column_id INNER JOIN
                      dbo.tblSYSChangeHistory_Details AS d1k ON d1k.i_id = i2.min_id AND d1k.col_id = c1.parent_column_id AND ISNULL(d1.new_val, 
                      ISNULL(d1.prev_val, '')) = ISNULL(d1k.new_val, ISNULL(d1k.prev_val, '')) --LEFT OUTER JOIN
UNION ALL
SELECT     i0.obj_id, i0.item_id, c01.parent_object_id AS rel_obj_id, i02.item_id AS rel_item_id
FROM         dbo.vSYSChangeHistoryItemsD AS i0 INNER JOIN
                      sys.foreign_key_columns AS c01 ON c01.referenced_object_id = i0.obj_id AND c01.constraint_column_id = 1 AND col_name(c01.referenced_object_id, 
                      c01.referenced_column_id) = 'ID' INNER JOIN
                      dbo.vSYSChangeHistoryItemsD AS i02 ON c01.parent_object_id = i02.obj_id INNER JOIN
                      dbo.tblSYSChangeHistory_Details AS d01k ON i02.min_id = d01k.i_id AND d01k.col_id = c01.parent_column_id AND ISNULL(d01k.new_val, 
                      d01k.prev_val) = CAST(i0.item_id AS varchar(max))

And finally, that view uses one more view:

ALTER VIEW [dbo].[vSYSChangeHistoryItemsD]
AS
SELECT     h.obj_id, m.item_id, MIN(m.id) AS min_id
FROM         dbo.tblSYSChangeHistory AS h INNER JOIN
                      dbo.tblSYSChangeHistory_Items AS m ON h.id = m.h_id
GROUP BY h.obj_id, m.item_id

Working with the Profiler, it appears that view vSYSChangeHistoryFK is the big culprit, and my testing suggests that the particular problem is in the join between the two copies of vSYSChangeHistoryItemsD and the foreign_key_columns table.

I'm looking for any ideas on how to give acceptable performance here. The client reports sometimes waiting as much as 15 minutes without getting results. I've tested up to nearly 10 minutes with no result in at least one case.

If there were new language elements in 2008 or later that would solve this, I think the client would be willing to upgrade.

Thanks.

Answers


Wow that's a mess. Your big gain should be in removing the cursor. I see 'where exists' - that's nice and efficient b/c as soon as it finds one match it aborts. And I see 'where not exists' - by definition that has to scan everything. Is it finding the top 4? You can do better with using ROW_NUMBER() OVER (PARTITON BY [whatever makes it unique] ORDER BY [whatever your id is]. It's hard to tell. select object_id(obj_name), @iObj_id, 0 makes it seem like only the @i=1 loop actually does anything (?)

If that is what it's doing, you could write it as

SELECT * from
(
select ROW_NUMBER() OVER (PARTITION BY obj_id ORDER BY item_id desc) as Row, 
     obj_id, item_id
FROM bo.vSYSChangeHistoryFK a with (nolock) 
where obj_type = 'U' and descr = cast(@cPrev_val AS varchar(150))
   ) paged
where Row between 1 and 4 
ORDER BY Row

A DBA level change that could help would be to set up a partitioning scheme based on date. Roll over to a new partition every so often. Put the old partitions on different disks. Most queries may only need to hit the recent partition, which will be say 1/5th the size that it used to be, making it much faster without changing anything else.

Not a full answer, sorry. That mess would take hours to parse


Need Your Help

How to encode the evaluation order for a SQL query using Java?

java mysql database parsing sql-parser

I am trying to develop a SQL query evaluator using JSQLParser, and I am really confused as to how do I decide on the evaluation order, like if I have a query of the following form

Hi-res PNG file in ipad's safari

ipad safari png

I have some asp.net site that shows png images that converted from hi-res tiff files.