July 5, 2002 at 6:05 am
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.
July 5, 2002 at 6:15 am
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
July 5, 2002 at 8:53 am
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
July 5, 2002 at 9:39 am
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
July 5, 2002 at 9:56 am
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.
July 5, 2002 at 11:23 am
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
July 15, 2002 at 6:47 am
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
July 18, 2002 at 2:53 am
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