[QODBC ALL] - How to increase the update or delete SQL statement performance.

Problem Description:

Ran this SQL to move an invoice from one Customer to another:

Update invoice set CustomerRefListID = '1500' where TxnNumber = 8453

It took about 15+ minutes, and the status dialog showed it waiting on QuickBooks as it went through all 2,500 customers - but it took over 15 minutes to complete! I have a situation where moving an invoice to another Customer may occur several times in a batch of invoices. Are there additional conditions I can put in the WHERE clause that would speed this up?

Solution:

Please make sure Optimizer is enabled.  - https://qodbc.com/links/2358

If you are executing an SQL statement for the first time on this table, QODBC has built the optimizer before processing the SQL statement. "Waiting on QuickBooks" may take longer than usual.

Please run the SQL statement again, and if you are still facing the same issue, please try the following options.

Make use of the index fields such as TxnID or TxnDate to filter the number of records scanned.

Update invoice set CustomerRefListID = '1500' where TxnID='ValueOfYourTxnID'

or

Update invoice set CustomerRefListID = '1500' where TxnDate>={d'2022-11-10'} and  TxnDate<={d'2022-11-15'} and  TxnNumber = 8453

If you are still facing the issue, please try using the UNOPTIMIZED keyword.

Update invoice UNOPTIMIZED set CustomerRefListID = '1500' where TxnID='ValueOfYourTxnID'

Problem Description:

We have low performance in SQL Server Linked Server using QREMOTE.

For example, the below statement is taking less than 2 seconds in the QODBC 64-bit Test tool and more than 2 minutes in our linked server:
UPDATE Customer SET JobTitle = 'Job3' WHERE ListID='8000000F-1466614340'.

In SQL Server, the statement seems to scan the whole table to be executed (14000 records)

We are running QuickBooks and SQL Server on the same machine on a cloud server. All drivers are 64bits. The same system DSN is used in Test Tool and in SQL Linked server: QuickBooks Data 64-bit QRemote

Solution:

Please make sure Optimizer is enabled.  - https://qodbc.com/links/2358

Update QREMOTE...customer set JobTitle = 'Job' where ListID='8000000F-1466614340'

When you use the above query, SQL Server will fetch all the data from the Customer table. SQL server will then apply the filter of List ID.

SQL Server will generate an update statement from the derived result and send it to QODBC/QRemote.

Thus, for a single record update, the entire table is scanned.

To avoid this, use OpenQuery and select only the record you want to update.

Please use the Index fields in the "select" and "update" clauses.

Example:
update openquery (QRemote, 'select ListID, JobTitle from Customer where ListID=''8000000F-1466614340''') set JobTitle = 'Job' where ListID='8000000F-1466614340'

Note: Here, we are using ListID (the index field) in the select statement and the OpenQuery update statement.

If you are still facing the issue, please use the UNOPTIMIZED keyword.

update openquery (QRemote, 'select ListID, JobTitle from Customer UNOPTIMIZED where ListID=''8000000F-1466614340''') set JobTitle = 'Job' where ListID='8000000F-1466614340'