Hi, I use updates like this to keep data updated every night on certain tables across databases. update A set A.description = b.description, A.shortdescription = b.shortdescription, A.keywords = b.keywords, A.smallimagepath = b.smallimagepath from sfproducts A join databaseb.dbo.sfproducts b on A.prodid = b.prodid where A.description != b.description, or A.shortdescription != b.shortdescription, or A.keywords != b.keywords, or A.smallimagepath != b.smallimagepath The example above has 150,000+ rows and 170 columns. Updating across databases on the same machine, it usually updates 5000 records a night in less than a minute. We recently split the master tables off to another machine and the updates run for hours before I break them. Is this a limitation of sql server distributed transactions? Any help would be greatly appreciated. A linked server is being used and the servers are on the same internal network. eric this is my first post here, so let me know if you need more info.
Hi, Usally it will not take this much time , have you set your network packet size ! Regards Hemantgiri S. Goswami ghemant@gmail.com "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami
Hi, read BOL for sp_configure , it will shows you the current settings for sql server. Regards Hemantgiri S. Goswami ghemant@gmail.com "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br /><b>it usually updates 5000 records a night in less than a minute.</b><br />We recently <b>split</b> the master tables off to another machine and the updates run for hours before I break them. Is this a limitation of sql server distributed transactions?<br />Any help would be greatly appreciated.<br />A linked server is being used and the servers are on the same internal network.<br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />And one more thing , is it production site ! and how many concurrent users are accessing database in day time !<br /><br />[<img src='/community/emoticons/emotion-1.gif' alt='' />]<br />Regards<br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami<br />
database A supports ecommerce site with 200,000+ page views daily. database B is central admin database where orders get fed into from the 3 frontend databases (1 of which is A in this case) and new product info gets fed out from to the 3 frontend databases. Database B supports 60 concurrent users inside here. Also, I think I may have finally found the problem last night. Updating distributed like this does not seem to like NTEXT fields. 1 of the major tables I was having problems with is working now.