Efficiently Reading a recordset returned from a SQL Server SPROC in Access 2013

I am fairly new to SQL Server, using Developer 14 as the back end of my Access 2013 application.

I have created a SQL SPROC that returns a recordset to my Access front end.  The recordset is read only and will only be read from start to finish. This is a one time conversion and the SPROC will be returning several million records so I want to make sure I'm reading it the most efficient way.

This is the example code I found.  It works but I'm wondering if it can be more efficient, given the forward only, read only usage.  Every little bit of improvement will help due to the large number of records.  I'm thinking specifically about the .locktype and .Cursortype but please note any improvements that can be made.

'///////////////////////////// Stored Procedure ///////////////////////////////////////
Dim rsIn2 As ADODB.Recordset
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

With cmd
    .CommandText = "aConvertspMuni_TQHD"
    .CommandType = adCmdStoredProc
    setSQLConnection
    .ActiveConnection = gConnection
     
    Set rsIn2 = New ADODB.Recordset
        With rsIn2
            .CursorLocation = adUseServer
            .CursorType = adOpenKeyset
            .LockType = adLockOptimistic
            .Open cmd
        End With
        '
'///////////////////////////// Stored Procedure ///////////////////////////////////////
LVL 1
mlcktmguyAsked:
Who is Participating?
 
PatHartmanCommented:
If the data is in SQL Server and going to SQL Server and you want to run it in Access,  You can use linked tables and Access syntax but you will need to be careful that you are only using VBA functions that have direct equivalents in SQL Server.  If any of the functions do not have a direct counterpart, Access will help you out by downloading all the data from the server so it can run the query locally and then uploading it.  This could easily be slower than the cursor loop.  If you create a passthrough append query, you avoid this potential problem but you will have to convert the Access functions to T-SQL yourself or create UDF's on the server to replace the VBA/Access functions.
0
 
John TsioumprisSoftware & Systems EngineerCommented:
Why do you pass arguments to the SPROC to make it return the least needed records..
0
 
mlcktmguyAuthor Commented:
John: It's a one time data conversion from a legacy system to the new SQL backend.  All records are needed and there are several million.

When I am using DAO to open an input only, forward only table or query I use this structure to make it more efficient
'
Set db = getCurrentDbC
Dim rsIn2 As DAO.Recordset
Set rsIn2 = db.OpenRecordset(inputTable, dbOpenSnapshot, dbReadOnly)   ' opened Readonly  //////////////// Read Only  //////////////////

Open in new window


I was wondering if there are option similar to  'dbOpenSnapshot' and 'dbReadOnly' that could be used instead of 'adOpenKeyset' and 'adLockOptimistic'.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
PatHartmanCommented:
Why are you even bringing the recordset into Access?  Why not do what you need to do with it in the sproc?
0
 
mlcktmguyAuthor Commented:
Pat: There is too much data manipulation and reformatting.  I am quite the beginner in SQL.  Here's an example of one of the processing loops.
If rsIn2.EOF Then
    '
Else
  '  If rsIn2.RecordCount > 0 Then
        '
        wkrecCount = 0
        '
        While Not rsIn2.EOF
            '
            wkrecCount = wkrecCount + 1
            '
            rsOut.AddNew
                '
                rsOut![ID] = Nz(rsIn2![ID], 0)
                rsOut![MuniCode] = passedMuniCode
                rsOut![ControlNumber] = Nz(rsIn2![ControlNumber], 0)
                rsOut![TaxYear] = Nz(rsIn2![TaxYear], 0)
                rsOut![SequenceNumber] = Nz(rsIn2![DequenceNumber], 0)
                rsOut![RecordType] = Nz(rsIn2![RecordType], 0)
               '
               '/ / / / / / / / / / / / / / / / / / / /
               '
               ' Relaced with function below to convert JTS Tax Type to MJO Tax TYpes
               '
               ' If passedMuniCode < 99 Then
               '     rsOut![TaxType] = eTaxType.eLibrary
               ' Else
               '     rsOut![TaxType] = Nz(rsIn2![TaxType], 0)
               ' End If
               ' '
               rsOut![TaxType] = convertJTSTaxTypesToMJOTaxTypes(passedMuniCode, Nz(rsIn2![TaxType], 0))
               '
               '/ / / / / / / / / / / / / / / / / / / /
               '
                rsOut![MultiUse] = Nz(rsIn2![MultiUse], "")
                
                wkMultiUse = Nz(rsIn2![MultiUse], "")
                '
                ' the code below looks for values all the way to position 82 of wkMultiUse
                ' somw weren't coming in that long and it was blowngup the Mid( refernces
                ' So, right pad wkmultiuse with spaces to a lenght of at least 82
                '
                wkPrevLenghtOfMultiUse = Len(wkMultiUse)
                rightPadAString wkMultiUse, 82
                wkCurrLenghtOfMultiUse = Len(wkMultiUse)
                '
                ' Handle 7 or 9 record
                '
                If Nz(rsIn2![RecordType], 0) = 9 Then
                    rsOut![Rec9_Docket] = Mid(wkMultiUse, 10, 30)
                Else
               '     Debug.Print Len(wkMultiUse); " ."; wkMultiUse
    '
                    wkRec7_Series = Val(Nz(Mid(wkMultiUse, 1, 2), 0))
                    wkRec7_Volume_01 = Val(Nz(Mid(wkMultiUse, 3, 3), 0))
                    wkRec7_Volume_02 = Nz(Mid(wkMultiUse, 6, 1), "")
                    wkRec7_Page = Val(Nz(Mid(wkMultiUse, 7, 3), 0))
                    wkRec7_Docket = Nz(Mid(wkMultiUse, 10, 30), "")
                    '
                    wkRec7_EntryMM = Nz(Mid(wkMultiUse, 40, 2), 0)
                    wkRec7_EntryDD = Nz(Mid(wkMultiUse, 42, 2), 0)
                    wkRec7_EntryYY = Nz(Mid(wkMultiUse, 44, 4), 0)
                    
                    wkRec7_ReviveMM = Nz(Mid(wkMultiUse, 48, 2), 0)
                    wkRec7_ReviveDD = Nz(Mid(wkMultiUse, 50, 2), 0)
                    wkRec7_ReviveYY = Nz(Mid(wkMultiUse, 52, 4), 0)
                    '
                    wkRec7_LienAmt = convertImportDbl2DecStrToNumber(Nz(Mid(wkMultiUse, 56, 9), 0))
                    
                    wkRec7_DTD_Year = Val(Nz(Mid(wkMultiUse, 65, 2), 0))
                    wkRec7_DTD_Number = Val(Nz(Mid(wkMultiUse, 67, 6), 0))
                    wkRec7_DTD_Alpha = Nz(Mid(wkMultiUse, 73, 1), 0)
                    wkRec7_Asgn_DTD_Year = Val(Nz(Mid(wkMultiUse, 74, 2), 0))
                    wkRec7_Asgn_DTD_Number = Val(Nz(Mid(wkMultiUse, 76, 6), 0))
                    wkRec7_Asgn_DTD_Alpha = Nz(Mid(wkMultiUse, 82, 1), "")
                    '
                    rsOut![Rec7_Series] = wkRec7_Series
                    rsOut![Rec7_Volume_01] = wkRec7_Volume_01
                    rsOut![Rec7_Volume_02] = wkRec7_Volume_02
                    rsOut![Rec7_Page] = wkRec7_Page
                    rsOut![Rec7_Docket] = wkRec7_Docket
                    '
                    If Nz(wkRec7_EntryYY, 0) = 0 Or Len(Trim(wkRec7_EntryYY)) = 0 Then
                    Else
                        rsOut![Rec7_EntryDate] = convertDateCCCCMMYY(Nz(wkRec7_EntryYY, 0), Nz(wkRec7_EntryMM, 0), Nz(wkRec7_EntryDD, 0))
                    End If
                    '
                    If Nz(wkRec7_ReviveYY, 0) = 0 Or Len(Trim(wkRec7_ReviveYY)) = 0 Then
                    Else
                        rsOut![Rec7_Reviv_Date] = convertDateCCCCMMYY(Nz(wkRec7_ReviveYY, 0), Nz(wkRec7_ReviveMM, 0), Nz(wkRec7_ReviveDD, 0))
                    End If
                    '
                    rsOut![Rec7_LienAmt] = wkRec7_LienAmt
                    
                    rsOut![Rec7_DTD_Year] = wkRec7_DTD_Year
                    rsOut![Rec7_DTD_Number] = wkRec7_DTD_Number
                    rsOut![Rec7_DTD_Alpha] = wkRec7_DTD_Alpha
                    rsOut![Rec7_Asgn_DTD_Year] = wkRec7_Asgn_DTD_Year
                    rsOut![Rec7_Asgn_DTD_Number] = wkRec7_Asgn_DTD_Number
                    rsOut![Rec7_Asgn_DTD_Alpha] = wkRec7_Asgn_DTD_Alpha
                    '
                End If
                
                rsOut![PaidFlag] = Nz(rsIn2![PaidFlag], "")
                rsOut![ReceiptNumber] = Nz(rsIn2![ReceiptNumber], "")
                '
                If Nz(rsIn2![PaidYYYY], 0) = 0 Then
                Else
                    rsOut![PaidDate] = convertDateCCCCMMYY(Nz(rsIn2![PaidYYYY], 0), Nz(rsIn2![PaidMM], 0), Nz(rsIn2![PaidDD], 0))
                End If
                '
                rsOut![FaceAmtPaid] = convertImportDbl2DecStrToNumber(Nz(rsIn2![FaceAmtPaid], 0))
                rsOut![PenaltyAmtPaid] = convertImportDbl2DecStrToNumber(Nz(rsIn2![PenaltyAmtPaid], 0))
                rsOut![InterestAmtPaid] = convertImportDbl2DecStrToNumber(Nz(rsIn2![InterestAmtPaid], 0))
                rsOut![CostAmtPaid] = convertImportDbl2DecStrToNumber(Nz(rsIn2![CostAmtPaid], 0))
                rsOut![CommissAmtPaid] = convertImportDbl2DecStrToNumber(Nz(rsIn2![CommissAmtPaid], 0))
                '
                rsOut![PaidToCounty] = Nz(rsIn2![PaidToCounty], 0)
                rsOut![SatByCounty] = Nz(rsIn2![SatByCounty], 0)
                rsOut![SoldFlag] = Nz(rsIn2![SoldFlag], "")
                rsOut![PenaltyOverideAmtDue] = convertImportDbl2DecStrToNumber(Nz(rsIn2![PenaltyOverideAmtDue], 0))
                rsOut![InterestOverideAmtDue] = convertImportDbl2DecStrToNumber(Nz(rsIn2![InterestOverideAmtDue], 0))
                rsOut![CostOverideAmtDue] = convertImportDbl2DecStrToNumber(Nz(rsIn2![CostOverideAmtDue], 0))
                rsOut![OrigFaceAmtDue] = convertImportDbl2DecStrToNumber(Nz(rsIn2![OrigFaceAmtDue], 0))
                '
                ' new field added by Vince
                '
                If Nz(rsIn2![InterstStart_IntAdd_YYYY], 0) = 0 Then
                Else
                    rsOut![InterstStart_IntAdd_Date] = convertDateCCCCMMYY(Nz(rsIn2![InterstStart_IntAdd_YYYY], 0), Nz(rsIn2![InterstStart_IntAdd_MM], 0), Nz(rsIn2![InterstStart_IntAdd_DD], 0))
                End If
                '
            '    rsOut![Filler] = Nz(rsIn2![Filler], "")
                '
                rsOut![Full_Int_Add_TypeID] = eRegAddIntTax.eRegular
                rsOut![SequenceNo] = 0
            '
            rsOut.Update
            '
            rsIn2.MoveNext
        Wend
            
 '   End If    'recordcount >0
End If

Open in new window

0
 
PatHartmanCommented:
Most of the code would work in the sproc although I'm not sure they support the NZ() function so those might need to be IIf()'s.  In fact, you can probably do a lot of it with append queries.

Cursor's are the slowest method of processing data so I use queries whenever possible and only use cursors when there is too much coding required to do the conversions.
0
 
mlcktmguyAuthor Commented:
Pat: I have other routines that do pretty much move one field to another and would be better candidates

When you say 'append queries' are you talking about Access append queries, appending records from one linked SQL table or query to another SQL table.  These would be faster than a processing loop?

Or are you referring to an append query in SQL Server?

This is one of the for sure candidates?
                    rsOut.AddNew
                    '
                    rsOut![PropertyRecID] = Nz(rsIn2![PropertyRecID])
                    rsOut![Muni] = Nz(rsIn2![MuniCode])
                    rsOut![LotBlock] = Nz(rsIn2![LotBlock])
                    rsOut![TieBreaker] = Nz(rsIn2![TieBreaker])
                    rsOut![JTSAddr1] = Trim(Nz(rsIn2![AddrLine1]))
                    rsOut![JTSAddr2] = Trim(Nz(rsIn2![AddrLine2]))
                    rsOut![JTSAddr3] = Trim(Nz(rsIn2![AddrLine3]))
                    rsOut![JTSAddr4] = Trim(Nz(rsIn2![AddrLine4]))
                    
                    
                    rsOut![JTSSeqNumber] = Nz(rsIn2![SequenceNumber])
                    rsOut![Name1] = Nz(rsIn2![AddrLine1])
                    rsOut![Name2] = Nz(rsIn2![AddrLine2])
                 '   rsOut![Name3] = Nz(rsIn2![T])
                    rsOut![Address1] = Nz(rsIn2![AddrLine3])
                 '   rsOut![Address2] = Nz(rsIn2![T])
                    rsOut![City] = Nz(rsIn2![City])
                    rsOut![State] = Nz(rsIn2![State])
                    rsOut![ZipCode] = Nz(rsIn2![Zip])
                  '  rsOut![ZipPlusFour] = Nz(rsIn2![T])
                    rsOut![JTSOldControlNum] = Nz(rsIn2![ControlNumber])
                    rsOut![BillingAddressYN] = True
                    rsOut![IncludeOnLegal] = False
                    rsOut![SequenceOnLegal] = 0
                    rsOut![UnMailable] = Nz(rsIn2![UnMailable])
                    rsOut![AddressCheckNeeded] = Nz(rsIn2![AddressCheckNeeded])
                    rsOut![MailingAddress_YN] = True
                    rsOut![InterestTypeID] = eInterestedPartyType.eOther
                    rsOut![DefaultAddress_YN] = True
                    rsOut![Active_YN] = True
                    'rsOut![DayPhone] =
                    'rsOut![NightPhone] = Nz(rsIn2![T])
                    'rsOut![MobilePhone] = Nz(rsIn2![T])
                    'rsOut![Email] = Nz(rsIn2![T])
                    'rsOut![Comments] = Nz(rsIn2![T])
                    rsOut![DateAdded] = wkDate
                    rsOut![UserAdded] = wkUser
                    'rsOut![DateRevised] = Nz(rsIn2![T])
                    'rsOut![UserRevised] = Nz(rsIn2![T])
                '    rsOut![THATimeStamp] = wkStartTime
                    '
                    rsOut.Update

Open in new window

0
 
mlcktmguyAuthor Commented:
Thanks Pat
0
 
PatHartmanCommented:
You're welcome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.