Queries Directed to Cloud SQL Server from Access Extreamely Slow (1 Viewer)

[nector]

I Have checked all the forms even if binded on actual tables as long as the indexes are there the performance is realy good. The nightmare is on the queries they are extremely slow, can you Imagin one line take 3 minutes to process and just 10 lines takes 40 minutes.

Here is the actual query below , but when you run it against access database it can process 30 lines in 50 seconds , then why it is too slow in Cloud Ms Sql Server:

SELECT tblCustomerInvoice.InvoiceID, tblLineDetails.ItemesID, tblProducts.ProductName, tblProducts.ProductID, tblLineDetails.Quantity, tblLineDetails.UnitPrice, tblLineDetails.Discount, tblLineDetails.IsTaxInclusive, tblLineDetails.RRP, tblLineDetails.VAT, ((([Quantity]*[UnitPrice]))) AS TotalAmount, tblLineDetails.TaxClassA, tblLineDetails.TourismClass, tblLineDetails.ExciseClass, tblLineDetails.InsuranceClass, IIf([IsTaxInclusive]<0,"True","False") AS CGControl, tblProducts.BarCode, tblLineDetails.ESDPrice, tblCustomerInvoice.FCRate, tblLineDetails.Duty
FROM tblCustomerInvoice INNER JOIN (tblProducts INNER JOIN tblLineDetails ON tblProducts.ProductID = tblLineDetails.ProductID) ON tblCustomerInvoice.InvoiceID = tblLineDetails.InvoiceID
WHERE (((tblCustomerInvoice.InvoiceID)=[Forms]![frmCustomerInvoice]![CboEsdInvoices]));

 

[CJ_London]

Not surprised it’s slow - fact of life with a web based sql server. but are all fields used in the joins and criteria indexed? 30 lines in 50 seconds in access is still very slow which implies they are not.

And what happens if you temporarily substitute the forms criteria for a hard coded value

 

[Pat Hartman]

Another method is to build a view that performs the join and link to the view from Access.

Access is optimized to work over a LAN, It will not provide good performance over the internet. Your local LAN is more than 10 times faster than the fastest internet connection so that is just the starting point. If you really need to use Access via the internet, the best option is Citrix, especially if you can host Citrix yourself and the RDBMS is on the same LAN as the Citrix server.

 

[CJ_London]

Seems to me this is the same question you asked here

Forms and Subforms too slow in data capturing to Cloud SQL Server

I have migrated my Ms. Access database to cloud sql database using the ODBC connection tables strings all the users are able to connect their front end to cloud server using any form of internet and its fast just like as if you are connected to the local database: (1) Reports are quite fast to...

www.access-programmers.co.uk

I see little point in going over the same ground since you appear to have not tried any of the suggestions made

 

[nector]

Well after investigating the entire VBA code I discovered that the part which is slowing the processing is only the lookup which taking too much time to read the above code

Here is the DLookup

Private Sub CmdCwrite_Click()
On Error GoTo Err_Handler
Dim Cancel As Integer
If IsNull(Me.txtEsDFinInvoice) Then
Beep
MsgBox "Please select the invoice to sign on", vbOKOnly, "Data is required here"
Cancel = True
Exit Sub
End If
Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim fld As DAO.Field
    Dim qdf As DAO.QueryDef
    Dim prm As DAO.Parameter
    Dim root As Dictionary
    Dim transaction As Dictionary
    Dim transactions As Collection
    Dim item As Dictionary
    Dim items As Collection
    Dim Tax As Collection
    Dim i As Long
    Dim j As Long
    Dim t As Long
    Dim itemCount As Long
    Dim taxCount As Long
    Dim strTaxes As Boolean
    Dim invoiceCount As Long
    Dim json As Object
    Dim intPortID As Integer ' Ex. 1, 2, 3, 4 for COM1 - COM4
    Dim lngStatus As Long
    Dim strError  As String
    Dim strData   As String
    Dim Details As Variant
    Dim n As Integer
    Dim s As String
    Dim Z As Long
    Set root = New Dictionary
    Set transactions = New Collection
    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 transaction = New Dictionary
        transaction.Add "PosVendor", "Nector Prime Accounting Solutions"
        transaction.Add "PosSoftVersion", "2.0.0.1"
        transaction.Add "PosModel", "Cap-2017"
        transaction.Add "PosSerialNumber", DLookup("PosSerialNumber", "tblEFDs", "ID = 1")
        transaction.Add "IssueTime", DateAdd("n", 120, Now())
        transaction.Add "TransactionType", DLookup("ReceiptType", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
        transaction.Add "PaymentMode", 3
        transaction.Add "SaleType", 1
        transaction.Add "LocalPurchaseOrder", DLookup("LocalPurchaseOrder", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
        transaction.Add "Cashier", DLookup("Cashier", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
        transaction.Add "BuyerTPIN", DLookup("BuyerTPIN", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
        transaction.Add "BuyerName", DLookup("BuyerName", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
        transaction.Add "BuyerTaxAccountName", DLookup("BuyerTaxAccountName", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
        transaction.Add "BuyerAddress", DLookup("BuyerAddress", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
        transaction.Add "BuyerTel", DLookup("BuyerTel", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
        transaction.Add "OriginalInvoiceCode", DLookup("OrignalInvoiceCode", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
        transaction.Add "OriginalInvoiceNumber", DLookup("OrignalInvoiceNumber", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
        transaction.Add "Memo", DLookup("TheNotes", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
        transaction.Add "Currency-Type", DLookup("MoneyType", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
        transaction.Add "Conversion-Rate", DLookup("FCrate", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
        '--- loop over all the items
        itemCount = Me.txtinternalaudit
        Set items = New Collection
        For i = 1 To itemCount
            Set item = New Dictionary
            item.Add "ItemId", i
            item.Add "Description", DLookup("ProductName", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i))
            item.Add "BarCode", DLookup("BarCode", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i))
            item.Add "Quantity", DLookup("Quantity", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i))
            item.Add "UnitPrice", DLookup("UnitPrice", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i))
            item.Add "Discount", 0
            '--- loop over all the taxes
            taxCount = 1
            Set Tax = New Collection
             strTaxes = DLookup("CGControl", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i))
            '--- loop over all the invoices
            invoiceCount = 1
            For j = 1 To invoiceCount
                For t = 1 To taxCount
            Next t
            item.Add "TaxLabels", Tax
            
            Tax.Add DLookup("TaxClassA", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i))
               If Len(Trim(Nz(DLookup("TourismClass", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i)), ""))) > 0 Then
               Tax.Add Nz(DLookup("TourismClass", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i)), "")
               End If
            If Len(Trim(Nz(DLookup("InsuranceClass", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i)), ""))) > 0 Then
               Tax.Add Nz(DLookup("InsuranceClass", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i)), "")
               End If
            If Len(Trim(Nz(DLookup("ExciseClass", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i)), ""))) > 0 Then
               Tax.Add Nz(DLookup("ExciseClass", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i)), "")
               End If
            item.Add "TotalAmount", DLookup("TotalAmount", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i))
            item.Add "IsTaxInclusive", strTaxes
            item.Add "RRP", DLookup("RRP", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i))
                
            Next j
            
            items.Add item
        Next i
        transaction.Add "Items", items
        
        rs.MoveNext
    Loop
    
    root.Add "", transaction
    On Error Resume Next
    intPortID = Forms!frmLogin!txtFinComPort.value
    Call CommFlush(intPortID)
    If lngStatus <> 0 Then
Application.Quit
ElseIf lngStatus = 0 Then
End If
    ' Write data to serial port.
    ' Build data packet to transmit (passing command code, and data to package)
    strData = BuildData(JsonConverter.ConvertToJson(transaction, Whitespace:=3))
'Here the chief auditor down confirmed the slowness above
n = FreeFile()
Open "C:\Users\chris.hankwembo\Desktop\Testing\test.txt" For Output As #n
Print #n, strData
Close #n
MsgBox "strData:" & vbCrLf & ShowHex(strData)
                
    ' Send the data packet and check for error
    lngStatus = CommWrite(intPortID, strData)
    If lngStatus <> Len(strData) Then
Beep
MsgBox "There is no data to write", vbOKOnly, "Data is required here"
    Application.Quit
    
        ' Handle error.
    lngStatus = CommGetError(strError)
        MsgBox "COM Error: " & strError
    End If

rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set json = Nothing
    Set transaction = Nothing
    Set transactions = Nothing
    Set item = Nothing
    Set items = Nothing
    Set Tax = Nothing
    Set fld = Nothing
    Set root = Nothing
    Set qdf = Nothing
    Set prm = Nothing
    Set Details = Nothing

Exit Sub
Exit_CmdCwrite_Click:
Exit Sub
Err_Handler:
MsgBox Err.Number & Err.Description, vbExclamation, "Error"
Resume Exit_CmdCwrite_Click
End Sub

The above is reading the data from the code below:

SELECT tblCustomerInvoice.InvoiceID, tblLineDetails.ItemesID, tblProducts.ProductName, tblProducts.ProductID, tblLineDetails.Quantity, tblLineDetails.UnitPrice, tblLineDetails.Discount, tblLineDetails.IsTaxInclusive, tblLineDetails.RRP, tblLineDetails.VAT, ((([Quantity]*[UnitPrice]))) AS TotalAmount, tblLineDetails.TaxClassA, tblLineDetails.TourismClass, tblLineDetails.ExciseClass, tblLineDetails.InsuranceClass, IIf([IsTaxInclusive]<0,"True","False") AS CGControl, tblProducts.BarCode, tblLineDetails.ESDPrice, tblCustomerInvoice.FCRate, tblLineDetails.Duty
FROM tblCustomerInvoice INNER JOIN (tblProducts INNER JOIN tblLineDetails ON tblProducts.ProductID = tblLineDetails.ProductID) ON tblCustomerInvoice.InvoiceID = tblLineDetails.InvoiceID
WHERE (((tblCustomerInvoice.InvoiceID)=[Forms]![frmCustomerInvoice]![CboEsdInvoices]));

The problem here is how to do away with DLookup

 

[Minty]

What's the SQL for QryJson? Is that data also on the server?
You should be able to join that to your source data, and why are you looping through it to insert records, when presumably an Insert query could be used instead.

 

[ebs17]

You love DLookup and use it in an inflationary way. A DLookup is a complete query encapsulated in an Access function. It returns exactly one value or NULL if there is no match.

Do you know what you are actually doing? I'll explain it with an image:

You get a lot of guests. Now you think Guest1 will be thirsty, so you run down to the basement and get a bottle of water (= DLookup #1). Now you think Guest2 will be thirsty too, so you run down to the basement again (= DLookup #2). And because you want to treat all guests equally well, your basement stairs will get no rest, and if they're long and narrow and wobbly, you'll need a lot of time to make your guests happy on the first lap.

Another host would take a whole crate of water or several crates at once right in the basement and would only have to go the route once. He would be done much quicker.

Think first, then act.

 

[CJ_London]

if you follow your chain of threads over the last couple of months essentially asking the same question, you will find you were directed to this thread

addressing performance issues

As your db expands and there are more active users, it is quite possible you will experience performance issues and may be considering upsizing to SQL Server or MySQL. Before considering such a transition, you should review your current design: Overview From a performance perspective, Access...

www.access-programmers.co.uk

as to

how to do away with DLookup

it's not clear to me why you need dictionaries and collections but you have a number of dlookups all accessing the same table with the same criteria - these can be replaced with a single recordset.

No wonder your access is slow (50 secs to process 30 lines)

 

[nector]

Its clear here people have misunderstood me, please note that I'm send the data in the correct format as per serial gadget and to say that I do not understand anything is not right unless I'm mistaken, I want to believe that this is a professional site demeaning others in that manner, that language belong to the dust bin.

Below is the final results from the VBA code converter above and working ok , all I need to make it work fast nothing else:

]  Á{
   "PosVendor": "Nector Prime Accounting Solutions",
   "PosSoftVersion": "2.0.0.1",
   "PosModel": "Cap-2017",
   "PosSerialNumber": "100100001829",
   "IssueTime": "20230130120252",
   "TransactionType": 0,
   "PaymentMode": 3,
   "SaleType": 1,
   "LocalPurchaseOrder": null,
   "Cashier": "Admin Manager",
   "BuyerTPIN": null,
   "BuyerName": "MWIINDE",
   "BuyerTaxAccountName": "MWIINDE",
   "BuyerAddress": null,
   "BuyerTel": null,
   "OriginalInvoiceCode": null,
   "OriginalInvoiceNumber": null,
   "Memo": null,
   "Currency-Type": "ZMW",
   "Conversion-Rate": 1,
   "Items": [
      {
         "ItemId": 1,
         "Description": "MIRINDA ORANGE 350 MLS",
         "BarCode": "009090",
         "Quantity": 19,
         "UnitPrice": 30,
         "Discount": 0,
         "TaxLabels": [
            "A"
         ],
         "TotalAmount": 570,
         "IsTaxInclusive": true,
         "RRP": 0
      }
   ]
}{

 

[Minty]

What we are saying is that you have been given some large hints at how to make things work more efficiently but don't seem to be taking them on board.

Your DLookups are almost certainly not required, as both the Json query referenced and the outputs could probably be created as views within SQL server and joined, meaning the only looping required would be through a final recordset to create your text stream output file, which should be relatively fast.

I parse recordsets out to text delimited files for automatic FTP upload, and it processes 70-100 rows in about 3-4 seconds.
So your current technique is not efficient.

You have posted what looks like the final output above which is not what I asked you to give us an example of.

 

[nector]

Many thanks your reply is now formal and ok. I followed your advice and removed all the Dlookup by using the already opened QryJons see below:

Private Sub CmdCwrite_Click()
Dim Cancel As Integer
If IsNull(Me.txtEsDFinInvoice) Then
Beep
MsgBox "Please select the invoice to sign on", vbOKOnly, "Data is required here"
Cancel = True
Exit Sub
End If
Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim fld As DAO.Field
    Dim qdf As DAO.QueryDef
    Dim prm As DAO.Parameter
    Dim root As Dictionary
    Dim transaction As Dictionary
    Dim transactions As Collection
    Dim item As Dictionary
    Dim items As Collection
    Dim Tax As Collection
    Dim i As Long
    Dim j As Long
    Dim t As Long
    Dim itemCount As Long
    Dim taxCount As Long
    Dim strTaxes As Boolean
    Dim invoiceCount As Long
    Dim json As Object
    Dim intPortID As Integer ' Ex. 1, 2, 3, 4 for COM1 - COM4
    Dim lngStatus As Long
    Dim strError  As String
    Dim strData   As String
    Dim Details As Variant
    Dim n As Integer
    Dim s As String
    Dim Z As Long
    Set root = New Dictionary
    Set transactions = New Collection
    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)
        rs.MoveFirst
    Do While Not rs.EOF
        Set transaction = New Dictionary
        transaction.Add "PosVendor", "Nector Prime Accounting Solutions"
        transaction.Add "PosSoftVersion", "2.0.0.1"
        transaction.Add "PosModel", "Cap-2017"
        transaction.Add "PosSerialNumber", "100100001829"
        transaction.Add "IssueTime", DateAdd("n", 120, Now())
        transaction.Add "TransactionType", "ReceiptType"
        transaction.Add "PaymentMode", "PaymentMode"
        transaction.Add "SaleType", "SalesType"
        transaction.Add "LocalPurchaseOrder", "LocalPurchaseOrder"
        transaction.Add "Cashier", "Cashier"
        transaction.Add "BuyerTPIN", "BuyerTPIN"
        transaction.Add "BuyerName", "BuyerName"
        transaction.Add "BuyerTaxAccountName", "BuyerTaxAccountName"
        transaction.Add "BuyerAddress", "BuyerAddress"
        transaction.Add "BuyerTel", "BuyerTel"
        transaction.Add "OriginalInvoiceCode", "OrignalInvoiceCode"
        transaction.Add "OriginalInvoiceNumber", "OrignalInvoiceNumber"
        transaction.Add "Memo", "TheNotes"
        transaction.Add "Currency-Type", "MoneyType"
        transaction.Add "Conversion-Rate", "FCrate"
        '--- loop over all the items
        itemCount = Me.txtinternalaudit
        Set items = New Collection
        For i = 1 To itemCount
            Set item = New Dictionary
            item.Add "ItemId", i
            item.Add "Description", "ProductName" & " AND ItemesID =" & CStr(i)
            item.Add "BarCode", "BarCode" & " AND ItemesID =" & CStr(i)
            item.Add "Quantity", "Quantity" & " AND ItemesID =" & CStr(i)
            item.Add "UnitPrice", "UnitPrice" & " AND ItemesID =" & CStr(i)
            item.Add "Discount", 0
            '--- loop over all the taxes
            taxCount = 1
            Set Tax = New Collection
             strTaxes = "CGControl" & " AND ItemesID =" & CStr(i)
            '--- loop over all the invoices
            invoiceCount = 1
            For j = 1 To invoiceCount
                For t = 1 To taxCount
            Next t
            item.Add "TaxLabels", Tax
            
            Tax.Add "TaxClassA" & " AND ItemesID =" & CStr(i)
               If Len(Trim(Nz("TourismClass" & " AND ItemesID =" & CStr(i), ""))) > 0 Then
               Tax.Add Nz("TourismClass" & " AND ItemesID =" & CStr(i), "")
               End If
            If Len(Trim(Nz("InsuranceClass" & " AND ItemesID =" & CStr(i), ""))) > 0 Then
               Tax.Add Nz("InsuranceClass" & " AND ItemesID =" & CStr(i), "")
               End If
            If Len(Trim(Nz("ExciseClass" & " AND ItemesID =" & CStr(i), ""))) > 0 Then
               Tax.Add Nz("ExciseClass" & " AND ItemesID =" & CStr(i), "")
               End If
            item.Add "TotalAmount", "TotalAmount" & " AND ItemesID =" & CStr(i)
            item.Add "IsTaxInclusive", strTaxes
            item.Add "RRP", "RRP" & " AND ItemesID =" & CStr(i)
                
            Next j
            
            items.Add item
        Next i
        transaction.Add "Items", items
        
        rs.MoveNext
    Loop
    
    root.Add "", transaction
    On Error Resume Next
    intPortID = Forms!frmLogin!txtFinComPort.value
    Call CommFlush(intPortID)
    If lngStatus <> 0 Then
Application.Quit
ElseIf lngStatus = 0 Then
End If
    ' Write data to serial port.
    ' Build data packet to transmit (passing command code, and data to package)
    strData = BuildData(JsonConverter.ConvertToJson(transaction, Whitespace:=3))
'Here the chief auditor down confirmed the slowness above
n = FreeFile()
Open "C:\Users\chris.hankwembo\Desktop\Testing\test.txt" For Output As #n
Print #n, strData
Close #n
MsgBox "strData:" & vbCrLf & ShowHex(strData)
                
    ' Send the data packet and check for error
    lngStatus = CommWrite(intPortID, strData)
    If lngStatus <> Len(strData) Then
Beep
MsgBox "There is no data to write", vbOKOnly, "Data is required here"
    Application.Quit
    
        ' Handle error.
    lngStatus = CommGetError(strError)
        MsgBox "COM Error: " & strError
    End If

rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set json = Nothing
    Set transaction = Nothing
    Set transactions = Nothing
    Set item = Nothing
    Set items = Nothing
    Set Tax = Nothing
    Set fld = Nothing
    Set root = Nothing
    Set qdf = Nothing
    Set prm = Nothing
    Set Details = Nothing

Exit Sub
End Sub

The only area where I'm getting type mismatch is below the rest of the code the auditor has written the matching data:

item.Add "Description", "ProductName" & " AND ItemesID =" & CStr(i)

The problem here is that I need to concatenate Itemes ID from a number to string

 

[Minty]

Unless I'm being stupid, I can't see where you reference anything from the record set created at the top and looped through?
In other words, what is the point of the recordset, when you appear to be doing everything in a dictionary or collection?

 

[Pat Hartman]

I'm sure you saw some article about the dictionary object and said - well that's cool - let me use that. Well, just because you can do something, doesn't mean you should. The dictionary object is far more useful in Word and Excel than it is in Access where you have actual tables.

The "dictionary" is simply an in memory table and you are building it in the most inefficient way possible.
1. you probably should be using a query rather than the dictionary object
2. to build the dictionary, at least use a join rather than domain functions to lookup values

 

[nector]

I'm sure you saw some article about the dictionary object and said - well that's cool - let me use that. Well, just because you can do something, doesn't mean you should. The dictionary object is far more useful in Word and Excel than it is in Access where you have actual tables

Again there we go! Kindly if you have nothing to say with value you do us a favour by slimply keeping quite.

The correct answer to iterate in the record set is below not what you are suggesting, thank God I did not get bothered with your comments:

transaction.Add "LocalPurchaseOrder", rs!LocalPurchaseOrder
        transaction.Add "Cashier", rs!Cashier.value
        transaction.Add "BuyerTPIN", rs!BuyerTPIN.value
        transaction.Add "BuyerName", rs!BuyerName.value
        transaction.Add "BuyerTaxAccountName", rs!BuyerTaxAccountName.value
        transaction.Add "BuyerAddress", rs!BuyerAddress.value

 

[ebs17]

Your tone is inappropriate, to say the least.

What is the value of your last post? Filling a dictionary certainly doesn't take minutes.

In general: A chain is as strong as its weakest link. If you want to find this weakest link (= performance brake), it is less suitable to show this and that link in the chain. If, out of your wisdom, you pointed out the right link, you could fix it yourself and not send out a general cry for help.

So in order to find the weak point, you would have to look at the entire chain, i.e. the entire process ... at least that would be my approach. If you trust me to be able to help, then I see the whole thing myself and am less interested in individual stories, which often lack essential details.

The whole process: Look at your posts and "information" and say where you can see and understand this. I can't really see any of that. Whenever you have to guess or interpret something because it is not clearly and adequately described, any subsequent consideration is a waste of time.

Remember, it's YOUR problem, not someone else's.

 

[Minty]

You have an unfortunate tone - and you need to listen to what experienced users are telling you. You don't need the Dictionary at all.

You are creating a record set with your desired information. Until I pointed out you weren't using it it was pointless, and the routine couldn't have worked.
You have now added the record set fields to the process, however, you are simply duplicating the record set data by adding this same data to another object (the dictionary).

You are then using the dictionary to create your text file. It's completely unnecessary. Simply use the recordset objects directly.

You have a header section and line detail. The header section is fixed data for the export, the line detail comes from the recordset.
Why bother with the dictionary, it's another complication you simply don't need.

To paraphrase your post - "The correct answer is to simply use the recordset."

 

[Pat Hartman]

Again there we go! Kindly if you have nothing to say with value you do us a favour by slimply keeping quite.

You "heard" nothing valuable. Feel free to block me. Then you won't be offended. Or, if you prefer, I can add you to my cannot be helped list so I don't waste my time trying to help you.

 

[spaLOGICng]

I Have checked all the forms even if binded on actual tables as long as the indexes are there the performance is realy good. The nightmare is on the queries they are extremely slow, can you Imagin one line take 3 minutes to process and just 10 lines takes 40 minutes.

Here is the actual query below , but when you run it against access database it can process 30 lines in 50 seconds , then why it is too slow in Cloud Ms Sql Server:

SELECT tblCustomerInvoice.InvoiceID, tblLineDetails.ItemesID, tblProducts.ProductName, tblProducts.ProductID, tblLineDetails.Quantity, tblLineDetails.UnitPrice, tblLineDetails.Discount, tblLineDetails.IsTaxInclusive, tblLineDetails.RRP, tblLineDetails.VAT, ((([Quantity]*[UnitPrice]))) AS TotalAmount, tblLineDetails.TaxClassA, tblLineDetails.TourismClass, tblLineDetails.ExciseClass, tblLineDetails.InsuranceClass, IIf([IsTaxInclusive]<0,"True","False") AS CGControl, tblProducts.BarCode, tblLineDetails.ESDPrice, tblCustomerInvoice.FCRate, tblLineDetails.Duty
FROM tblCustomerInvoice INNER JOIN (tblProducts INNER JOIN tblLineDetails ON tblProducts.ProductID = tblLineDetails.ProductID) ON tblCustomerInvoice.InvoiceID = tblLineDetails.InvoiceID
WHERE (((tblCustomerInvoice.InvoiceID)=[Forms]![frmCustomerInvoice]![CboEsdInvoices]));

Hi,

Is your cloud MS Server on Azure or AWS, etc.? Are the DTU's or CORES provisioned? Do you have Auto-tuning turned on? I am on the fence about Auto-tuning. The resource only becomes faster the more it is used. I prefer self-managed indexes.

Have you tried to implement the Query as a VIEW on the Server and then link to it?

My experience with Cloud Databases to increase performance:

1) Make sure there is a Timestamp or RowVersion Column in EACH Table.
2) Create VIEWS representing the Tables needed, with only the specified Columns
3) Replicate Access Queries into VIEWS and then connect to the VIEW

If the VIEW needs updating, make sure that you include the Primary Key from each Table in the View and aliased so not conflict if the names are the same. I ALAWS have a Column in each Table named "ID", that is the IDENTITY(1,1). The ID is ALWAYS the Primary Key. I never use a human created Value for the PK, but I will CREATE UNIQUE INDEX for the Column as needed. I never create Multi-Column PK's. I will CREATE UNIQUE INDEX that has multiple columns.

When you link to the VIEW Access will ask for a PK. If you need the PK to perform an Update, select the ID Column from the primary Table. Whe you refresh links, it may lose the PK assignment, I solve for this in my AutoExec macro that calls a Startup procedure. There is a sub procedure that is called to "Set_PKs()" In the Set_PKs Procedure I will have a list of Tables that are linked to Views to create the PK on the View. Because I always use the ID column as the PK, I do not have to guess what the name of the Column is. I use the

Sub Set_PKs()

On Error Resume Next

'Add Primary Keys Here.
'They tend to get lost when refreshing Views.

With CurrentDb
.Execute "CREATE UNIQUE INDEX [PK_vwView_Name_Id] ON [vwViewName] ([ID]) WITH PRIMARY;"
End With

End Sub

Just some tips to help mitigate performance.

 

[The_Doc_Man]

I'll take a crack at this. In your post #5, you showed some code that had several DLookups that all were based on the same exact criterion string, which was Me.CboEsdInvoices & " AND ItemesID =" & CStr(i) - and you complained that you were getting poor performance. I will explain why. You were using a DLookup which performs steps equivalent to opening a recordset (which involves the overhead of reading the table definition to find the field definitions), then performing the equivalent to a FindFirst based on the criterion. After that, it extracts the named field from that record and then releases the recordset. Each time you do a DLookup, you run a query. EACH TIME.

If you just manually opened a recordset beforehand to the same criteria, you would be able to directly extract the values from that record without doing all of the overhead steps for each DLookup. You asked about performance. That is why folks are targeting your use of DLookup. Using it incurs extra overhead steps behind the scenes - steps that could EASILY be avoided by having a second recordset open at the time for the lookup of the right record, after which it becomes child's play to find each field of interest.

Your tone is hard to understand, though. Do you realize that the folks here are all volunteers? We don't get paid, we don't get laid, we get NOTHING except some degree of personal satisfaction that we can help someone. When you reply sharply to some of our members who have been trying to advise you of ways to improve things, you seem to think they are insulting your intelligence or something. No, far from it. We have seen how sometimes Access can be counter-intuitive. A lot of us are experienced Access programmers. (You DO understand the true meaning of "experienced" don't you? We can recognize mistakes so easily because we've made so many of them ourselves.) Lose the personality and you will get a lot more out of this experience. Maybe even including some answers.

 

[nector]

Many thanks dear valued professionals below is how I sorted out that problem just in case other may want to learn something, now it works faster even combining with Json:

Private Sub CmdComWrite_Click()
On Error GoTo Err_Handler
Dim Cancel As Integer
If IsNull(Me.CboEsdss) Then
Beep
MsgBox "Please select the invoice to sign off", vbOKOnly, "Invoice to sign off not selected"
Cancel = True
Exit Sub
End If
Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim fld As DAO.Field
    Dim qdf As DAO.QueryDef
    Dim prm As DAO.Parameter
    Dim root As Dictionary
    Dim transaction As Dictionary
    Dim transactions As Collection
    Dim item As Dictionary
    Dim items As Collection
    Dim Tax As Collection
    Dim i As Long
    Dim Z As Integer
    Dim j As Long
    Dim t As Long
    Dim itemCount As Long
    Dim taxCount As Long
    Dim strTaxes As Boolean
    Dim invoiceCount As Long
    Dim json As Object
    Dim intPortID As Integer ' Ex. 1, 2, 3, 4 for COM1 - COM4
    Dim lngStatus As Long
    Dim strError  As String
    Dim strData   As String
    Dim itemiz As Dictionary
    Dim s As String
    Dim n As Integer
    Dim Details As Variant
    Set root = New Dictionary
    Set transactions = New Collection
    Set db = CurrentDb
    Set qdf = db.QueryDefs("QryJsonPos")
    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 transaction = New Dictionary
        transaction.Add "PosVendor", "Nector Prime Accounting Solutions"
        transaction.Add "PosSoftVersion", "2.0.0.1"
        transaction.Add "PosModel", "Cap-2017"
        transaction.Add "PosSerialNumber", "18000018963"
        transaction.Add "IssueTime", DateAdd("n", 120, Now())
        transaction.Add "TransactionType", rs!TransactionType.value
        transaction.Add "PaymentMode", 0
        transaction.Add "SaleType", 0
        transaction.Add "LocalPurchaseOrder", rs!LocalPurchaseOrder.value
        transaction.Add "Cashier", rs!Cashier.value
        transaction.Add "BuyerTPIN", rs!BuyerTPIN.value
        transaction.Add "BuyerName", rs!BuyerName.value
        transaction.Add "BuyerTaxAccountName", rs!BuyerTaxAccountName.value
        transaction.Add "BuyerAddress", rs!BuyerAddress.value
        transaction.Add "BuyerTel", rs!BuyerTel.value
        transaction.Add "OriginalInvoiceCode", rs!OrignalInvoiceCode.value
        transaction.Add "OriginalInvoiceNumber", rs!OrignalInvoiceNumber.value
        transaction.Add "Memo", rs!TheNotes.value
        transaction.Add "Currency-Type", rs!CurrencyType.value
        transaction.Add "Conversion-Rate", rs!FCRate.value
        '--- loop over all the items
        itemCount = Me.txttestAuditESD
        Set items = New Collection
        For i = 1 To itemCount
            Set item = New Dictionary
            item.Add "ItemId", i
            item.Add "Description", rs!ProductName.value
            item.Add "BarCode", rs!BarCode.value
            item.Add "Quantity", rs!QtySold.value
            item.Add "UnitPrice", rs!SellingPrice.value
            item.Add "Discount", rs!Discount.value
            '--- loop over all the taxes
            taxCount = 1
            Set Tax = New Collection
             strTaxes = True
            '--- loop over all the invoices
            invoiceCount = 1
            For j = 1 To invoiceCount
                For t = 1 To taxCount
            Next t
            item.Add "TaxLabels", Tax
            
            Tax.Add rs!TaxClassA.value
               If Len(Trim(Nz(rs!TourismClass.value, ""))) > 0 Then
               Tax.Add Nz(rs!TourismClass.value, "")
               End If
            If Len(Trim(Nz(rs!InsuranceClass.value, ""))) > 0 Then
               Tax.Add Nz(rs!InsuranceClass.value, "")
               End If
            If Len(Trim(Nz(rs!ExciseClass.value, ""))) > 0 Then
               Tax.Add Nz(rs!ExciseClass.value, "")
               End If
            
            item.Add "TotalAmount", rs!TotalAmount.value
            item.Add "IsTaxInclusive", strTaxes
            item.Add "RRP", rs!RRP.value
            
            Next j
            
            items.Add item
        Next i

        transaction.Add "Items", items
        
        rs.MoveNext
    Loop
    
    root.Add "", transaction
    intPortID = Forms!frmLogin!txtFinComPort.value
    Call CommFlush(intPortID)
    If lngStatus <> 0 Then
Application.Quit
ElseIf lngStatus = 0 Then
End If
   ' Build data packet to transmit (passing command code, and data to package)
    strData = BuildData(JsonConverter.ConvertToJson(transaction, Whitespace:=3))
                  
    ' Send the data packet and check for error
    lngStatus = CommWrite(intPortID, strData)
    If lngStatus <> Len(strData) Then
        ' Handle error.
    lngStatus = CommGetError(strError)
        MsgBox "COM Error: " & strError
    End If

rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set json = Nothing
    Set transaction = Nothing
    Set transactions = Nothing
    Set item = Nothing
    Set items = Nothing
    Set Tax = Nothing
    Set fld = Nothing
    Set root = Nothing
    Set qdf = Nothing
    Set prm = Nothing
    Set Details = Nothing
Exit Sub
Exit_CmdComWrite_Click:
Exit Sub
Err_Handler:
MsgBox Err.Number & Err.Description, vbExclamation, "Error"
Resume Exit_CmdComWrite_Click
End Sub