How to keep a table in sync with a view

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to keep a table in sync with a view

Author

Topic

swingnchad
Starting Member

4 Posts

Posted - 2011-02-15 : 12:02:02

I need to keep a table in sync with a recursive CTE query (or view) that joins to multiple tables in the same database. The CTE may return different results every time it's ran (if any changes were made to the tables it joins on). The CTE is slow and I can't afford to have a trigger run it since the query that caused the trigger to fire won't return until after the trigger has fired (I don't want a simple update to a single record to take 15 seconds because it's waiting on the trigger to finish). Any details on how to implement the solution in SSMS would be nice too, but not necessary.

One solution would be to have a scheduled job run the CTE, and merge it's results with the table. The scheduled job can run every minute so the table is as up-to-date as possible. But I don't know how to create a scheduled job in Sql Server 2005 or how to Merge the results of a CTE query with a table.

To restate my question:

How do I keep the table up to date as close to real-time as possible using the CTE?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-02-15 : 15:58:05

Unusual that you know how to write a recursive cte but don't know how to create a scheduled job...

I suspect that the ultimate solution to your problem will involve something other that what you are asking. In order to evaluate that you should post DDL for your table structures that underly your view and the DML of your view. Include indexes and approximate row counts of the tables. Also the typical SELECT you would like to make on this final table, ie will it always be for the entire table or more often just for one user,account,region,etc.

To answer your direct question, I don't believe there is a way to maintain a table based on a view without "running" the view code. And I don't think you can index a view if it is based on a recursive cte.

To create a job go to Object Explorer | <your server> | sql server Agent | Jobs. right click "jobs" and select "new job". The job properties has a "schedules" page where you can create a schedule for your job.

Be One with the Optimizer
TG

swingnchad
Starting Member

4 Posts

Posted - 2011-02-15 : 17:30:49

I'm not a DBA but a software engineer who's required to become a DBA when needed :) I'm sure this sounds familiar for a lot of small shops. Here's the info you requested.

DECLARE @tbl TABLE (
Id INT
,[Name] VARCHAR(20)
,ParentId INT
)

INSERT INTO @tbl( Id, Name, ParentId )
VALUES
(1, 'Europe', NULL)
,(2, 'Asia', NULL)
,(3, 'Germany', 1)
,(4, 'UK', 1)
,(5, 'China', 2)
,(6, 'India', 2)
,(7, 'Scotland', 4)
,(8, 'Edinburgh', 7)
,(9, 'Leith', 8)

;
DECLARE @tbl2 table (id int, abbreviation varchar(10), tbl_id int)
INSERT INTO @tbl2( Id, Abbreviation, tbl_id )
VALUES
(100, 'EU', 1)
,(101, 'AS', 2)
,(102, 'DE', 3)
,(103, 'CN', 5)

-- the CTE
;WITH abbr AS (
SELECT a.*, isnull(b.abbreviation,'') abbreviation
FROM @tbl a
left join @tbl2 b on a.Id = b.tbl_id
), abcd AS (
-- anchor
SELECT id, [Name], ParentID,
CAST(([Name]) AS VARCHAR(1000)) [Path],
cast(abbreviation as varchar(max)) abbreviation
FROM abbr
WHERE ParentId IS NULL
UNION ALL
--recursive member
SELECT t.id, t.[Name], t.ParentID,
CAST((a.path + '/' + t.Name) AS VARCHAR(1000)) [Path],
isnull(nullif(t.abbreviation,'')+',', '') + a.abbreviation
FROM abbr AS t
JOIN abcd AS a
ON t.ParentId = a.id
)
SELECT *, [Path] + ':' + abbreviation
FROM abcd

I'm fine with running the view code every time. And no, a recursive CTE can't have an index which is part of the root problem. So here's one solution I'm thinking might work, but I don't know how to do it.

Proposed Solution:
Create an SSIS package that merges the view with a new table called tbl3 which has the exact same structure as the CTE. Using the merge or merge join, I can merge the view with the tbl3 table. The merge results will be applied to the tbl3 table so it will be inserted, updated, or deleted from as needed. I then schedule the ssis package to run using the sql server agent.

If this is the best solution, can someone give me instructions on how to do the merge in ssis? Do I need to apply an identity column to the view and tbl3 table for it to work? I'd like it to be as fast as possible.

swingnchad
Starting Member

4 Posts

Posted - 2011-02-17 : 10:58:50

I figured it out myself. Using the Sql Server Agent, along with the very cool tablediff.exe utility, I was able to keep the view and table in sync using the upsert/delete statements generated by the tablediff util. It required a temp table and a unique column in the view (which I created by concatenating 2 column ids into one (which I knew would make it unique)).

This was a much better solution because I didn't have to truncate the live table or drop it and rename the temp table. Plus it runs in it's own thread/environment so it's ok if it takes a minute to run (vs using a trigger which always needs to be fast).

If anyone wants more details let me know.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources