writing records to SQL Server very slow 1

[waubain]

I screen scrape records from out legacy system(Reflections). Typically I write to Excel or Access and this is lighting fast. Now I am trying to write them directly to our SQL Server. My code works but is very slow. Since this is my first time with this type of connection, I am wondering if my parameters are some how set up wrong and is slowing the process down. I typically scrape in excess of 100,000 records at a time. My sessions are timing out after 40 minutes.

This code is for a batch update. The actual reading in of the records takes about 30 seconds but the batch update is the slow process.

Sub FilemanToSS3()
  
    Dim conn As Object
    Dim rst As Object

    
    Set conn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")
    
    With conn
    'Create a connection to SQL Server Connection
        .ConnectionString = "Provider=SQLOLEDB.1; Integrated Security=SSPI;Persist Security Info=True;Data Source=R02LITDWH62;Initial catalog=MyDatabase"
    'Open connection
        .Open
    End With
    
    With Session ' Starts Session
    
    With rst        ' Opens destination table in Batch mode
        Set .ActiveConnection = conn
        .Source = dbo.tblTest
        .CursorType = adOpenStatic
        .LockType = adLockBatchOptimistic
        .CursorLocation = adUseClient
        .Open
    
        i = 1
        Do Until (Trim(strReadline) Like "" And i > 10) ' read one record at a time until end
            '...code to read in 1 record from screen and clean it
                arrData = Split(strReadline, "|")   'Creates array from record which are pipe delimited
                .AddNew
                For y = LBound(arrData) To UBound(arrData)
                    If Len(arrData(y)) <> 0 Then        'Check if array element is empty
                        rst.Fields(y).value = Trim(arrData(y))
                    End If
                Next y
            i = i + 1
        Loop
        rst.UpdateBatch
    End With

    'Close connection to SQL Server and set objects to Nothing
    conn.Close
    Set rst = Nothing
    Set conn = Nothing
    
End With ' End Session
Exit Sub ' Exit Sub

I have also tried to update after each record and the records scroll very slow as it updates after each record.

    With rst        ' Opens destination table in Batch mode
        Set .ActiveConnection = conn
        .Source = dbo.tblTest
        .CursorType = adOpenStatic
        .LockType = adLockOptimistic
        .CursorLocation = adUseClient
        .Open
    
        i = 1
        Do Until (Trim(strReadline) Like "" And i > 10) ' read one record at a time until end
            ...code to read in 1 record from screen and clean it
                arrData = Split(strReadline, "|")   'Creates array from record
                .AddNew
                For y = LBound(arrData) To UBound(arrData)
                    If Len(arrData(y)) <> 0 Then        'Check if array element is empty
                        rst.Fields(y).value = Trim(arrData(y))
                    End If
                Next y
                .Update
            i = i + 1
        Loop
    End With

Any suggestions would be much appreciated.

You don't know what you don't know...

 

[gk53]

just couple comments.
1. you open database connection and read all records into memory, from this point you have very small amount of memory left.... .Source = dbo.tblTest (all records from tblTest)loads into buffer. try somthing like
sql="select * from dbo.tblTest where 0=1" and
rst.open conn, sql, adOpenStatic, adLockOptimistic
2. you can try to set rst.ActiveConnection = nothing after read empty recordset and reconnect just before rst.UpdateBatch

 

[waubain]

GK, thank you for the suggestions, although I am not sure I fully understand. I tried to implement your suggestion #2, but it did not change how long it took to update the records.

Is this what you meant?

Loop
Set rst.ActiveConnection = Nothing
Set rst.ActiveConnection = conn
rst.UpdateBatch
End With

I am not sure what to write for the vba for your suggestion #1. It looks like you are querying an empty recordset. Should this go at the beginning when I am first opening the recordset or at the end when I am reestablishing the connection.

You don't know what you don't know...

 

[gmmastros]

I'm curious... how long does it take to run the ".BatchUpdate" line of code in comparison to the rest of the code?

Also, can you describe your environment? Looks like VB6 code, but could also be VBA code running through a script process or even on a web page.

There are a couple things you could try.

1. Change the cursor location to adUseServer.
2. Change the connection process the way gk53 mentions.
3. Write the data to a flat file and then bulk import the data.

Basically... if you can write to Excel and/or Access very quickly, you should be able to write the data to a flat file just as quickly and then bulk import (which should also be very fast).


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gk53]

I wrote about

Sub FilemanToSS3()
  
    Dim conn As Object
    Dim rst As Object

    
    Set conn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")
    
    With conn
    'Create a connection to SQL Server Connection
        .ConnectionString = "Provider=SQLOLEDB.1; Integrated Security=SSPI;Persist Security Info=True;Data Source=R02LITDWH62;Initial catalog=MyDatabase"
    'Open connection
        .Open
    End With
    
    With Session ' Starts Session
    sql="select * from dbo.tblTest where 0=1"    
    With rst        ' Opens destination table in Batch mode
        .CursorType = adOpenStatic
        .open conn, sql, adOpenStatic, adLockOptimistic
        Set .ActiveConnection = Nothing
        i = 1
        Do Until (Trim(strReadline) Like "" And i > 10) ' read one record at a time until end
            '...code to read in 1 record from screen and clean it
                arrData = Split(strReadline, "|")   'Creates array from record which are pipe delimited
                .AddNew
                For y = LBound(arrData) To UBound(arrData)
                    If Len(arrData(y)) <> 0 Then        'Check if array element is empty
                        rst.Fields(y).value = Trim(arrData(y))
                    End If
                Next y
            i = i + 1
        Loop
        Set rst.ActiveConnection = conn
        rst.UpdateBatch
    End With

    'Close connection to SQL Server and set objects to Nothing
    conn.Close
    Set rst = Nothing
    Set conn = Nothing
    
End With ' End Session
Exit Sub ' Exit Sub

 

[waubain]

Thank you both for your suggestions.

George:
There are 24308 lines of display which equates to 11743 records.
The scraping of records takes 20 seconds and the UpdateBatch takes 2:45 minutes

My enviroment is Attachmate Reflections for UNIX and OpenVMS. It is vba. The source network and the destination network are 2 different networks. I did try changing the CursorLocation to adUseServer but I killed the test at 5 minutes.
Since these are one time queries I had already considered writing to Excel and then use a BULK IMPORT or using the wizard, but then I would miss out on the opportunity to be completely frustrated at work for several days trying to learn something new.

GK: I had to make some minor changes to get you code to run, but it did improve the UpdateBatch to about 1:10 minutes on several tries. I got real excited the first time as UpdateBatch took 1 second, but it only wrote 1 record. It seems to need .LockType = adLockBatchOptimistic to get all the records

    sql = "SELECT * FROM dbo.tblTest WHERE 0=1"
    With rst        ' Opens destination table in Batch mode
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockBatchOptimistic
        .Open sql, conn
        Set .ActiveConnection = Nothing
            
	'....Loop to scrape record and addnew

        Set .ActiveConnection = conn
        .UpdateBatch
    End With

This might be acceptable. I have recordset of about 300,000 that I will try out tomorrow.
Thank you again.

You don't know what you don't know...

 

[fredericofonseca]

or you might try and load your lookup data into a temp table on sql server and then do the compares/cleaning through a set based sql - should be faster also

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[kjv1611]

This line in particular, SQL-wise, will generally slow down a query, and I'm certain it would slow down a looped query that is running through one record at a time (I'm assuming that's what it's doing - haven't looked at it and thought about it in depth):

Do Until (Trim(strReadline) Like "" And i > 10) ' read one record at a time until end

Is there not a way you can make a selection in SQL first with your requirements, and then loop through that via a temp table? Or it may be you can find where you can do a single transaction of an UPDATE query in SQL instead of looping through and changing records one at a time. Perhaps what you're doing isn't possible like that, but thought I'd mention it as an idea. If you could do it that way, it should run WAY faster.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57