Solved

can anybody help speed this up?

Posted on 2004-10-21
177 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
Question by:gary_j
    13 Comments
     
    LVL 52

    Accepted Solution

    by:
    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 44

    Expert Comment

    by:aikimark
    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
    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
     
    LVL 44

    Expert Comment

    by:Arthur_Wood
    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
    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
    Use a store procedure instead of inline SQL.

    Leon
    0
     
    LVL 5

    Author Comment

    by:gary_j
    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
    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
    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
    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
    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
    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
    I tried everything suggested, and the only thing that worked well was "fill as you go"

    Thank you all very much!
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    IT Security CISA, CISSP & CISM Certification

    Master the advanced techniques required to protect network resources from external threats with the IT Cyber Security bundle. Built around industry best-practice guidelines, the IT Cyber Security bundle consists of three in-depth courses.

    Suggested Solutions

    There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    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…

    875 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now