Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 184
  • Last Modified:

can anybody help speed this up?

This routine returns about 800 tables, and 23000 total nodes in a treeview
It takes almost three minutes to run.  Is there anything I can do to speed it up?

The connection string I use for g_cnnactive connection is:
        "Provider=SQLOLEDB.1;" & _
        "Persist Security Info=False;" & _
        "User ID=" & strLog & ";" & _
        "Password=" & strPW & ";" & _
        "Initial Catalog=" & strDB & ";" & _
        "Data Source=" & strSrvr


    Set rs1 = g_cnnActive.OpenSchema(adSchemaTables)
   
    Do While Not rs1.EOF
        'tables
        l = l + 1
        Set addNode = tvwMain.Nodes.Add(, , "T" & CStr(l), rs1.Fields("TABLE_NAME"))
        lngNode = lngNode + 1
       
        'add fields
        ll = 0
        rs2.Open "SELECT column_name As [N],data_type As [T],is_nullable As [Nu]," & _
            "character_maximum_length As [M],numeric_precision As [P]," & _
            "numeric_scale As [S] FROM INFORMATION_SCHEMA.COLUMNS WHERE " & _
            "table_name = '" & rs1.Fields("TABLE_NAME") & "'" & _
            " ORDER BY ordinal_position"
       
        Do While Not rs2.EOF
            ll = ll + 1
            Set addNode = tvwMain.Nodes.Add("T" & CStr(l), tvwChild, _
                "T" & CStr(l) & "F" & CStr(ll), rs2.Fields("N"))
                lngNode = lngNode + 1
            rs2.MoveNext
        Loop
       
        rs2.Close
       
        rs1.MoveNext
    Loop
   
    rs1.Close
0
gary_j
Asked:
gary_j
  • 3
  • 2
  • 2
  • +4
2 Solutions
 
Carl TawnSystems and Integration DeveloperCommented:
If you're using a treeview you could try initially just returning all of the tables (rs1 in your case) and then only retrieve the column info each time a node is expanded.  This would mean more database reads, but they would each return a much smaller set of results and should, hopefully, make things appear to happen quicker.

Hope this helps.
0
 
aikimarkCommented:
1. make the treeview control disabled and invisible while you are doing this
2. add the nodes at the same level and then change the parentage in a subsequent pass
3. create a string variable and set it once in the loop
example:
Dim strI as String
I=I+1
strI = CStr(I)
Set addNode = tvwMain.Nodes.Add(, , "T" & strI, rs1.Fields("TABLE_NAME"))
...
Do While Not rs2.EOF
   ll = ll + 1
   Set addNode = tvwMain.Nodes.Add("T" & strI, tvwChild, _
      "T" & strI & "F" & CStr(ll), rs2.Fields("N"))
   lngNode = lngNode + 1
   rs2.MoveNext
Loop

4. Make sure you have declared all numeric variables as Long Integer
5. Consider commenting the lngNode incrementing statements.  They don't appear to be useful and you can get the root node's children count.  These statements shouldn't be slowing you down, but it is an example of extraneous statements.
6. minimize the number of columns returned in the 'columns' query to only those you will need to build the treeview.
example:
        rs2.Open "SELECT column_name As [N], & _
            " FROM INFORMATION_SCHEMA.COLUMNS WHERE " & _
            "table_name = '" & rs1.Fields("TABLE_NAME") & "'" & _
            " ORDER BY ordinal_position"

7. play with other formats of recordset field access, such as
rs1![TABLE_NAME]
rs2![N]
0
 
JR2003Commented:
gary_j,

I agree with carl_tawn, load the data on demand by responding to the node click event in the tree view. Initally all you need to load is the list of tables. You can use the 'Tag' property on each node to store all the information you need to beable to load the column information when it is clicked.

There are 2 reasons why your code is slow:
1) The treeview is intrinsically slow when loading large amounts of data (you are loading 23,000 nodes)

2) You are performing 800 sql querys (1 for each table).

If you decide not to load data on demand when the user clicks a node as suggested above  you could speed up the load a bit by executing one sql statement that selectes the table_name and column information from the column view ordering by "table_name, ordinal_position" and when there is a break in the table name just create a new table node. This way you only need to execute 1 sql statement that returns all the data instead of the 800 you are currently executing.

JR
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Arthur_WoodCommented:
It would also improve performance if you assign the value of rs1.Fields("TABLE_NAME") to a Variable, that way you only need to read fro the recordset once,

Dim strTableName as String
    Set rs1 = g_cnnActive.OpenSchema(adSchemaTables)
   
    Do While Not rs1.EOF
        'tables
        strTableName = rs1.Fields("TABLE_NAME")
        l = l + 1

        Set addNode = tvwMain.Nodes.Add(, , "T" & CStr(l), strTableName)
        lngNode = lngNode + 1
       
        'add fields
        ll = 0
        rs2.Open "SELECT column_name As [N FROM INFORMATION_SCHEMA.COLUMNS WHERE " & _
            "table_name = '" & strTableName & "'" & _
            " ORDER BY ordinal_position"
       
        Do While Not rs2.EOF
            ll = ll + 1
            Set addNode = tvwMain.Nodes.Add("T" & CStr(l), tvwChild, _
                "T" & CStr(l) & "F" & CStr(ll), rs2.Fields("N"))
                lngNode = lngNode + 1
            rs2.MoveNext
        Loop
       
        rs2.Close
       
        rs1.MoveNext
    Loop


AW
0
 
Arthur_WoodCommented:
oops, minor type - should be:

Dim strTableName as String
    Set rs1 = g_cnnActive.OpenSchema(adSchemaTables)
   
    Do While Not rs1.EOF
        'tables
        strTableName = rs1.Fields("TABLE_NAME")
        l = l + 1

        Set addNode = tvwMain.Nodes.Add(, , "T" & CStr(l), strTableName)
        lngNode = lngNode + 1
       
        'add fields
        ll = 0
        rs2.Open "SELECT column_name As [N] FROM INFORMATION_SCHEMA.COLUMNS WHERE " & _
            "table_name = '" & strTableName & "'" & _
            " ORDER BY ordinal_position"
       
        Do While Not rs2.EOF
            ll = ll + 1
            Set addNode = tvwMain.Nodes.Add("T" & CStr(l), tvwChild, _
                "T" & CStr(l) & "F" & CStr(ll), rs2.Fields("N"))
                lngNode = lngNode + 1
            rs2.MoveNext
        Loop
       
        rs2.Close
       
        rs1.MoveNext
    Loop
0
 
leonstrykerCommented:
Use a store procedure instead of inline SQL.

Leon
0
 
gary_jAuthor Commented:
Leon - can you give me a hint on how to return the data from an sp -- I'm not familiar enough with sql to do that, though I suspect this may be the best answer

Arthur_wood -- thanks for the tip.  I didn't realize that using rs1.Fields("TABLE_NAME") was a read -- I thought it was the equivalent of using a variable

aikimark -- most of what you suggest, i already have done.  tvw is not visible, didn't realize that also disabling it would help.  All numeric variables are long. lngNode was temporary for debugging.  I do need all the columns returned, just haven't put them in my nodes yet. -- BUT I'm intersted in #2 -- how would i go about changing the level on a subsequent pass?

carl_tawn & jr2003 -- If i don't load the children, then the + does not appear.  Otherwise I understand your suggestions ...

Thanks to everyone.  I'm gonna play with this a little and see what happens.
0
 
IThemaCommented:
Summarised and speaking from my experience:
- Make the treeview invisible during bulk inserts of nodes. Helps a lot!
- Load only the visible level of nodes. For that, you should build a function/sub that builds only the level of a given ID.

One thing that makes it complete: You can check if the node you're adding also has childs. If a node has a child, you could add a dummy node with the text 'Loading...'. Once a node is expanded, you can check wether it is in fact a dummy node and call the function that builds the tree.
If there's a unique ID, you should set the key of each node to that ID (preceded by a string, otherwise an error occurs, since the node's key property cannot be a numeric value, ie I545). You can set the key of each Dummy node to ie L545, where 545 is the ID of its parent.

ie:
Public Sub BuildTree(ParentID as Long)
  'Retrieve all sub items
  'Make treeview invisible
  'Iterate through the items
  'Check wether a node has childs
  'If a node has childs, add a dummy node
  'Make treeview visible
End Sub

Private Sub MyTreeView1_Expand(Node As MSComctlLib.Node)
  If Left$(Node.Child.Key, 1) = "L" Then
    BuildTree Mid(Node.Child.Key, 2)
  End If
End Sub
0
 
IThemaCommented:
From my experience: Doing just these two actions resulted in reducing the time to load from about 5 minutes to 1 second!
0
 
IThemaCommented:
btw,
  'Iterate through the items
  'Check wether a node has childs
  'If a node has childs, add a dummy node

should be:
  'Start loop through the items
    'Add it to the tree
    'Check if the item has children
      'Add a dummy node that allows you to check wether to load the sub-items and to show that you're actually loading the children.
  'End loop through the items
0
 
leonstrykerCommented:
To return data from a store procedure you really should not need to do much more then that you are doing now.  Create a store procedure with an input parameter.  To call it modify your recordset method to:

strSQL = "Exec usp_MyProcedure '" & rs1.Fields("TABLE_NAME") & "'"

rs2.Open strSQL, g_cnnActive, adOpenStatic, adLockOptimistic

Leon
0
 
JR2003Commented:
You don't have to populate the treeview with all the column information to get the '+' sign, you can just add 1 dummy child node to each table node, this will give you the '+' sign. Then in the _Expand event of the treeview if the child node is one of your dummy ones just remove it and populate it with column information else just expand the node.
0
 
gary_jAuthor Commented:
I tried everything suggested, and the only thing that worked well was "fill as you go"

Thank you all very much!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now