Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SMO performance issues! Expand / Collapse
Author
Message
Posted Monday, March 23, 2009 5:57 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 14, 2013 10:48 AM
Points: 121, Visits: 52
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!
Post #682001
Posted Monday, March 23, 2009 8:54 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #682026
Posted Monday, March 23, 2009 8:58 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,995, Visits: 31,516
Heh... what??? You guys didn't know that "SMO" stood for "Slow Moving Objects"?

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #682027
Posted Tuesday, March 24, 2009 10:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 14, 2013 10:48 AM
Points: 121, Visits: 52
Jeff Moden (3/23/2009)
Heh... what??? You guys didn't know that "SMO" stood for "Slow Moving Objects"?

HaH! This is most appropriate term I ever saw!

Regards!
Post #682579
Posted Tuesday, March 24, 2009 11:05 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
Jeff Moden (3/23/2009)
Heh... what??? You guys didn't know that "SMO" stood for "Slow Moving Objects"?


Actually, considering the nature of the problem, "Selects Mostly One" would be more appropiate.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #682606
Posted Tuesday, March 24, 2009 11:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:47 AM
Points: 7,005, Visits: 8,451
As already stated, double check you are pointing the SMO properties to the specifics you are querying, scripting, modifying,... !!



Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- 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
Post #682648
Posted Saturday, April 30, 2011 2:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, April 30, 2011 2:11 AM
Points: 1, Visits: 0
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!
Post #1101153
Posted Monday, May 23, 2011 3:02 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:35 AM
Points: 871, Visits: 293
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
Post #1113641
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse