SQL Clone
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 (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 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
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35008 Visits: 9518
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 Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215286 Visits: 41979
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

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

Group: General Forum Members
Points: 167 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
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35008 Visits: 9518
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
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29871 Visits: 8986
As already stated, double check you are pointing the SMO properties to the specifics you are querying, scripting, modifying,... !!

Johan


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


- 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
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 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
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4808 Visits: 1241
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