| Groups | Blog | Home |
sql server replication : Merge Performance
Scenario:
I work for a company that is implementing a large-scale disconnected PDA application. We are using merge replication. We have 204 articles, about 100 of which are bidirectional. Of these 100, about 50 are filtered using joins. Currently, partitioned snapshots take about 2 minutes each to complete. The initial database size is about 50mb and takes about 30 minutes to initialize a subscriber. After this, syncs are generally fast, taking about 2 minutes to complete hundreds of rows. Questions: 1.) What can we do to improve performance of the filters? 2.) Sometimes generation evaluation can cause follow-up syncs to take 5-10X longer. Any ideas why?
Generally the more shallow the joins the better performance. Make sure you
have indexes on the filters as well. Set the retention period to something small to minimize your sync times. There is something very wrong with taking 30 minutes to initialize a subscriber. Do you generate your snapshot frequently? -- RelevantNoise.com - dedicated to mining blogs for business intelligence. Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "Jon" <Jon@discussions.microsoft.com> wrote in message news:5B8C3206-A33D-4DA8-97F7-AAD182215951@microsoft.com... > Scenario: > I work for a company that is implementing a large-scale disconnected PDA > application. We are using merge replication. We have 204 articles, about > 100 of which are bidirectional. Of these 100, about 50 are filtered using > joins. Currently, partitioned snapshots take about 2 minutes each to > complete. The initial database size is about 50mb and takes about 30 > minutes > to initialize a subscriber. After this, syncs are generally fast, taking > about 2 minutes to complete hundreds of rows. > > Questions: > 1.) What can we do to improve performance of the filters? > 2.) Sometimes generation evaluation can cause follow-up syncs to take > 5-10X > longer. Any ideas why? > > Thanks in advance.
Hilary,
Can you please help me with my posting on the 27/09/2007 "Urgent help with replication and data filtering"? Thanks [quoted text, click to view] "Hilary Cotter" wrote:
> Generally the more shallow the joins the better performance. Make sure you > have indexes on the filters as well. > > Set the retention period to something small to minimize your sync times. > > There is something very wrong with taking 30 minutes to initialize a > subscriber. Do you generate your snapshot frequently? > > -- > RelevantNoise.com - dedicated to mining blogs for business intelligence. > > Looking for a SQL Server replication book? > http://www.nwsu.com/0974973602.html > > Looking for a FAQ on Indexing Services/SQL FTS > http://www.indexserverfaq.com > "Jon" <Jon@discussions.microsoft.com> wrote in message > news:5B8C3206-A33D-4DA8-97F7-AAD182215951@microsoft.com... > > Scenario: > > I work for a company that is implementing a large-scale disconnected PDA > > application. We are using merge replication. We have 204 articles, about > > 100 of which are bidirectional. Of these 100, about 50 are filtered using > > joins. Currently, partitioned snapshots take about 2 minutes each to > > complete. The initial database size is about 50mb and takes about 30 > > minutes > > to initialize a subscriber. After this, syncs are generally fast, taking > > about 2 minutes to complete hundreds of rows. > > > > Questions: > > 1.) What can we do to improve performance of the filters? > > 2.) Sometimes generation evaluation can cause follow-up syncs to take > > 5-10X > > longer. Any ideas why? > > > > Thanks in advance. > >
Hilary,
Because of the large database size on the PDA (about 61mb), initial syncs take 30 minutes (although the new device has reduced that time to about 20 mins). I'm not really sure what else we can do to reduce that time. The bulk of the time appears to be a result of applying a massive amount of rows on such a small device. Another question, I noticed that the recgen is very behind the sentgen for all subscribers, even those who have synced recently, successfully. Is this normal? Thanks, Jon [quoted text, click to view] "Hilary Cotter" wrote:
> Generally the more shallow the joins the better performance. Make sure you > have indexes on the filters as well. > > Set the retention period to something small to minimize your sync times. > > There is something very wrong with taking 30 minutes to initialize a > subscriber. Do you generate your snapshot frequently? > > -- > RelevantNoise.com - dedicated to mining blogs for business intelligence. > > Looking for a SQL Server replication book? > http://www.nwsu.com/0974973602.html > > Looking for a FAQ on Indexing Services/SQL FTS > http://www.indexserverfaq.com > "Jon" <Jon@discussions.microsoft.com> wrote in message > news:5B8C3206-A33D-4DA8-97F7-AAD182215951@microsoft.com... > > Scenario: > > I work for a company that is implementing a large-scale disconnected PDA > > application. We are using merge replication. We have 204 articles, about > > 100 of which are bidirectional. Of these 100, about 50 are filtered using > > joins. Currently, partitioned snapshots take about 2 minutes each to > > complete. The initial database size is about 50mb and takes about 30 > > minutes > > to initialize a subscriber. After this, syncs are generally fast, taking > > about 2 minutes to complete hundreds of rows. > > > > Questions: > > 1.) What can we do to improve performance of the filters? > > 2.) Sometimes generation evaluation can cause follow-up syncs to take > > 5-10X > > longer. Any ideas why? > > > > Thanks in advance. > >
that's a hard one. I'll try to get to it today:)
-- RelevantNoise.com - dedicated to mining blogs for business intelligence. Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "Giorgio" <Giorgio@discussions.microsoft.com> wrote in message news:3C23C9AD-D724-429E-B2AA-55F8CFA49BA9@microsoft.com... > Hilary, > Can you please help me with my posting on the 27/09/2007 "Urgent help > with > replication and data filtering"? > Thanks > > "Hilary Cotter" wrote: > >> Generally the more shallow the joins the better performance. Make sure >> you >> have indexes on the filters as well. >> >> Set the retention period to something small to minimize your sync times. >> >> There is something very wrong with taking 30 minutes to initialize a >> subscriber. Do you generate your snapshot frequently? >> >> -- >> RelevantNoise.com - dedicated to mining blogs for business intelligence. >> >> Looking for a SQL Server replication book? >> http://www.nwsu.com/0974973602.html >> >> Looking for a FAQ on Indexing Services/SQL FTS >> http://www.indexserverfaq.com >> "Jon" <Jon@discussions.microsoft.com> wrote in message >> news:5B8C3206-A33D-4DA8-97F7-AAD182215951@microsoft.com... >> > Scenario: >> > I work for a company that is implementing a large-scale disconnected >> > PDA >> > application. We are using merge replication. We have 204 articles, >> > about >> > 100 of which are bidirectional. Of these 100, about 50 are filtered >> > using >> > joins. Currently, partitioned snapshots take about 2 minutes each to >> > complete. The initial database size is about 50mb and takes about 30 >> > minutes >> > to initialize a subscriber. After this, syncs are generally fast, >> > taking >> > about 2 minutes to complete hundreds of rows. >> > >> > Questions: >> > 1.) What can we do to improve performance of the filters? >> > 2.) Sometimes generation evaluation can cause follow-up syncs to take >> > 5-10X >> > longer. Any ideas why? >> > >> > Thanks in advance. >> >> >>
Bump for response.
[quoted text, click to view] "Jon" wrote:
> Hilary, > > Because of the large database size on the PDA (about 61mb), initial syncs > take 30 minutes (although the new device has reduced that time to about 20 > mins). I'm not really sure what else we can do to reduce that time. The > bulk of the time appears to be a result of applying a massive amount of rows > on such a small device. > > Another question, I noticed that the recgen is very behind the sentgen for > all subscribers, even those who have synced recently, successfully. Is this > normal? > > Thanks, > > Jon > > "Hilary Cotter" wrote: > > > Generally the more shallow the joins the better performance. Make sure you > > have indexes on the filters as well. > > > > Set the retention period to something small to minimize your sync times. > > > > There is something very wrong with taking 30 minutes to initialize a > > subscriber. Do you generate your snapshot frequently? > > > > -- > > RelevantNoise.com - dedicated to mining blogs for business intelligence. > > > > Looking for a SQL Server replication book? > > http://www.nwsu.com/0974973602.html > > > > Looking for a FAQ on Indexing Services/SQL FTS > > http://www.indexserverfaq.com > > "Jon" <Jon@discussions.microsoft.com> wrote in message > > news:5B8C3206-A33D-4DA8-97F7-AAD182215951@microsoft.com... > > > Scenario: > > > I work for a company that is implementing a large-scale disconnected PDA > > > application. We are using merge replication. We have 204 articles, about > > > 100 of which are bidirectional. Of these 100, about 50 are filtered using > > > joins. Currently, partitioned snapshots take about 2 minutes each to > > > complete. The initial database size is about 50mb and takes about 30 > > > minutes > > > to initialize a subscriber. After this, syncs are generally fast, taking > > > about 2 minutes to complete hundreds of rows. > > > > > > Questions: > > > 1.) What can we do to improve performance of the filters? > > > 2.) Sometimes generation evaluation can cause follow-up syncs to take > > > 5-10X > > > longer. Any ideas why? > > > > > > Thanks in advance. > > > >
Oops sorry man.
Some folks build local sdf and then ftp it to the pda to get around this. Can you perhaps email me offline and I'll have a quick look at your topology and see if I can figure out what is going on and escalate it to Microsoft if necessary. Use the gmail address I post from. -- RelevantNoise.com - dedicated to mining blogs for business intelligence. Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "Jon" <Jon@discussions.microsoft.com> wrote in message news:D2C1E1E9-12ED-413D-8F87-98D201C51478@microsoft.com... > Bump for response. > > "Jon" wrote: > >> Hilary, >> >> Because of the large database size on the PDA (about 61mb), initial syncs >> take 30 minutes (although the new device has reduced that time to about >> 20 >> mins). I'm not really sure what else we can do to reduce that time. The >> bulk of the time appears to be a result of applying a massive amount of >> rows >> on such a small device. >> >> Another question, I noticed that the recgen is very behind the sentgen >> for >> all subscribers, even those who have synced recently, successfully. Is >> this >> normal? >> >> Thanks, >> >> Jon >> >> "Hilary Cotter" wrote: >> >> > Generally the more shallow the joins the better performance. Make sure >> > you >> > have indexes on the filters as well. >> > >> > Set the retention period to something small to minimize your sync >> > times. >> > >> > There is something very wrong with taking 30 minutes to initialize a >> > subscriber. Do you generate your snapshot frequently? >> > >> > -- >> > RelevantNoise.com - dedicated to mining blogs for business >> > intelligence. >> > >> > Looking for a SQL Server replication book? >> > http://www.nwsu.com/0974973602.html >> > >> > Looking for a FAQ on Indexing Services/SQL FTS >> > http://www.indexserverfaq.com >> > "Jon" <Jon@discussions.microsoft.com> wrote in message >> > news:5B8C3206-A33D-4DA8-97F7-AAD182215951@microsoft.com... >> > > Scenario: >> > > I work for a company that is implementing a large-scale disconnected >> > > PDA >> > > application. We are using merge replication. We have 204 articles, >> > > about >> > > 100 of which are bidirectional. Of these 100, about 50 are filtered >> > > using >> > > joins. Currently, partitioned snapshots take about 2 minutes each to >> > > complete. The initial database size is about 50mb and takes about 30 >> > > minutes >> > > to initialize a subscriber. After this, syncs are generally fast, >> > > taking >> > > about 2 minutes to complete hundreds of rows. >> > > >> > > Questions: >> > > 1.) What can we do to improve performance of the filters? >> > > 2.) Sometimes generation evaluation can cause follow-up syncs to take >> > > 5-10X >> > > longer. Any ideas why? >> > > >> > > Thanks in advance. >> > >> > >> >
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
October 2008
| home · blog · groups | Questions? Comments? Contact the dn |