[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

can anybody help speed this up?

Posted on 2004-10-21
13
Medium Priority
?
183 Views
Last Modified: 2013-12-25
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
Comment
Question by:gary_j
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +4
13 Comments
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 1600 total points
ID: 12375353
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
 
LVL 46

Expert Comment

by:aikimark
ID: 12377273
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
 
LVL 18

Expert Comment

by:JR2003
ID: 12379016
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
Technology Partners: 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!

 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 12379604
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 12379619
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
 
LVL 29

Expert Comment

by:leonstryker
ID: 12380406
Use a store procedure instead of inline SQL.

Leon
0
 
LVL 5

Author Comment

by:gary_j
ID: 12380685
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
 
LVL 5

Expert Comment

by:IThema
ID: 12380775
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
 
LVL 5

Expert Comment

by:IThema
ID: 12380824
From my experience: Doing just these two actions resulted in reducing the time to load from about 5 minutes to 1 second!
0
 
LVL 5

Expert Comment

by:IThema
ID: 12380841
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
 
LVL 29

Expert Comment

by:leonstryker
ID: 12381607
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
 
LVL 18

Assisted Solution

by:JR2003
JR2003 earned 400 total points
ID: 12383216
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
 
LVL 5

Author Comment

by:gary_j
ID: 12383538
I tried everything suggested, and the only thing that worked well was "fill as you go"

Thank you all very much!
0

Featured Post

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question