SMO vs DMO script object method - why SMO so verbosed/slow?

hi,

does knows how make more 25 minutes smo version of scripting procedure @ least near of 2 minutes dmo version of same vb.net code?

i've followed recomendations regarding setup setdefaultinitfields() before enumerating objects, etc.

but if see profiler output objstoredprocedure.script(msmoscript) see genererates average 16 lines of tsql against database while same dmo operation generates 4 lines only! (multiply difference scripted objects , 2 dmo against 25 smo minutes)

the difference between scripter.enumdependencies(objstoredprocedure, smo.dependencytype.parents)  , objstoredprocedure.enumdependencies(ctype(me.mdmoscriptdependencies, sqldmo.sqldmo_dependency_type)) huge...


sample results using sql server 2000,  nothwind database, custorderhist procedure:

smo code:
dim tempscript stringcollection = objstoredprocedure.script(msmoscript)

dmo code:
dim tempscript string = objstoredprocedure.script(mdmoscript)


smo scriptoptions used are:
        msmoscript.default = true
        msmoscript.primaryobject = true
        msmoscript.driall = true
        msmoscript.driallkeys = true
        msmoscript.permissions = true
        msmoscript.indexes = true
        msmoscript.triggers = true
        msmoscript.includeifnotexists = true
        msmoscript.bindings = true
        msmoscript.nocollation = true
        msmoscript.extendedproperties = true
        msmoscript.fulltextcatalogs = true
        msmoscript.fulltextindexes = true
        msmoscript.encoding = text.encoding.unicode

note: extendedproperties, fulltextcatalogs , fulltextindexes  tested in "false" simulate lack of options on dmo version (because configured on

dmo scriptoptions used are:

sqldmo.sqldmo_script_type = _
                        sqldmo.sqldmo_script_type.sqldmoscript_default or _
                        sqldmo.sqldmo_script_type.sqldmoscript_primaryobject or _
                        sqldmo.sqldmo_script_type.sqldmoscript_dri_all or _
                        sqldmo.sqldmo_script_type.sqldmoscript_dri_allkeys or _
                        sqldmo.sqldmo_script_type.sqldmoscript_objectpermissions or _
                        sqldmo.sqldmo_script_type.sqldmoscript_indexes or _
                        sqldmo.sqldmo_script_type.sqldmoscript_triggers or _
                        sqldmo.sqldmo_script_type.sqldmoscript_includeifnotexists or _
                        sqldmo.sqldmo_script_type.sqldmoscript_bindings                   
sqldmo.sqldmo_script2_type = _
                        sqldmo.sqldmo_script2_type.sqldmoscript2_extendedproperty or _
                        sqldmo.sqldmo_script2_type.sqldmoscript2_nocollation or _
                        sqldmo.sqldmo_script2_type.sqldmoscript2_fulltextcat or _
                        sqldmo.sqldmo_script2_type.sqldmoscript2_fulltextindex or _
                        sqldmo.sqldmo_script2_type.sqldmoscript2_unicodefile   

note: sqldmo.sqldmo_script2_type not used scripting stored procedures.

profiler dmo output lines!!:
select objectproperty(object_id(n'[dbo].[custorderhist]'), n'execisquotedidenton'), objectproperty(object_id(n'[dbo].[custorderhist]'), n'execisansinullson')
go
if exists (select * dbo.syscomments id=object_id(n'[dbo].[custorderhist]')) select c.text, c.encrypted, c.number, xtype=convert(nchar(2), o.xtype),     datalength(c.text), convert(varbinary(8000), c.text), 0 dbo.syscomments c, dbo.sysobjects o     o.id = c.id , c.id = object_id(n'[dbo].[custorderhist]') order c.number, c.colid option(robust plan)
go
select o.name dbo.sysobjects o o.xtype = n'p' , (objectproperty(o.id, n'execisstartup') = 1)   , (o.name = n'custorderhist')
go
 exec sp_msobjectprivs n'[dbo].[custorderhist]'
go


profiles smo output lines!!! (when extendedproperties, fulltextcatalogs , fulltextindexes  set "false")

use [northwind]
go
select
sp.name [name],
sp.id [id],
sp.crdate [createdate],
ssp.name [schema],
cast(
                case when (objectproperty(sp.id, n'ismsshipped')=1) 1 when 1 = objectproperty(sp.id, n'issystemtable') 1 else 0 end
             bit) [issystemobject],
cast(objectproperty(sp.id,n'execisansinullson') bit) [ansinullsstatus],
cast(objectproperty(sp.id,n'execisquotedidenton') bit) [quotedidentifierstatus],
cast((select top 1 encrypted dbo.syscomments p sp.id = p.id , p.colid=1 , p.number < 2) bit) [isencrypted],
cast(sp.status & 4 bit) [recompile],
cast( objectproperty(sp.id, n'execisstartup') bit) [startup],
cast(case sp.xtype when n'rf' 1 else 0 end bit) [forreplication],
1 [implementationtype]
from
dbo.sysobjects sp
inner join sysusers ssp on ssp.uid = sp.uid
where
(sp.xtype = n'p' or sp.xtype = n'rf')and(sp.name=n'custorderhist' , ssp.name=n'dbo')
go

                declare @useroption int
                select @useroption=c.value master.dbo.sysconfigures c, master.dbo.spt_values v v.type = 'c  ' , not c.status null , v.number = c.config , v.name='user options'
           


select
cast(@useroption & 1 bit) [disabledefaultconstraintcheck],
cast(@useroption & 2 bit) [implicittransactions],
cast(@useroption & 4 bit) [cursorcloseoncommit],
cast(@useroption & 8 bit) [ansiwarnings],
cast(@useroption & 16 bit) [ansipadding],
cast(@useroption & 32 bit) [ansinulls],
cast(@useroption & 64 bit) [abortonarithmeticerrors],
cast(@useroption & 128 bit) [ignorearithmeticerrors],
cast(@useroption & 256 bit) [quotedidentifier],
cast(@useroption & 512 bit) [nocount],
cast(@useroption & 1024 bit) [ansinulldefaulton],
cast(@useroption & 2048 bit) [ansinulldefaultoff],
cast(@useroption & 4096 bit) [concatenatenullyieldsnull],
cast(@useroption & 8192 bit) [numericroundabort],
cast(@useroption & 16384 bit) [aborttransactiononerror]
go
use [northwind]
go
select
null [text],
cast(sp.id nvarchar(20)) + '_' + cast(db_id() nvarchar(20)) + '_0' [definition]
from
dbo.sysobjects sp
inner join sysusers ssp on ssp.uid = sp.uid
where
(sp.xtype = n'p' or sp.xtype = n'rf')and(sp.name=n'custorderhist' , ssp.name=n'dbo')
go
use [northwind]
go
select
cast(c.id nvarchar(20)) + n'_' + cast(db_id() nvarchar(20)) + n'_' + cast(case when c.number > 1 c.number else 0 end nvarchar(20)) [objectidentifier],
c.colid [id],
c.text [text]
from
dbo.sysobjects sp
inner join sysusers ssp on ssp.uid = sp.uid
inner join dbo.syscomments c on c.id=sp.id , case when c.number > 1 c.number else 0 end=0
where
(sp.xtype = n'p' or sp.xtype = n'rf')and(sp.name=n'custorderhist' , ssp.name=n'dbo')
order by
[objectidentifier] asc,[id] asc
go
use [northwind]
go
select
grantee_principal.name + n'_' +
                    convert(nvarchar(10), (case when 1 = grantee_principal.issqlrole 3 when 1 = grantee_principal.isapprole 4 else 2 end)) + n'_' +
                    grantor_principal.name + n'_' +
                    convert(nvarchar(10), (case when 1 = grantor_principal.issqlrole 3 when 1 = grantor_principal.isapprole 4 else 2 end)) + n'_' +
                    convert(nvarchar(10), (prmssn.id)) + n'_' +
                    convert(nvarchar(10), prmssn.protecttype) + n'_' +
                    convert(nvarchar(10), prmssn.action)
             [name]
from
dbo.sysobjects sp
inner join sysusers ssp on ssp.uid = sp.uid
inner join dbo.sysprotects prmssn on prmssn.id=sp.id
inner join sysusers grantee_principal on grantee_principal.uid = prmssn.uid
inner join sysusers grantor_principal on grantor_principal.uid = prmssn.grantor
where
(sp.xtype = n'p' or sp.xtype = n'rf')and(sp.name=n'custorderhist' , ssp.name=n'dbo')
order by
[name] asc
go
use [northwind]
go
select
grantee_principal.name + n'_' +
                    convert(nvarchar(10), (case when 1 = grantee_principal.issqlrole 3 when 1 = grantee_principal.isapprole 4 else 2 end)) + n'_' +
                    grantor_principal.name + n'_' +
                    convert(nvarchar(10), (case when 1 = grantor_principal.issqlrole 3 when 1 = grantor_principal.isapprole 4 else 2 end)) + n'_' +
                    convert(nvarchar(10), (prmssn.id)) + n'_' +
                    convert(nvarchar(10), prmssn.protecttype) + n'_' +
                    convert(nvarchar(10), prmssn.action)
             [name],
grantee_principal.name [grantee],
case when 1 = grantee_principal.issqlrole 3 when 1 = grantee_principal.isapprole 4 else 2 end [granteetype],
grantor_principal.name [grantor],
case when 1 = grantor_principal.issqlrole 3 when 1 = grantor_principal.isapprole 4 else 2 end [grantortype],
case prmssn.id when 0 0 else 1 end  [objectclass],
case prmssn.protecttype when 204 87 when 205 71 when 206 68 end [permissionstate],
null [code],
cast(prmssn.action int) [sqlcodepp]
from
dbo.sysobjects sp
inner join sysusers ssp on ssp.uid = sp.uid
inner join dbo.sysprotects prmssn on prmssn.id=sp.id
inner join sysusers grantee_principal on grantee_principal.uid = prmssn.uid
inner join sysusers grantor_principal on grantor_principal.uid = prmssn.grantor
where
(grantee_principal.name + n'_' +
                    convert(nvarchar(10), (case when 1 = grantee_principal.issqlrole 3 when 1 = grantee_principal.isapprole 4 else 2 end)) + n'_' +
                    grantor_principal.name + n'_' +
                    convert(nvarchar(10), (case when 1 = grantor_principal.issqlrole 3 when 1 = grantor_principal.isapprole 4 else 2 end)) + n'_' +
                    convert(nvarchar(10), (prmssn.id)) + n'_' +
                    convert(nvarchar(10), prmssn.protecttype) + n'_' +
                    convert(nvarchar(10), prmssn.action)
            =n'public_3_dbo_2_821577965_205_224')and((sp.xtype = n'p' or sp.xtype = n'rf')and(sp.name=n'custorderhist' , ssp.name=n'dbo'))
go
use [northwind]
go
select
cast(param.colid int) [id],
param.name [name]
from
dbo.sysobjects sp
inner join sysusers ssp on ssp.uid = sp.uid
inner join syscolumns param on (param.number = 1) , (param.id=sp.id)
where
(sp.xtype = n'p' or sp.xtype = n'rf')and(sp.name=n'custorderhist' , ssp.name=n'dbo')
order by
[id] asc
go
use [northwind]
go
select
nsp.number [number]
from
dbo.sysobjects sp
inner join sysusers ssp on ssp.uid = sp.uid
inner join syscomments nsp on (nsp.colid = 1 , nsp.number > 1) , (nsp.id=sp.id)
where
(sp.xtype = n'p' or sp.xtype = n'rf')and(sp.name=n'custorderhist' , ssp.name=n'dbo')
order by
[number] asc
go

profiles smo output lines (when extendedproperties, fulltextcatalogs , fulltextindexes  set "true")

use [northwind]
go
select
sp.name [name],
sp.id [id],
sp.crdate [createdate],
ssp.name [schema],
cast(
                case when (objectproperty(sp.id, n'ismsshipped')=1) 1 when 1 = objectproperty(sp.id, n'issystemtable') 1 else 0 end
             bit) [issystemobject],
cast(objectproperty(sp.id,n'execisansinullson') bit) [ansinullsstatus],
cast(objectproperty(sp.id,n'execisquotedidenton') bit) [quotedidentifierstatus],
cast((select top 1 encrypted dbo.syscomments p sp.id = p.id , p.colid=1 , p.number < 2) bit) [isencrypted],
cast(sp.status & 4 bit) [recompile],
cast( objectproperty(sp.id, n'execisstartup') bit) [startup],
cast(case sp.xtype when n'rf' 1 else 0 end bit) [forreplication],
1 [implementationtype]
from
dbo.sysobjects sp
inner join sysusers ssp on ssp.uid = sp.uid
where
(sp.xtype = n'p' or sp.xtype = n'rf')and(sp.name=n'custorderhist' , ssp.name=n'dbo')
go

                declare @useroption int
                select @useroption=c.value master.dbo.sysconfigures c, master.dbo.spt_values v v.type = 'c  ' , not c.status null , v.number = c.config , v.name='user options'
           


select
cast(@useroption & 1 bit) [disabledefaultconstraintcheck],
cast(@useroption & 2 bit) [implicittransactions],
cast(@useroption & 4 bit) [cursorcloseoncommit],
cast(@useroption & 8 bit) [ansiwarnings],
cast(@useroption & 16 bit) [ansipadding],
cast(@useroption & 32 bit) [ansinulls],
cast(@useroption & 64 bit) [abortonarithmeticerrors],
cast(@useroption & 128 bit) [ignorearithmeticerrors],
cast(@useroption & 256 bit) [quotedidentifier],
cast(@useroption & 512 bit) [nocount],
cast(@useroption & 1024 bit) [ansinulldefaulton],
cast(@useroption & 2048 bit) [ansinulldefaultoff],
cast(@useroption & 4096 bit) [concatenatenullyieldsnull],
cast(@useroption & 8192 bit) [numericroundabort],
cast(@useroption & 16384 bit) [aborttransactiononerror]
go
use [northwind]
go
select
null [text],
cast(sp.id nvarchar(20)) + '_' + cast(db_id() nvarchar(20)) + '_0' [definition]
from
dbo.sysobjects sp
inner join sysusers ssp on ssp.uid = sp.uid
where
(sp.xtype = n'p' or sp.xtype = n'rf')and(sp.name=n'custorderhist' , ssp.name=n'dbo')
go
use [northwind]
go
select
cast(c.id nvarchar(20)) + n'_' + cast(db_id() nvarchar(20)) + n'_' + cast(case when c.number > 1 c.number else 0 end nvarchar(20)) [objectidentifier],
c.colid [id],
c.text [text]
from
dbo.sysobjects sp
inner join sysusers ssp on ssp.uid = sp.uid
inner join dbo.syscomments c on c.id=sp.id , case when c.number > 1 c.number else 0 end=0
where
(sp.xtype = n'p' or sp.xtype = n'rf')and(sp.name=n'custorderhist' , ssp.name=n'dbo')
order by
[objectidentifier] asc,[id] asc
go
use [northwind]
go
select
grantee_principal.name + n'_' +
                    convert(nvarchar(10), (case when 1 = grantee_principal.issqlrole 3 when 1 = grantee_principal.isapprole 4 else 2 end)) + n'_' +
                    grantor_principal.name + n'_' +
                    convert(nvarchar(10), (case when 1 = grantor_principal.issqlrole 3 when 1 = grantor_principal.isapprole 4 else 2 end)) + n'_' +
                    convert(nvarchar(10), (prmssn.id)) + n'_' +
                    convert(nvarchar(10), prmssn.protecttype) + n'_' +
                    convert(nvarchar(10), prmssn.action)
             [name]
from
dbo.sysobjects sp
inner join sysusers ssp on ssp.uid = sp.uid
inner join dbo.sysprotects prmssn on prmssn.id=sp.id
inner join sysusers grantee_principal on grantee_principal.uid = prmssn.uid
inner join sysusers grantor_principal on grantor_principal.uid = prmssn.grantor
where
(sp.xtype = n'p' or sp.xtype = n'rf')and(sp.name=n'custorderhist' , ssp.name=n'dbo')
order by
[name] asc
go
use [northwind]
go
select
grantee_principal.name + n'_' +
                    convert(nvarchar(10), (case when 1 = grantee_principal.issqlrole 3 when 1 = grantee_principal.isapprole 4 else 2 end)) + n'_' +
                    grantor_principal.name + n'_' +
                    convert(nvarchar(10), (case when 1 = grantor_principal.issqlrole 3 when 1 = grantor_principal.isapprole 4 else 2 end)) + n'_' +
                    convert(nvarchar(10), (prmssn.id)) + n'_' +
                    convert(nvarchar(10), prmssn.protecttype) + n'_' +
                    convert(nvarchar(10), prmssn.action)
             [name],
grantee_principal.name [grantee],
case when 1 = grantee_principal.issqlrole 3 when 1 = grantee_principal.isapprole 4 else 2 end [granteetype],
grantor_principal.name [grantor],
case when 1 = grantor_principal.issqlrole 3 when 1 = grantor_principal.isapprole 4 else 2 end [grantortype],
case prmssn.id when 0 0 else 1 end  [objectclass],
case prmssn.protecttype when 204 87 when 205 71 when 206 68 end [permissionstate],
null [code],
cast(prmssn.action int) [sqlcodepp]
from
dbo.sysobjects sp
inner join sysusers ssp on ssp.uid = sp.uid
inner join dbo.sysprotects prmssn on prmssn.id=sp.id
inner join sysusers grantee_principal on grantee_principal.uid = prmssn.uid
inner join sysusers grantor_principal on grantor_principal.uid = prmssn.grantor
where
(grantee_principal.name + n'_' +
                    convert(nvarchar(10), (case when 1 = grantee_principal.issqlrole 3 when 1 = grantee_principal.isapprole 4 else 2 end)) + n'_' +
                    grantor_principal.name + n'_' +
                    convert(nvarchar(10), (case when 1 = grantor_principal.issqlrole 3 when 1 = grantor_principal.isapprole 4 else 2 end)) + n'_' +
                    convert(nvarchar(10), (prmssn.id)) + n'_' +
                    convert(nvarchar(10), prmssn.protecttype) + n'_' +
                    convert(nvarchar(10), prmssn.action)
            =n'public_3_dbo_2_821577965_205_224')and((sp.xtype = n'p' or sp.xtype = n'rf')and(sp.name=n'custorderhist' , ssp.name=n'dbo'))
go
use [northwind]
go

                create table #extprops(owner sysname null, objname sysname null, name sysname, value sql_variant null)
           

if not (n'dbo' null )
begin
    insert #extprops (owner, objname, name, value) select null, objname, name, value ::fn_listextendedproperty (null, 'user', n'dbo', n'procedure', n'custorderhist', default, default)
    update #extprops set owner = n'dbo'
end
else
begin
    declare @owner sysname
    declare @name sysname
   
    declare crs insensitive cursor
        ( select
ssp.name [schema],
sp.name [name]
from
dbo.sysobjects sp
inner join sysusers ssp on ssp.uid = sp.uid
where
(sp.xtype = n'p' or sp.xtype = n'rf')and(sp.name=n'custorderhist' , ssp.name=n'dbo') )
    read
    open crs
    fetch crs @owner, @name
    while @@fetch_status >= 0
    begin
            insert #extprops (owner, objname, name, value) select null, objname, name, value ::fn_listextendedproperty (null, 'user', @owner, n'procedure', @name, default, default)
            update #extprops set owner = @owner owner null
            fetch crs @owner, @name
    end
    close crs
    deallocate crs
end


select
p.name [name]
from
dbo.sysobjects sp
inner join sysusers ssp on ssp.uid = sp.uid
inner join #extprops p on cast(cast(p.owner varbinary(256)) sysname)=ssp.name , cast(cast(p.objname varbinary(256)) sysname)=sp.name
where
(sp.xtype = n'p' or sp.xtype = n'rf')and(sp.name=n'custorderhist' , ssp.name=n'dbo')
order by
[name] asc

                drop table #extprops
           

go
use [northwind]
go
select
cast(param.colid int) [id],
param.name [name]
from
dbo.sysobjects sp
inner join sysusers ssp on ssp.uid = sp.uid
inner join syscolumns param on (param.number = 1) , (param.id=sp.id)
where
(sp.xtype = n'p' or sp.xtype = n'rf')and(sp.name=n'custorderhist' , ssp.name=n'dbo')
order by
[id] asc
go
use [northwind]
go

                create table #extprops(owner sysname null, parentobjname sysname null,
                        objname sysname null, name sysname , value sql_variant null)
           

if not (n'dbo' null or n'custorderhist' null)
begin
    insert #extprops (owner, parentobjname, objname, name, value) select null, null, objname, name, value ::fn_listextendedproperty (null, 'user', n'dbo', n'procedure', n'custorderhist', n'parameter', default)
    update #extprops set owner = n'dbo', parentobjname = n'custorderhist'
end
else
begin
    declare @owner sysname
    declare @parentname sysname
    declare @name sysname
   
    declare crs insensitive cursor
        ( select
ssp.name [parentowner],
sp.name [parentobjname],
param.name [name]
from
dbo.sysobjects sp
inner join sysusers ssp on ssp.uid = sp.uid
inner join syscolumns param on (param.number = 1) , (param.id=sp.id)
where
(param.name=n'@customerid')and((sp.xtype = n'p' or sp.xtype = n'rf')and(sp.name=n'custorderhist' , ssp.name=n'dbo')) )
    read
    open crs
    fetch crs @owner, @parentname, @name
    while @@fetch_status >= 0
    begin
            insert #extprops (owner, parentobjname, objname, name, value) select null, null, objname, name, value ::fn_listextendedproperty (null, 'user', @owner, n'procedure', @parentname, n'parameter', @name)
            update #extprops set owner = @owner, parentobjname = @parentname owner null
            fetch crs @owner, @parentname, @name
    end
    close crs
    deallocate crs
end


select
p.name [name]
from
dbo.sysobjects sp
inner join sysusers ssp on ssp.uid = sp.uid
inner join syscolumns param on (param.number = 1) , (param.id=sp.id)
inner join #extprops p on cast(cast(p.objname varbinary(256)) sysname)=param.name , cast(cast(p.parentobjname varbinary(256)) sysname)=sp.name , cast(cast(p.owner varbinary(256)) sysname)=ssp.name
where
(param.name=n'@customerid')and((sp.xtype = n'p' or sp.xtype = n'rf')and(sp.name=n'custorderhist' , ssp.name=n'dbo'))
order by
[name] asc

                drop table #extprops
           

go
use [northwind]
go
select
nsp.number [number]
from
dbo.sysobjects sp
inner join sysusers ssp on ssp.uid = sp.uid
inner join syscomments nsp on (nsp.colid = 1 , nsp.number > 1) , (nsp.id=sp.id)
where
(sp.xtype = n'p' or sp.xtype = n'rf')and(sp.name=n'custorderhist' , ssp.name=n'dbo')
order by
[number] asc
go


any light on issue highly appreciated!
thanks in advance,
horacio.-

does knows if of problems fixed on service pack or something?
thanks in advance.

SQL Server  >  SQL Server SMO/DMO