When using the “ODBC” or “OLEDB” tools in TaskCentre, it may appear that a single query is executed on the database. In actual fact from trial and error, I have identified multiple queries are being called.
For example, if you run the following query (This query identifies all Item Ledger Entries, where the timestamp is odd):
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 1000 [Item No_]
,[Posting Date]
,[Description]
FROM [dbo].[CRONUS UK Ltd_$Item Ledger Entry]
WHERE ([timestamp] % 2) <> 0
Notice, that the transaction isolation level is READ UNCOMMITTED. This means that the query will read the dirty data and not block other users. Also it is limited to 1000 rows.
However when the task is running, it appears that the following SQL queries are run against the database:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 1000 [Item No_]
,[Posting Date]
,[Description]
FROM [dbo].[CRONUS UK Ltd_$Item Ledger Entry]
WHERE ([timestamp] % 2) <> 0
and
SELECT COUNT(*)
FROM [dbo].[CRONUS UK Ltd_$Item Ledger Entry]
WHERE ([timestamp] % 2) <> 0
The first query returns the data, the 2nd query returns the number of rows.
Notice in the 2nd query, the transaction isolation level is not declared… (In this case, will cause blocking). Also the TOP statement has been removed too, meaning that all records are counted – not good especially when there is a complex filter such as the above which can be time consuming.
Solution
A solution for this is to disable the counting of the records. One way I have identified how to do this is to UNION to a table returning no rows. (As obviously you don’t want to return rows to corrupt your SQL query).
In this example:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 1000 [Item No_]
,[Posting Date]
,[Description]
FROM [dbo].[CRONUS UK Ltd_$Item Ledger Entry]
WHERE ([timestamp] % 2) <> 0
UNION
(SELECT NULL,NULL,NULL WHERE 1<>1)
I’ve identified that when UNION is used, Taskcentre does not count the number of records. This means that if you are using the data, the “RowCount” will return -1 instead of the actual data. If you need this for a further step (such as a decision), then you have to calculate it by looping round in VB as per this knowledgebase article.
Note and Disclaimer:
I don’t have knowledge of how the software actually works internally, so this document is only based on my experience and investigation, so is provided as-is.
