OpenSchema extremely slow 
Author Message
 OpenSchema extremely slow

When the following code is run, it takes *several* seconds, sometimes up to a minute, to retrieve the columns from a table, especially if the table has 50 or more columns. When I bring up the task manager on a W2K machine, it reports that the app is "not responding".

There must be a way to optimize this code. Ideas anyone?

Private Sub GetFieldNames(TableName As String)

On Error GoTo ErrorHandler

  With Me

    '-- clear the fields listbox of previous contents
    .lstFields.Clear

    '-- let user know what's going on
    .sbarMain.Panels(1).Text = "Collecting field information for " & _
                                UCase$(TableName) & "..."

    '-- ** this is what's taking so long **
    Set m_oRs = m_oCn.OpenSchema(adSchemaColumns, _
                                 Array(Empty, Empty, TableName, Empty))

    Do Until m_oRs.EOF()

      .lstFields.AddItem UCase$(m_oRs!COLUMN_NAME)
      m_oRs.MoveNext

    Loop

    '-- replace the "%n%" token with the number of
    '   of records returned in the recordset
    .lblFieldCount.Caption = Replace(FIELD_LIST_CAPTION, _
                                    "%n%", _
                                    CStr(m_oRs.RecordCount))
    .sbarMain.Panels(1).Text = ""

  End With

CleanUp:
  Call CloseRecordset
  Exit Sub

ErrorHandler:
  MsgBox "Error #" & CStr(Err.Number) & vbCrLf & _
          Err.Description, _
          vbExclamation Or vbOKOnly, _
         "Error retrieving names of fields"

  Resume CleanUp

End Sub



Sun, 18 Jul 2004 22:55:12 GMT
 OpenSchema extremely slow

The slowness is in the call to OpenSchema or in the loop?

What database are you using? If you are using Oracle or SQL Server you can ask your DBA to trace the underlying SQL statements to the database catalog.

In general, OpenSchema is faster if you set the two first parameters (owner, schema, etc) rather than suplying Empty.

--
Best regards,

Carlos J. Quintero

MZ-Tools 3.0 freeware 'all-in-one' Add-In for VB6 and VBA: Code Templates, Detect dead code, Favorite Procedures, Favorite Projects, External Utilities, XML Documentation,  Private Clipboards, Select Case Assistant, Sort Procedures, Collapse Projects, Split/Combine Lines, Open Folder of File, Edit File As Text, MsgBox Assistant, Connect String Assistant and many more features:
www.mztools.com


  When the following code is run, it takes *several* seconds, sometimes up to a minute, to retrieve the columns from a table, especially if the table has 50 or more columns. When I bring up the task manager on a W2K machine, it reports that the app is "not responding".

  There must be a way to optimize this code. Ideas anyone?

  Private Sub GetFieldNames(TableName As String)

  On Error GoTo ErrorHandler

    With Me

      '-- clear the fields listbox of previous contents
      .lstFields.Clear

      '-- let user know what's going on
      .sbarMain.Panels(1).Text = "Collecting field information for " & _
                                  UCase$(TableName) & "..."

      '-- ** this is what's taking so long **
      Set m_oRs = m_oCn.OpenSchema(adSchemaColumns, _
                                   Array(Empty, Empty, TableName, Empty))

      Do Until m_oRs.EOF()

        .lstFields.AddItem UCase$(m_oRs!COLUMN_NAME)
        m_oRs.MoveNext

      Loop

      '-- replace the "%n%" token with the number of
      '   of records returned in the recordset
      .lblFieldCount.Caption = Replace(FIELD_LIST_CAPTION, _
                                      "%n%", _
                                      CStr(m_oRs.RecordCount))
      .sbarMain.Panels(1).Text = ""

    End With

  CleanUp:
    Call CloseRecordset
    Exit Sub

  ErrorHandler:
    MsgBox "Error #" & CStr(Err.Number) & vbCrLf & _
            Err.Description, _
            vbExclamation Or vbOKOnly, _
           "Error retrieving names of fields"

    Resume CleanUp

  End Sub



Mon, 19 Jul 2004 00:35:40 GMT
 OpenSchema extremely slow

Carlos,

The slowness is in the call to OpenSchema(). Here's the code where the connection is opened, minus the error trapping stuff. It's opening dBase IV type tables used by a 3rd party POS application. The code that gets the list of tables is very quick. If it means anything, the Borland Database Engine (BDE) is installed

Private Sub OpenConnection(DataDir As String)

  Set m_oCn = New ADODB.Connection

  With m_oCn

    .CursorLocation = adUseClient
    .Mode = (adModeReadWrite Or adModeShareDenyNone)
    .Provider = "MSDASQL.1"

    .Open "Driver={Microsoft dBASE Driver (*.dbf)};" & _
                  "DriverID=533;" & _
                  "DefaultDir=" & DataDir & ";"

  End With

End Sub

This is the code that gets the name of all the tables once the connection is opened:

Private Sub GetTableNames()

  Set m_oRs = m_oCn.OpenSchema(adSchemaTables, _
                               Array(Empty, Empty, Empty, Empty))

  With Me

    Do Until m_oRs.EOF()

      .lstTables.AddItem UCase$(m_oRs!TABLE_NAME)
      m_oRs.MoveNext

    Loop

    .lblTableCount.Caption = Replace(TABLE_LIST_CAPTION, _
                                      "%n%", _
                                      CStr(m_oRs.RecordCount))
  End With

  Call CloseRecordset

End Sub


  The slowness is in the call to OpenSchema or in the loop?

  What database are you using? If you are using Oracle or SQL Server you can ask your DBA to trace the underlying SQL statements to the database catalog.

  In general, OpenSchema is faster if you set the two first parameters (owner, schema, etc) rather than suplying Empty.

  --
  Best regards,

  Carlos J. Quintero

  MZ-Tools 3.0 freeware 'all-in-one' Add-In for VB6 and VBA: Code Templates, Detect dead code, Favorite Procedures, Favorite Projects, External Utilities, XML Documentation,  Private Clipboards, Select Case Assistant, Sort Procedures, Collapse Projects, Split/Combine Lines, Open Folder of File, Edit File As Text, MsgBox Assistant, Connect String Assistant and many more features:
  www.mztools.com



Mon, 19 Jul 2004 05:40:07 GMT
 OpenSchema extremely slow

Since you are using the MSDASQL Provider for ODBC and the dBase ODBC driver, the ODBC API Function used is SQLTables. Read in the ODBC docs about this function and use a tool called ODBC Test 32-bit provided by Microsoft in the ODBC SDK or so to measure the performance using SQLTables. Also, you can trace the ODBC calls to see the underlying statements to the engine using Control Panel, ODBC Data Sources, Trace tab.

--
Best regards,

Carlos J. Quintero

MZ-Tools 3.0 freeware 'all-in-one' Add-In for VB6 and VBA: Code Templates, Detect dead code, Favorite Procedures, Favorite Projects, External Utilities, XML Documentation,  Private Clipboards, Select Case Assistant, Sort Procedures, Collapse Projects, Split/Combine Lines, Open Folder of File, Edit File As Text, MsgBox Assistant, Connect String Assistant and many more features:
www.mztools.com


  Carlos,

  The slowness is in the call to OpenSchema(). Here's the code where the connection is opened, minus the error trapping stuff. It's opening dBase IV type tables used by a 3rd party POS application. The code that gets the list of tables is very quick. If it means anything, the Borland Database Engine (BDE) is installed

  Private Sub OpenConnection(DataDir As String)

    Set m_oCn = New ADODB.Connection

    With m_oCn

      .CursorLocation = adUseClient
      .Mode = (adModeReadWrite Or adModeShareDenyNone)
      .Provider = "MSDASQL.1"

      .Open "Driver={Microsoft dBASE Driver (*.dbf)};" & _
                    "DriverID=533;" & _
                    "DefaultDir=" & DataDir & ";"

    End With

  End Sub

  This is the code that gets the name of all the tables once the connection is opened:

  Private Sub GetTableNames()

    Set m_oRs = m_oCn.OpenSchema(adSchemaTables, _
                                 Array(Empty, Empty, Empty, Empty))

    With Me

      Do Until m_oRs.EOF()

        .lstTables.AddItem UCase$(m_oRs!TABLE_NAME)
        m_oRs.MoveNext

      Loop

      .lblTableCount.Caption = Replace(TABLE_LIST_CAPTION, _
                                        "%n%", _
                                        CStr(m_oRs.RecordCount))
    End With

    Call CloseRecordset

  End Sub


    The slowness is in the call to OpenSchema or in the loop?

    What database are you using? If you are using Oracle or SQL Server you can ask your DBA to trace the underlying SQL statements to the database catalog.

    In general, OpenSchema is faster if you set the two first parameters (owner, schema, etc) rather than suplying Empty.

    --
    Best regards,

    Carlos J. Quintero

    MZ-Tools 3.0 freeware 'all-in-one' Add-In for VB6 and VBA: Code Templates, Detect dead code, Favorite Procedures, Favorite Projects, External Utilities, XML Documentation,  Private Clipboards, Select Case Assistant, Sort Procedures, Collapse Projects, Split/Combine Lines, Open Folder of File, Edit File As Text, MsgBox Assistant, Connect String Assistant and many more features:
    www.mztools.com



Mon, 19 Jul 2004 16:30:16 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. OpenSchema extremely slow

2. ODBC extremely slow

3. SAP/BW extremely slow

4. extremely slow production load, pls help!!

5. SQL Server 2000 jdbc driver is extremely slow !!!!

6. Extremely slow Servers.

7. MMC Performance Extremely Slow

8. SAP/BW extremely slow

9. Pivot Table Extremely slow

10. Distinct count extremely slow

11. Stored Procedures - extremely slow

12. Extremely slow NOT IN


 
Powered by phpBB® Forum Software