January 27, 2008 at 10:29 am
All,i have a stored procedure which does updates on tables existing in a different database on the same server. Does this have an impact on teh performance if we do cross database updates or is itbest to have the table residing in same database from where the storeprocedure is kicked off. Any help on this will be greatly apprecaited.TIA
January 27, 2008 at 7:04 pm
I don't think there's a performance impact, it's more a security issue. There is additional checking of rights when you cross databases, but that's minor. Unless this is a very busy server, it shouldn't matter.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 27, 2008 at 7:44 pm
This is our prodn transactional server which is pretty busy during the day, but this st.proc runs during off hrs as a batch job so shouldnt have any impact on performance correct? thanks for your response.
January 28, 2008 at 1:20 am
You should be fine. However, you could try moving the table to see if it helps? Alternatively, a middle ground could be to create a SYNONYM which may help avoid repeated name resolutions. Give that a go as it requires little effort - I don't think it will make a huge difference however. Let us know how you go.
January 28, 2008 at 7:34 am
One of the table thats used in st.rpoc gets updated repeatedly and thsi has numerous indexes on different columns. wont thsi slow down if there are inserts/updaes happening on this table.
January 28, 2008 at 9:51 am
Yes, it takes longer to do inserts/updates on tables that are heavily indexed because of the extra overhead of indexing the new data. Since you are running these processes during off hours it should minimize the number of users adversely impacted. One thing you can do is make sure the indexing is necessary. If you have indices that are referencing seldom used columns, those indexes should be removed.
[font="Verdana"]EKD[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply