Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SMO performance issues!


SMO performance issues!

Author
Message
Betim Drenica
Betim Drenica
SSC-Enthusiastic
SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)

Group: General Forum Members
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!
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9438 Visits: 9517
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."
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45017 Visits: 39886
Heh... what??? You guys didn't know that "SMO" stood for "Slow Moving Objects"? :-P

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Betim Drenica
Betim Drenica
SSC-Enthusiastic
SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)

Group: General Forum Members
Points: 121 Visits: 52
Jeff Moden (3/23/2009)
Heh... what??? You guys didn't know that "SMO" stood for "Slow Moving Objects"? :-P

HaH! This is most appropriate term I ever saw!

Regards!
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9438 Visits: 9517
Jeff Moden (3/23/2009)
Heh... what??? You guys didn't know that "SMO" stood for "Slow Moving Objects"? :-P


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

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6972 Visits: 8839
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 w00t

- 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" :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
seequell
seequell
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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!
patricklambin
patricklambin
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3508 Visits: 1240
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search