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.
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.