Pull Replication fails
-
Hi,
we try to set up a Pull-Replication with a dynamic filter on HOSTS_NAME().
The filter is based on a table with the HostName and a Number. The Number helps to Filter a second table which also helps to filter a third table.
Everytime when we generate a new subscrition and start the first pull from the subscriber it does work (very, very slowly). If we start a second subscriber to "pull" the data, the two get into a deadlock.
Is there any way to get more then one subscriber to pull its data from the publisher at the same time, without deadlocking???
The next problem is, that the pull takes about 2-3 hours. Is there any way to get the data faster?
Thanks in advance,
Thorsten
-
Hi Thorsten,
Sorry for the delay, been on a short vacation. What version of SQL and what service pack are you running? I found this on MSDN that relates to SQL7:
http://support.microsoft.com/support/kb/articles/Q224/3/29.ASP
If that doesn't help, could you post DDL for the tables involved so we can see the relationships, more details about your publication?
Andy
-
Hi Andy,
I have found the solution, at least for the first part.
Some of my indexes weren't set right. The table you are going to
join another table for the filtering the clustered index must be set.
For example:
Table1 Inner join Table2 on Table1.CostumerID=Table2.CostumerID
Then there has to be a standard Index on Table1.CostumerID and a CLUSTERED Index on Table2.CostumerID
Not setting the indexes this way caused the SQL-Server to perform a table scan each time the filter was needed. Because of locking most of the table we got a deadlock by running two initial Pull-Subcribtions at the same time.
But now I have another problem:
We have quite big databases with 8 tables in it.
Table1 - Has 3 columns: HostName and UserID (and of course ReplID) (Hostname is the name of the PC)
We set the filter on Hostname=HOST_NAME()
This table1 is joined to Table2 with 2 (3 with ReplID) columns UserID, GroupID in it.(Table1 approx. 40 rows)
Table1 join Table2 on Table1.UserID=Table2.UserID
Table2 is joined on Table3a with (3 with ReplID) columns (GroupID, ValueID) in it.(Table2 approx. 240 rows)
Table2 join Table3a on Table2.GroupID=Table3.GroupID
Table3a is joined on Table3b with 10 (11 with ReplID) columns (ValueID, ....) in it.(Table3a/4a/5a approx. 3 Million rows)
Table3a join Table3b on Table3a.ValueID=Table3b.ValueID(Table3b/4b/5b approx. 0.5 Million rows)
Table2 is joined again on Table4a with (3 with ReplID) columns (GroupID, ValueID) in it.
Table2 join Table4a on Table2.GroupID=Table4.GroupID
Table4a is joined on Table4b with 10 (11 with ReplID) columns (ValueID, ....) in it.
Table4a join Table4b on Table4a.ValueID=Table4b.ValueID
Table2 is joined on Table5a with (3 with ReplID) columns (GroupID, ValueID) in it.
Table2 join Table5a on Table2.GroupID=Table5a.GroupID
Table5a is joined on Table5b with 10 (11 with ReplID) columns (ValueID, ....) in it.
Table5a join Table5b on Table5a.ValueID=Table5b.ValueID
The filter with the joined tables works perfectly, even the first inital Pull does take about 10 Minutes.
The second time I start the Anonymous-Pull Replication on the client it takes about 40 to 60 Minutes until it is completed.
Why does it take so long? Do you know whats wrong? I need to generate on 150 Clients a Dynamic Filtered Subscription.
This would take forever. Do you have any idea?
Thanks in advance.
Thorsten Schwab
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply