March 23, 2009 at 5:57 pm
Hi guys!
I've used SMO in some my latest apps but I am really disappointed with performance! It is horrible slowly!
Did you felt this issues also?
What do you think!
March 23, 2009 at 8:54 pm
Yes.
Smo is terribly slow, especially when browsing the higher level collections, especially if you 'load' all of the properties. The principal reason for this is that SMO almost never retrieves its data from the server in Sets or even an object at a time. Rather, it usually retrieves its objects data a single property/column at a time.
I wrote a very extensive "SMO Browser" tool several years ago as a personal project and the thing that took me the most time was coming up with some way to effectively workaround this shortcoming of SMO.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 23, 2009 at 8:58 pm
Heh... what??? You guys didn't know that "SMO" stood for "[font="Arial Black"]S[/font]low [font="Arial Black"]M[/font]oving [font="Arial Black"]O[/font]bjects"? 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
March 24, 2009 at 10:45 am
Jeff Moden (3/23/2009)
Heh... what??? You guys didn't know that "SMO" stood for "[font="Arial Black"]S[/font]low [font="Arial Black"]M[/font]oving [font="Arial Black"]O[/font]bjects"? 😛
HaH! This is most appropriate term I ever saw!
Regards!
March 24, 2009 at 11:05 am
Jeff Moden (3/23/2009)
Heh... what??? You guys didn't know that "SMO" stood for "[font="Arial Black"]S[/font]low [font="Arial Black"]M[/font]oving [font="Arial Black"]O[/font]bjects"? 😛
Actually, considering the nature of the problem, "[font="Arial Black"]S[/font]elects [font="Arial Black"]M[/font]ostly [font="Arial Black"]O[/font]ne" would be more appropiate. 😀
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 24, 2009 at 11:38 am
As already stated, double check you are pointing the SMO properties to the specifics you are querying, scripting, modifying,... !!
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 30, 2011 at 2:25 am
Hey Barry! Can you kindly share your experience from working around the performance problem in SMO? I am considering creating a wrapper that internally uses SMO for tasks that work fine with SMO, but uses a custom implementation (raw SQL) for tasks that are known to have performance issues in SMO. Not sure if this is a good idea. Have you been able to identify these two categories in SMO (ones that work well and ones that don't)?
Bouquets and brickbats are welcome as well 🙂
Thanks in advance for your help!
May 23, 2011 at 3:02 pm
Hello,
SmO was awfully slow to create databases with its tables,stored procedures,views and so on with SMO 2005 SP2 . But i discovered with pleasure that SMO 2008 SP1 is about 5 times quicker than SMO 2005 SP2, but 50% slower than the execution of the script generated by SMO 2008.
It is true that SMO is not interesting to execute "current" queries as these ones used in an application ( you have less options than with SqlCommand ). The ServerConnection.ExecuteWithResults needs to store the statements in a StringCollection ( even for an unique statement ) and is able only to return a DataSet array and not a DataTable. It is why i am using a mix between SMO and SqlClient classes when i have to execute queries especially versus DMV.
Another reproach against SMO : for each new version of SQL Server, the namespaces are changing names, classes are changing their namespaces, and now i am using the Denali Documentation to try to understand what is the use of a property or method, Denali SMO documentation is more complete, while the SMO 2005 or 2008 documentation is to be on the fringe of nought. And why to document protected method/property in a sealed class ? Useless as it is nearly impossible to derivate from a SMO class.
I am fond of SMO but the documentation is too poor to incite DBA/developers to manage servers or databases
Have a nice day
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply