Speed of DMO Access

  • I`ve been playing with the DMO and VB. It seems pretty slow when compared to the speed of the enterprise manager. Do you rekkon this is because I`m using VB?

    Andy.

  • I wouldnt think it would be VB - performance should be about the same in any language, work is done in the COM object. That's not to say that you couldnt make it run slow if you tried to instantiate every object at once. Can you post some code?

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • Heres my code (you can probably see me building a treeview). I`ve thought about using the ListObjects method but can`t figure out the references....

    ---------------------------------------------

    Private Sub cmdConnect_Click()

    Tree1.Refresh

    Tree1.Indentation = 3

    Dim ict1, ict2, ict3 As Integer

    Dim nodDatabase As Node

    Dim nodDatabaseix As Integer

    Dim nodTable As Node

    Dim nodTables As Node

    Dim nodTablesix As Integer

    Dim nodProc As Node

    Dim nodProcs As Node

    Dim nodProcsix As Integer

    Dim nodCols As Node

    Dim nodColsix As Integer

    'Setup images for treeview

    With images.ListImages

    .Add , , LoadPicture(App.Path & "\1.ico", 0)

    .Add , , LoadPicture(App.Path & "\2.ico", 0)

    .Add , , LoadPicture(App.Path & "\3.ico", 0)

    .Add , , LoadPicture(App.Path & "\4.ico", 0)

    End With

    Tree1.ImageList = images

    Set oServer = New SQLDMO.SQLServer

    oServer.Connect txtServer.Text, txtAdmin.Text, txtPassword.Text

    Set oDatabases = oServer.Databases

    ict1 = 1

    ict2 = 1

    ' Iterate thu databases collection and create node per database

    For Each oDatabase In oServer.Databases

    ' set databases node

    Set nodDatabase = Tree1.Nodes.Add()

    nodDatabase.Text = oDatabase.Name

    nodDatabase.Image = 1

    ict2 = 1

    nodDatabaseix = nodDatabase.Index

    ' set tables node

    Set nodTable = Tree1.Nodes.Add(nodDatabaseix, tvwChild)

    nodTable.Text = "Tables"

    nodTable.Image = 2

    nodTablesix = nodDatabase.Index + 1

    ' set procs node

    Set nodProc = Tree1.Nodes.Add(nodDatabaseix, tvwChild)

    nodProc.Text = "Procs"

    nodProc.Image = 3

    nodProcsix = nodDatabase.Index + 2

    ' For each database iterate through tables collection and create

    ' node per table

    For Each oTable In oDatabases(ict1).Tables

    Set nodTables = Tree1.Nodes.Add(nodTablesix, tvwChild)

    nodTables.Text = oTable.Name

    nodTables.Image = 2

    nodColsix = nodTables.Index

    ' For each table iterate though columns collection and create

    ' node per column

    For Each oColumn In oDatabases(ict1).Tables(ict2).Columns

    Set nodCols = Tree1.Nodes.Add(nodColsix, tvwChild)

    With oColumn

    nodCols.Text = .Name & " - " & .Datatype & "(" & .Length & ")"

    nodCols.Image = 4

    End With

    Next oColumn

    ict2 = ict2 + 1

    Next oTable

    ' Iterate though stored procedures and create node per proc

    For Each oProc In oDatabases(ict1).StoredProcedures

    Set nodProcs = Tree1.Nodes.Add(nodProcsix, tvwChild)

    nodProcs.Text = oProc.Name

    nodProcs.Image = 3

    Next oProc

    'Increment database counter

    ict1 = ict1 + 1

    Next oDatabase

    Exit Sub

    End Sub

  • You'll definitely take a performance hit like that, you could be creating thousands of objects to load the treeview. Think about how Explorer works, it only loads files in folders that you view. If you know you'll be using them all, then it makes sense to take the hit up front and load everything. If you'll probably only use one or two db's, I'd say load on demand.

    Psuedo code:

    Connect to server

    Load list of dbs (Note: it might be faster to just query sysdatabases)

    Done.

    User clicks on db node

    See if tables have been loaded (maybe store in tag or elsewhere), if not, load them

    User clicks on table, see if columns have been loaded, etc.

    DMO provides a lot of functionality, you're not using it (yet anyway) so why not just do a couple queries? If you're going to make deeper use of it (build your own EM) then you need to think about which if any objects you need to keep open on the client (db collection, tables, etc) so that you have the info when you need it.

    One last comment. Compare how fast EM loads with the object browser in QA(SQL2K). QA is MUCH faster. I suspect it doesn't use DMO, at least for the initial treeview. This doesn't mean DMO is slower, it means ALL objects have more overhead than plain procedural code combined with recordsets. Im a proponent of objects, but sometimes you have to use more than one solution.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • Cheers,

    I`d thought about that, so I wrote 2 versions of this one that loaded everything at the start and then a load on demand. I suppose it`s got to load quite a bit of information so it`s gonna be slow like.

    I think I might try the DMO from ASP? Anyone done that? Built a small web admin site for instance?

    Andy.

  • No reason it wont work, same issues as with any other asp, have to work harder to not maintain state, also have to evaluate the security of the solution closely. I think BMC has a web admin solution, have not tried it.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • DMO tries to be all things to all people. You lose some speed in exchange for versatility. Nothing wrong with that, but definitely there are times when it will be faster to get just the info you need, or to take a different approach than objects to get it.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • I have also made extensive use of DMO for database comparison and synchronisation. Performance were not a big issue for me as these processes where only for administrative use. What I did discover is that DMO is prone to memory leaks (esp SQL 7 version) - for example cycling through each column in each table in a database is not a good idea! If you have this problem make use of the .Refresh method with the releasememberobjects parameter set to True.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply