May 30, 2006 at 3:51 pm
Hi,
My replication is setup in SQLServer 2005, the publication uses a pivot table (or so called by me) to filter a host of other tables in the publication. The ID from the pivot table is the FK/Filter to the joined tables.
I have timeout issues while updating the pivot table and I suspect it has to do with the snapshot triggers trying to propagate the update for the whole publication in the same instant causing the update to the pivot table to timeout and fail.
When the publication is dropped the update goes through in a breeze.
I would appreciate any inputs/resolution/work-arounds.
Thanks
Reggie
June 2, 2006 at 8:00 am
This was removed by the editor as SPAM
June 14, 2006 at 7:55 pm
June 14, 2006 at 8:50 pm
Its a merge replication. The main table has a column that has the host_name filter applied to it. The ID colum which is a uniqueidentifier and PK is also the rowguidcolumn for the table. The ID col of the main table is the filter to a bunch of lower level tables.
An update to the host_name filter column triggers the merge update trigger which then goes to update all the join filters. This update takes between 30 to 70 seconds in the query analyzer. In the mgmt console and in RMO code (from where the replication is worked) the update times out.
Updates to all other columns of the main table as well as to those filtering off the main table go through in a wink.
Thanks
Reggie
June 14, 2006 at 10:48 pm
Just to make sure I am clear on this.
You make an update to your filter column.
Then you cascade those changes down to all of the related records.
Can I ask why you need to update your filter column?
I only ask because this is not something I would do and maybe you have made a rod for your own back. Obviously, without understanding the need for this update it is hard to make any insightful comments.
June 15, 2006 at 10:47 am
Yes the update needs to be made to the filter column since its a regular column of the table. Let me be more precise. The table in question is called franchise which has a field called 'name'. This is also column to which the host_name filter is applied. So logically a real franchise replicates by providing its name as the host_name parameter. The franchise name can change since the PK is a of the franchise table is a col called ID which is a uniqueidentifier and also the rowguidcol. Besides, there would inserts and deletes to the franchise table. The rest of the tables filter off the ID of the franchise table. So a table called customer will have its own ID col and another col called FranID which filters off the ID of the franchise table.
I do not cascade any changes. The moment a row in the franchise table is updated, if the update is to the name column, the merge trigger kicks in and works on the joins that tie the other tables to the franchise table. This update returns in 30-110 secs which is unacceptable. The query completes only in the query analyzer and times out when you manipulate the name col of the franchise table directly in the manangement console table view and when the real web application tries to update the franchise table via code using the sql client command.
This model of replication by the way was depolyed and working in sql 2000.
Hope I have provided helpful information for understanding the situation.
Thanks
Reggie
June 15, 2006 at 8:10 pm
OK, I understand your table links - franchise has an ID which is the FK of other tables in the replication scenario.
I suspect that you need to find out exactly what is running when you make an update to this column.
You say the merge trigger. Do you know which one? Do you know what it is trying to do?
Answering these questions may help you to see where the problem lies.
Also, given that it works in SQL 2000, there may be a different behaviour on SQL 2005.
Would I be right in assuming that you have dynamic snapshots or partition groups as part of your replication setup? This might suggest that SQL is doing something under the hood that takes some time. Always interesting though that using the Management Studio and the Query Analyser give you differing results (not a problem specific to SQL 2005 either).
Sorry I can't be more specific. Hopefully you will be able to dig around a little to see what goes on in the background after you do the update. Perhaps using SQL profiler to too look at what processes are running.
Keep posting as you find dtuff and if it rings any bells I'm sure someone will understand the issue and be able to offer assistance.
June 21, 2006 at 10:43 am
The root cause is that when the Franchise table is updated or inserted to, the merge triggers installed on the table by the replication, fire (the update trigger when there is an update and the insert trigger when there is an insert). The trigger, which has massive code effectively tries to propogate the update via the snapshot to all the other tables that are joined to the Franchsie table (via merge filters- Franchise.ID = someTable.FranID). This action is rolled with the actual update (since its a trigger action) and takes from 30-110 secs. The transaction completes in the query analyzer but fails in code with a time out (the actual update completes as can be seen from the profiler trace but the sql client in code or the mgmt console does not wait long enough and throw a timeout exception.
Yes these are dynamic snapshots with every subscriber providing the host_name() parameter for its name and getting a snapshot for itself. Eventually the subscribers may number to 1000; I have no idea what will happen then.
Earlier all profiler results suggested that update to the filtered column is the long one but I have found that update to any col takes the same amount of time.
I have a work around in place where I have another table which has only the Franchise.ID and Franchise.Name cols. All rows from Franchise are populated to this table. I have applied the host name filter to this new table's name col and joined all the other tables to the id of this new table (the names will be cast in stone). This is an insert only table and so no updates are carred out to this table. With this approach the update problem is solved since the main update action is isolated since its to the Franchise table and not to this new table but the insert problem remains. I intend to do an end of the day service to carry out the inserts to Franchise to the new table. This is the only workable solution I have been able to come up with. I am working with MSDN support and the SQL Server Dev team and they seem to suggest that in 2005 all snapshot currency is encapsulated in the triggers and there will be overheads with large joins (70+ in my case)
Thanks
Reggie
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply