CopyFromRecordset slowing down to a crawl with large-ish recorset

[Loomah]

hi

i'm trying to use the code below to copy data from a sql (2008 r2) table to multiple sheets in excel 2003 - there are currently c420000 records, expanding at around 1000 a week. this is the requirement, i have no option to use access or later versions of excel for the output. i have been searching for some time and can find many threads on different forums relating to the same or similar issues but nothing specific enough to meet my requirements or help me resolve the issue.

what happens is the code will work but slows noticeably after around 30000 rows. i think the issue is the fact there are over 100 columns - i tested the code by selecting 6 or 7 columns and it returns a full dataset as required within an acceptable time period.

the code slows/hangs at the copyfromrecordset stage. if i break out of the code an error (-2147467259; Method 'CopyFromRecordset' of object 'Range' failed) is given but the code hasn't actually failed (yet), ie it can be continued without major issues.

i have not been able to complete the code for the full recordset and the longest i have let it run (2 hours) only completed around 50% - 60%.

can anybody shed any light on how i might be able to negate the problem with the process as it stands grinding to a painfully slow pace or suggest another method i might use? any help/suggestions gratefully appreciated

 Sub DATA_Import(Frequency As String)
   
    Dim sCon As String                  ' building string for the connection property
    Dim sSQL As String                  ' building string for the SQL property
    Dim rsData As ADODB.Recordset       ' reference made to latest ADO library - 2.8
    Dim cnxEWMS As ADODB.Connection     ' reference made to latest ADO library - 2.8
    Dim lWScount As Long
    Dim lRow As Long, lCol As Long      ' holders for last row & col in data
    Dim c As Range                      ' identifies where flags data begins - should be constant but you never know!
    Dim Cx As Long                      ' for looping through the flags columns to change blanks to 0
    Dim wbNew As Workbook               ' the final destination file!
    Dim sFileDate As String             ' the date for naming the output file
    Dim wsNotes As Worksheet            ' notes sheets for product
    Dim wsCover As Worksheet            ' cover sheet for product
    
    Worksheets("Headings").Cells.Delete
    

    ' using windows authentication
    ' won't work where user is not listed on SQL server
    sCon = "Provider=SQLOLEDB;" & _
            "Data Source=SOMESERVER;" & _
            "Initial Catalog=SomeDatabase;" & _
            "Integrated Security=SSPI"
    
    ' identify frequecy for reporting and build SQL
    ' daily data is live records only
    If Frequency = "daily" Then
        sSQL = "SELECT * " & _
                "FROM tblMainTabWithFlagsDaily " & _
                "WHERE status='LIVE';"
    Else
        'weekly - all records split over multiple sheets
        sSQL = "SELECT *" & _
                "FROM tblMainTabWithFlagsDaily;"
    End If
    
          
    ' create and open the connection to the database
    Set cnxEWMS = New ADODB.Connection
    With cnxEWMS
        .Provider = "SQLOLEDB;"
        .ConnectionString = sCon
        .Open
    End With
    
    ' create and open the recordset
    Set rsData = New ADODB.Recordset
    rsData.Open sSQL, cnxEWMS, adOpenForwardOnly, adLockReadOnly
    
    With Application
        ' if construct used for debugging/testing when called from module1
        If Not TestCaller Then
            .ScreenUpdating = False
        End If
        .Calculation = xlCalculationManual
    End With
    
        If Not rsData.EOF Then
            ' create header row 'dummy' sheet
            For lCol = 0 To rsData.Fields.Count - 1
                With Worksheets("Headings").Range("A1")
                    .Offset(0, lCol).Value = rsData.Fields(lCol).Name
                End With
            Next
            
            Set c = Worksheets("Headings").Rows("1:1").Cells.Find("warrflag_recno")
            
            ' copy data into workbook and format accordingly
            Do While Not rsData.EOF
            
                If wbNew Is Nothing Then
                    ' create the new "product" workbook
                    Worksheets("Headings").Copy
                    Set wbNew = ActiveWorkbook
                Else
                    lWScount = wbNew.Worksheets.Count
                    ThisWorkbook.Worksheets("Headings").Copy after:=wbNew.Worksheets(lWScount)
                End If
                
                With wbNew.Worksheets(lWScount + 1)
                    .UsedRange.Font.Bold = True
                    If Frequency = "daily" Then
                        .Name = "Live" & Format(lWScount + 1, "0#")    ' shouldn't need numerous sheets for live data - ave 15k - 16k records
                    Else
                        .Name = "Split" & Format(lWScount + 1, "0#")
                    End If
                
                ' THE REASON WE'RE ALL HERE!!!
                ' copy from recordset in batches of 55000 records
                ' this keeps hanging, presumably because of number of columns
                ' reducing columns to 6 or 7 runs fine and quickly
                .Range("A2").CopyFromRecordset rsData, 55000
                    
            ' the remainder of the code is removed 
            ' as it is just formatting and creating notes 
            ' and cover sheets and then saving
    
    ' tidy up!
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
    
    rsData.Close
    Set rsData = Nothing
    cnxEWMS.Close
    Set cnxEWMS = Nothing
    Set c = Nothing
    Set wsNotes = Nothing
    Set wsCover = Nothing
    
    End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[combo]

If the purpose is to store data locally and possibly link to it for reports, I would still use access (JET) database. With reference to ADOX and ADO it's pure VBA:

Sub CreateMdbDatabase()
Dim adxCat As ADOX.Catalog
Dim adxTable As ADOX.Table
Dim Conn

' ADOX: create database
Set adxCat = New ADOX.Catalog
adxCat.Create ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & "DataBasePathAndName.mdb" & ";" & _
        "Jet OLEDB:Engine Type=5")
Conn = adxCat.ActiveConnection

' ADOX: sreate table
Set adxTable = New ADOX.Table
With adxTable
    .Name = "Table1"
    With .Columns
        .Append "Field1", adDate ' date
        .Append "Field2", adSmallInt ' integer
        .Append "Field3", adUnsignedTinyInt ' byte
        .Append "Field4", adVarWChar, 1 ' string, field size=1
        .Append "Field5", adInteger ' long integer
        .Append "Field6", adDouble ' double
        .Append "Field7Optional", adVarWChar, 1 ' string, field size=1
        .Item("Field7Optional").Attributes = adColNullable ' allow null
    End With
End With
adxCat.Tables.Append adxTable
Set adxTable = Nothing

' ADO: access to table 1
Dim adoRs As ADODB.Recordset
Set adoRs = New ADODB.Recordset
adoRs.Open _
    Source:="Table1", _
    ActiveConnection:=Conn, _
    CursorType:=adOpenKeyset, _
    LockType:=adLockPessimistic, _
    Options:=adCmdTable

' fill "Table1"
   
adoRs.Close
Set adoRs = Nothing
Set adoRs = Nothing
End Sub

combo

 

[SkipVought]

this keeps hanging, presumably because of number of columns

So how many columns?

Also why so many rows? It ssem you getting ALL the rows from this table weekly. Can you process the data in your SQL, like summarize appropriately?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Loomah]

combo
access isn't an option as the end user doesn't have it on their systems and there are issues surrounding accessing anything held by "me" across different domains. the requirement is for a full data dump from a system with a second dataset merged with it. the background is messy and predates me by a number of years. all i know is that is what the end user wants and if they don't get it on time all hell breaks loose!!

there is an existing functional process that works (just) by having a single workbook pulling data through multiple queries but various changes to the organisation mean that direct access to the data in the current way may not be available. also this is a pain to maintain, or it will be...

skip
currently 103 columns and see above for explanation (such as it is) as to why the full dataset is required. a second daily sub set of the data is also provided for a different purpose

the ultimate objective here is to run the processing early in the morning so that data is ready for the team that use it when they get in at silly o'clock each morning so simply recreating the current process isn't ideal but could be the fallback position - last resort.

thanks to you both for your responses so far!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[combo]

I meant you don't need ms access. The code creates JET engine database (access uses it too) without access. The advantage is that you can create local repository, for which you can define and update multiple tables inside, including their field types, indexes, users etc.
So as long as queries and pivot tab ()les from external file (via ODBC)are all you need, with this architecture user has to update tables (excel man agement file) and update queries/reports next.
In case of missing ADOX (Microsoft ADO Ext. 2.x for DLL and Security) you can download MS MDAC (v. 2.8).

combo

 

[fredericofonseca]

you may try, as an alternative, to export into Excel using SSIS - might be faster and could be done on the server side.

And any formatting could be done afterwards, or indeed within SSIS with some c# code

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Loomah]

combo
thanks for that and sorry i didn't pick up on what you were telling me the first time! however (and i may just be having a 'senior' moment here) i'm not entirely sure how your suggested solution/alternative helps. i would still, surely, be in the situation where i need to dump 420k records (and rising) records into multiples of 65k rows. and there's the issue that the width of the data set (currently 103 columns) has the potential to grow....

frederico
thanks for your input. however if i had been able to get ssis to do what i wanted it to do using export to excel i wouldn't be here asking this question now!!

as i can't find a reason for the exponential slowing of copyfromrecordset i'm starting to think i'm going to have to revisit ssis for the output but i seem to recall that the dynamic nature of the dataset was an issue with the transformations (and with getting the headings out)!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[combo]

What I suggested is that, if excel it is not a "must have" feature, I would use a JET database file instead. It can be created, updated, queried (in excel via ODBC, external data queries / pivot tables) and managed totally without ms access.
I don't know the details, but I assumed that the whole process is splitted into:
1) sql server database,
2) local database from (1),
3) local reports from (2).
So the replacement in (2): "xls" => "mdb".

combo

 

[fredericofonseca]

Lomah - if you could not do it using SSIS then its possible you got into particular problems and were unable to solve them - post those issue on the correct forum and I and others will help if we can - I do a lot with SSIS and Excel on the current project I am with and yes there are a few problems but all sortable.

Regarding "dynamic nature of the dataset" there are ways around it. If nothing else the package can be created and executed on the fly by a C# running in the server.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Loomah]

combo
as it stands excel is the must have feature but i'm at the stage of wondering if that's because "we've always done it that way" - the supply of this data dump predates my arrival in this organisation, some 4 years ago, by quite a time. i'm planning to meet with the customer to re-assess their requirement to see what they use and how. if they have the understanding then perhaps what you are suggesting may be one possible solution - but that would be some time off...

Frederico
thanks for your input. i very well may come back to the ssis route and will come looking for you in that forum but would be looking for a vb.net solution rather than c#!!!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​