SMO vs SQL-DMO
-
Hi,
Did anybody compare the performance of scripting operations with SQL-DMO vs SMO? What other reasons should be considered, to choose between two solutions?
Thanks.
-
Mainly... SMO works both for 2000 and 2005. So any code you write with SMO will be usable for a very long period of time. Not sure about SQL-DMO. As for speed I don't know either well enough to compare. But it feels like comparing an apple and an orange... they both look the same but they are different!
Also if you want pure speed you pretty have have to make the scripts yourself (from a fellow developper).
-
SQL-DMO has been deprecated so I guess it will go away (in next SQL server release?)
-
M$ would like for you to use SMO. Its the way they are going so you might as well have your foot into it already. Besides SMO works with both 2005 and 2000 as mentioned earlier on.
I don't know much about the performance but if history is to go by when concerning M$, it might be slower.
-
I can't imagine this being faster. You have to make it work both for 2000 and 2005 at the same time... Maybe as fast but certainly not faster. I mean how many ways are there to concatenate a string. Once you get the fastest one, you can't speed this up many folds.
-
The official stance is that Microsoft does not support SMO in VBScript. VB.Net (2005) is the preferred method and DMO is supported via a .Net wrapper around the DMO COM.
As for speed, I converted a fairly lenghty VBScript (SQL-DMO) that will "clone" a server (minus data) for DR purposes to VB.Net with SQL-SMO and don't see any difference in the speed of execution.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
-
They both work for both 2000 and 2005 databases.
SMO has a fuller toolset, but they're both incomplete (try generating ALTER PROC scripts). SQL-DMO is deprecated and so it won't be extended, whereas SMO is in its infancy and will hopefully be enhanced in the coming years. SMO supposedly supports dependency discovery i.e. discover dependency information at runtime rather than rely on stored data, but I've found it to be maybe 95% reliable (whereas sysdepends was so unreliable as to be useless.)
Performance wise, I've no idea, but that's the least of my worries. It might take me 1 minute to generate a script, and 3 days to get it working properly! I'd rather have a working script that takes 5 minutes to generate.
Regards,
Dave McKinney.
-
If you have SS2K and SS2005 on the same box as well as VB6 and Visual Studion, DMO wouldn't work. Period. So I had to spent a bunch of time converting my projects to SMO.
-
Hi Barsuk,
I have VB6, VS2003, VS2005, SS2K, SS2005 all on the same box, and have no problems using both.
-
Hi,
How did you convert your project into SMO?
I need to list all available servers ( could be from SS2K or SS2005 ) and after selecting the server list all the databases.
I am using Powerbuilder10.5 and the code for DMO to list all SS2K server is:
/*
oleobject ole_CallSQLServerApp
oleobject ole_namelist
Long ll_status
ole_CallSQLServerApp = create oleobject
ll_status = ole_CallSQLServerApp.ConnectToNewObject("SQLDMO.Application")
// Enumerate all of the avaliable SQLServers and add the names to the list box.
//li_count = ole_CallSQLServerApp.ListAvailableSQLServers.Count
ole_namelist = ole_CallSQLServerApp.ListAvailableSQLServers
li_count = ole_namelist.Count
For i = 1 To li_count
ddlb_msservers.insertitem(ole_namelist.Item(i),i)
Next
Destroy ole_CallSQLServerApp
Destroy ole_namelist
End If
*/
Thanks in advance for any help.
Rizvi
-
kasper (11/16/2006)
Hi,Did anybody compare the performance of scripting operations with SQL-DMO vs SMO? What other reasons should be considered, to choose between two solutions?
Thanks.
Heh... now wait just a cotton-pickin'-minute ;)... what is it that you want to do in either? There may be an even more efficient thing you can use depending on what it is you're trying to do.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Jeff.....are you replying to someones post from 2006!?
Perhaps he's found a solution by now! 😉
-
Heh... WOW! C-O-F-F-E-E N-E-E-D-E-D H-E-R-E! 😛
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I was subscribed to this thread as well. And Jeff only replied to it because someone else posted another solution a couple minutes before him.
When replying to a thread, I never checked when the op started the thread, only if I have something valiable to add to it. When someone trolls here from google, the date of the thread and replies have no bearing on helping him towards a solution.
-
Actually, I'd still like to know (if the OP ever gets this), what either was being used for. Sometimes, there's a T-SQL solution that will blow both methods out of the water. 😉
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply