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

nector

Member
Local time
Today, 05:23
Joined
Jan 21, 2020
Messages
408
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.

Code:
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

..forever waiting... waiting for jellybean!
Local time
Today, 10:23
Joined
May 7, 2009
Messages
19,252
maybe MSA is not the right tool?
 

Minty

AWF VIP
Local time
Today, 03:23
Joined
Jul 26, 2013
Messages
10,397
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

George Hepworth
Local time
Yesterday, 19:23
Joined
Nov 25, 2004
Messages
2,115
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

AWF VIP
Local time
Today, 03:23
Joined
Jul 26, 2013
Messages
10,397
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

Member
Local time
Today, 05:23
Joined
Jan 21, 2020
Messages
408
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

George Hepworth
Local time
Yesterday, 19:23
Joined
Nov 25, 2004
Messages
2,115
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

AWF VIP
Local time
Today, 03:23
Joined
Jul 26, 2013
Messages
10,397
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

Super Moderator
Staff member
Local time
Yesterday, 22:23
Joined
Feb 19, 2002
Messages
43,854
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

AWF VIP
Local time
Today, 03:23
Joined
Jul 26, 2013
Messages
10,397
@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

Super Moderator
Staff member
Local time
Yesterday, 22:23
Joined
Feb 19, 2002
Messages
43,854
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
 

Users who are viewing this thread

Top Bottom