SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data replication between view and table within same database


Data replication between view and table within same database

Author
Message
AviLondhe
AviLondhe
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 2
Is it possible to do transaction replication of data from view into table in same database. I have final view based on multiple views but retrieving data from final view takes for ever due to size of data and processing complexity. Would like to replicate data from this final view into physical table for faster performance.

Replication: Won't let me select same database as Publisher and subscriber
TableDiff: Gives me message that it needs PK,GUID,Unique ley on view. Although I have column with NewId() on view, it is not getting detected as unique key by TableDiff.

Server is 2008R2.

--
Dave Ballantyne
Dave Ballantyne
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7262 Visits: 8370
When you replicate a view, then it will look to replicate the base data (i believe).
In any case, sounds like an indexed view would be better for your circumstances, have you tried and ruled one out ?



Clear Sky SQL
My Blog
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40412 Visits: 14413
You cannot replicate a regular view because in a regular view the data is not materialized. You can however replicate an indexed view for that same reason.

It sounds like one of your issues is that you are using nested views. Nesting views in SQL Server is an anti-pattern and it sounds like you have reached a level of complexity to where it is causing problems for the database engine. By attempting to materialize the data in the outer view you are only treating the symptom. I would suggest you do a design and performance review on all your views. You may be better off making some of the inner views referenced in your outer view into indexed views.

See Sin Indulging in Nested Views > The Seven Sins against TSQL Performance (31 July 2012) by Grant Fritchey

Detangling Nested Views (June 30, 2010) by Jen McCown

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
AviLondhe
AviLondhe
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 2
Yes you are correct, I am using nested views that cotains CTEs to process data from various source tables and views. So can't use materilized views. I believe Materilized view will have to be based on actual tables and cannor reference other views.

My problem is that I have few level of tables that involves huge amount of data processing, If I was to convert each of them into table, it would result in various updated that I will need to manage to refresh those tables. Some of them needs to happen when set of records are inserted in source table, so calling such process to refresh destination in trigger would be bad ideda as it would process thousands of records for every record inserted where as it is required only after set of records are inserted in source.

Updates has a ripple effect, that means when two or more source tables are updated they result in updates for two or more destination tables, which in turn would result in further level updates (need to reprocess data). I need to find out alternative way to achieve performance as well as to handle concurant updates to same tables in multi user scenario.

Rgds
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40412 Visits: 14413
The more work you can lift off those outermost views the better. That's what I mean by materializing the inner views. Views referencing views that reference base tables are bad enough. If your view hierarchy is deeper than even a simple two levels then you may need to rethink what's happening. I might suggest you move in the direction of converting some of these views to stored procedures or inline-table-valued functions and get out of the business of using views.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search