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.
(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