none
More Efficient Way to Find & Remove Rows? RRS feed

  • Question

  • I have an Excel file where I'm connecting to 3 different SQL servers to come up with data for a worksheet. Here's what is happening in the VBA procedure.

    1. Connect to SQL Server 1 ("SqlSrv1").
    2. Run query on SqlSrv1 returning around 13K records in an ADO RecordSet ("Rs1").
    3. Truncate worksheet ("WrkSht1").
    4. Copy Rs1 to a WrkSht1.
    5. Connect to SQL Server 2 ("SqlSrv2").
    6. Run query on SqlSrv2 returning around 663K records in an ADO RecordSet ("Rs2").
    7. Loop through records of Rs2 and for each, loop through WrkSht1 used rows to find matching value of a field.
    8. If a match is found between Rs2 and WrkSht1, delete the row.
    9. Connect to SQL Server 3 ("SqlSrv3").
    10. Run query on SqlSrv3 returning around 662K records in an ADO RecordSet ("Rs3").
    11. Loop through records of Rs3 and for each, loop through WrkSht1 used rows to find matching value of a field.
    12. If a match is not found between Rs3 and WrkSht1, delete the row.

    The problem here is that these queries are running on 3 different SQL servers and so I can't put everything into a single query. Using the method above, this procedure is taking almost a full day to run. Can someone tell me if there is a more efficient way to do this, other than an SSIS package, linked servers, etc.?


    Nathon Dalton
    Sr. Lead Developer
    Blog: http://www.nathondalton.com

    Tuesday, July 14, 2015 3:10 PM

Answers

  • Get all your data into your workbook using your queries. 

    Then - do not loop.  looping is bad - use native Excel capabilities whenever you can, so create a formula that returns TRUE or FALSE based on your match criteria - unfortunately, your statement 

    "If a match is found between Rs2 and WrkSht1, delete the row."

    is not specific enough... delete the row from? Rs2? WrkSht1? or both?

    Anyway, the formula should then be used to filter and delete your data - here is an example, where column H (8) is open and available, and the sheets are named "Record Set 1" and "Record Set 2", and the match is based on column A of the two sheets, and the rows are deleted from Record Set 2.
        

    Sub DeleteMatchedValue()
        Dim rngC As Range
        Dim iCol As Integer
        iCol = 8  'Column H is open on sheet Record Set 2
        With Worksheets("Record Set 2")
            .Cells(1, iCol).Value = "Matched?"
            Set rngC = .Range(.Cells(2, iCol), .Cells(.Cells(.Rows.Count, "A").End(xlUp).Row, iCol))
            rngC.Formula = "=NOT(ISERROR(MATCH(A2,'Record Set 1'!A:A,False)))"
            rngC.Value = rngC.Value
            .Range("A:A").Resize(, iCol).AutoFilter Field:=iCol, Criteria1:="TRUE"
            rngC.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            .ShowAllData
            .Columns(iCol).Delete
        End With

    End Sub



    Tuesday, July 14, 2015 3:59 PM

All replies

  • Get all your data into your workbook using your queries. 

    Then - do not loop.  looping is bad - use native Excel capabilities whenever you can, so create a formula that returns TRUE or FALSE based on your match criteria - unfortunately, your statement 

    "If a match is found between Rs2 and WrkSht1, delete the row."

    is not specific enough... delete the row from? Rs2? WrkSht1? or both?

    Anyway, the formula should then be used to filter and delete your data - here is an example, where column H (8) is open and available, and the sheets are named "Record Set 1" and "Record Set 2", and the match is based on column A of the two sheets, and the rows are deleted from Record Set 2.
        

    Sub DeleteMatchedValue()
        Dim rngC As Range
        Dim iCol As Integer
        iCol = 8  'Column H is open on sheet Record Set 2
        With Worksheets("Record Set 2")
            .Cells(1, iCol).Value = "Matched?"
            Set rngC = .Range(.Cells(2, iCol), .Cells(.Cells(.Rows.Count, "A").End(xlUp).Row, iCol))
            rngC.Formula = "=NOT(ISERROR(MATCH(A2,'Record Set 1'!A:A,False)))"
            rngC.Value = rngC.Value
            .Range("A:A").Resize(, iCol).AutoFilter Field:=iCol, Criteria1:="TRUE"
            rngC.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            .ShowAllData
            .Columns(iCol).Delete
        End With

    End Sub



    Tuesday, July 14, 2015 3:59 PM
  • Thanks for the help! That's what I was looking for.

    Nathon Dalton
    Sr. Lead Developer
    Blog: http://www.nathondalton.com

    Tuesday, July 14, 2015 10:04 PM