Solved Big variation when sending data through Cloud SQL Server Vs MS Access (1 Viewer)

[nector]

We have two backends (BEs, that is Cloud SQL Server and MS Access) using the same MS Access interface as front end (FE) to send data via the internet to the server. If we send the data using the code below and save the response to the backends below, see results:

(1) Cloud SQL Server will take 24 seconds to save the response
(2) MS Access Backend on the network with linked tables will take 2 seconds

Why is the SQL Server take such a long time? we are sending one record at a time, I tried to convert the Query (QryJson) as a passthrough query still it does not change anything.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rst As DAO.Recordset
Dim Company As New Dictionary
Dim strData As String
Dim Json As Object
Dim data As New Dictionary
Dim transactions As Collection
Dim itemCount As Long
Dim i As Long
Dim n As Integer
Dim Z As Integer
Dim item As New Dictionary
Dim items As New Collection
Dim prm As DAO.Parameter
Dim qdf As DAO.QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("QryJson")

For Each prm In qdf.Parameters
     prm = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset(dbOpenSnapshot, dbSeeChanges)
    Set qdf = Nothing
    rs.MoveFirst
    Do While Not rs.EOF
Set data = New Dictionary
Set transactions = New Collection
Set Company = New Dictionary
        Company.Add "tpin", "1002623668"
        Company.Add "bhfId", "000"
        Company.Add "invcNo", rs!InvoiceID.value
        Company.Add "orgInvcNo", Nz(rs!OrignalInvoiceNumber.value, 0)
        Company.Add "custTpin", rs!TPIN.value
        Company.Add "custNm", rs!Company.value
        Company.Add "salesTyCd", "N"
        Company.Add "rcptTyCd", "S"
        Company.Add "pmtTyCd", "01"
        Company.Add "salesSttsCd", "02"
        Company.Add "cfmDt", Now()
        Company.Add "salesDt", Format((Date), "YYYYMMDD")
        Company.Add "stockRlsDt", Now()
        Company.Add "cnclReqDt", Null
        Company.Add "cnclDt", Null
        Company.Add "rfdDt", Null
        Company.Add "rfdRsnCd", Null
        Company.Add "totItemCnt", Me.txtinternalaudit
        Company.Add "taxblAmtA", Round(Nz(Me.txtclassA, 0), 2)
        Company.Add "taxblAmtB", Round(Nz(Me.txtclassB, 0), 2)
        Company.Add "taxblAmtC1", Round(Nz(Me.txtclassC1, 0), 2)
        Company.Add "taxblAmtC2", Round(Nz(Me.txtclassC2, 0), 2)
        Company.Add "taxblAmtC3", Round(Nz(Me.txtclassC3, 0), 2)
        Company.Add "taxblAmtD", Round(Nz(Me.txtclassD, 0), 2)
        Company.Add "taxblAmtRvat", 0
        Company.Add "taxblAmtE", 0
        Company.Add "taxblAmtF", 0
        Company.Add "taxblAmtIpl1", 0
        Company.Add "taxblAmtIpl2", 0
        Company.Add "taxblAmtTl", 0
        Company.Add "taxblAmtEcm", 0
        Company.Add "taxblAmtExeeg", 0
        Company.Add "taxblAmtTot", 0
        Company.Add "taxRtA", Nz((rs!Tax.value) * 100, 0)
        Company.Add "taxRtB", Nz((rs!Tax.value) * 100, 0)
        Company.Add "taxRtC1", 0
        Company.Add "taxRtC2", 0
        Company.Add "taxRtC3", 0
        Company.Add "taxRtD", 0
        Company.Add "taxRtE", 0
        Company.Add "taxRtF", 0
        Company.Add "taxRtIpl1", 0
        Company.Add "taxRtIpl2", 0
        Company.Add "taxRtTl", 0
        Company.Add "taxRtEcm", 0
        Company.Add "taxRtExeeg", 0
        Company.Add "taxRtTot", 0
        Company.Add "taxRtRvat", 0
        Company.Add "taxAmtA", Round(Nz(Me.txtttaxclassA, 0), 2)
        Company.Add "taxAmtB", Round(Nz(Me.txtttaxclassb, 0), 2)
        Company.Add "taxAmtC1", 0
        Company.Add "taxAmtC2", 0
        Company.Add "taxAmtC3", 0
        Company.Add "taxAmtD", 0
        Company.Add "taxAmtE", 0
        Company.Add "taxAmtF", 0
        Company.Add "taxAmtIpl1", 0
        Company.Add "taxAmtIpl2", 0
        Company.Add "taxAmtTl", 0
        Company.Add "taxAmtEcm", 0
        Company.Add "taxAmtExeeg", 0
        Company.Add "taxAmtTot", 0
        Company.Add "taxAmtRvat", 0
        Company.Add "totTaxblAmt", Round(Nz(Me.txttotaxableAB, 0), 2)
        Company.Add "totTaxAmt", Round(Nz(Me.txttotaltaxAll, 0), 2)
        Company.Add "totAmt", Round(Nz(Me.txtGrandtotal, 0), 2)
        Company.Add "prchrAcptcYn", "N"
        Company.Add "remark", rs!TheNotes.value
        Company.Add "regrId", "11999"
        Company.Add "regrNm", rs!Cashier.value
        Company.Add "modrId", "45678"
        Company.Add "modrNm", rs!Cashier.value
        Company.Add "receipt", data
        data.Add "custMblNo", Null
        data.Add "trdeNm", rs!Company.value
        data.Add "adrs", rs!Address.value
        data.Add "topMsg", ""
        data.Add "btmMsg", "Thank you for choosing us"
        data.Add "prchrAcptcYn", "N"
        Company.Add "itemList", transactions
             
    '--- loop over all the items
        itemCount = Me.txtinternalaudit
       
        For i = 1 To itemCount
            Set item = New Dictionary
            transactions.Add item
            item.Add "itemSeq", i
            item.Add "itemCd", rs!itemCd.value
            item.Add "itemClsCd", rs!itemClsCd.value
            item.Add "itemNm", rs!ProductName.value
            item.Add "bcd", Null
            item.Add "pkgUnitCd", "NT"
            item.Add "pkg", 1
            item.Add "qtyUnitCd", "U"
            item.Add "qty", rs!Quantity.value
            item.Add "prc", rs!UnitPrice.value
            item.Add "splyAmt", rs!UnitPrice.value
            item.Add "dcRt", 0
            item.Add "dcAmt", 0
            item.Add "isrccCd", Null
            item.Add "isrccNm", Null
            item.Add "isrcRt", Null
            item.Add "isrcAmt", Null
            item.Add "vatCatCd", rs!TaxClassA.value
            item.Add "iplCatCd", "IPL1"
            item.Add "tlCatCd", "TL"
            item.Add "exciseCatCd", "EXEEG"
            item.Add "taxblAmt", Round(rs!SupplierAmount.value, 2)
            item.Add "vatAmt", Round(rs!FinalTax.value, 2)
            item.Add "iplAmt", Null
            item.Add "tlAmt", Null
            item.Add "exciseAmt", Null
            item.Add "totAmt", Round(rs!TotalAmount.value, 2)
strData = JsonConverter.ConvertToJson(Company, Whitespace:=3)
rs.MoveNext
Next
Loop

Dim Request As Object
Dim stUrl As String
Dim Response As String
Dim requestBody As String
Dim Details As Variant
stUrl = "http://localhost:8080/nectorgeneral/Sales/processSales"
Set Request = CreateObject("MSXML2.XMLHTTP")
requestBody = strData
    With Request
        .Open "POST", stUrl, False
        .setRequestHeader "Content-type", "application/json"
        .send requestBody
        Response = .responsetext
    End With

 

[arnelgp]

maybe MSA is not the right tool?

 

[Minty]

Because you are sending the data one record at a time, and that is very inefficient.

If you stored the results locally and updated them in a single batch it would be quicker.

 

[GPGeorge]

That difference in performance is a large part of the reason most seasoned Access developers warn against blindly thinking that upsizing to "SQL Server" is going to be a path to greater performance. It is very often the opposite, and when you throw in "Cloud SQL Server" it's usually an even greater disappointment. More than 20 years ago, Albert Kallal wrote an article (pre-blog days, that's how long ago it was) laying out the problem.

The answer as @Minty suggests is to re-think your design and overall approach. Lots of references can be found on the internet on optimizing Access to work with remote SQL Server or SQL Azure, or other "cloud" databases.

Sending one record at a time over the internet is about as inefficient an approach as I can think of. You can make that better, but you will never fully achieve local network speeds.

 

[Minty]

We host a majority of our clients databases in Cloud based Azure SQL.
You have to allow for manoeuvring things around in a very specific fashion to make it work, and accept that a local SQL server, or Access BE can and will be faster in certain circumstances.

You have to learn to do things in the "right" way to maximise performance, and it's not a five minute lesson.

 

[nector]

Anyway, we will leave that way rather start storing data locally is again waste of time and in addition to that users can easily forget to update the local tables

 

[GPGeorge]

Anyway, we will leave that way rather start storing data locally is again waste of time and in addition to that users can easily forget to update the local tables

I do not suggest reverting to a local Access accdb Back End.

I advocate a redesign of the current interface to support the Cloud Back End better.

 

[Minty]

Anyway, we will leave that way rather start storing data locally is again waste of time and in addition to that users can easily forget to update the local tables

I didn't suggest that at all.

The conversion process would temporarily store the data locally while it was gathering each item, then at the end upload that data in one hit as the final part of the process.

The user shouldn't need to see anything different and certainly shouldn't need to "remember to do" anything, it should be part of the overall process.

 

[Pat Hartman]

Wow, bound forms would probably be better. Just make sure that your forms are bound to queries with where clauses that select only the record you want to update.

 

[Minty]

@Pat Hartman, it's an import routine for JSon data.
That is why I was suggesting storing the results locally until the were all processed, then doing a single upload to the remote backend.

 

[Pat Hartman]

I agree. Rather than putting the data into collections, put it in to tables (preferably in a side-end but that's a different discussion). Then use ONE append query instead of a bunch