|
|
|
|
|
Warning:
This is a technical discussion for advanced users only
It is possible for an SQL statement to generate more than one result-set. There are two circumstances where this happens:
AQT can deal with both of these cases, however there is a technical issue with the some databases that you should be aware of if you are using this feature. This problem has been seen for Sybase, MS SQL Server and Teradata.
If you wish to run multiple SQL statements you would normally code these as separate statements, delimited by a semicolon. Example:
Update dbo.Products Set UnitPrice=30.0000 where ProductID=7;
SELECT count(*) FROM Products;
AQT will interpret this as two statements, and will run them separately.
Sybase and MS SQL Server also allow you to run a block of Transact-SQL code. This can include multiple statements, and will be run in AQT as a single statement.
Example:
declare @CategoryId int
select @CategoryId=2
select count(*) from Products where CategoryId = @CategoryId
select * from Products where CategoryId = @CategoryId
Code Blocks such as this can return multiple result-sets.
In order to deal with statements that return more than one result-set, the logic in AQT for processing queries is as follows:
This works fine on all databases except Sybase, MS SQL Server and Teradata.
When AQT processes a query as described in the previous paragraph, it hits a major performance problem with Sybase, MS SQL Server and Teradata.
This problem happens when AQT is accessing a large table. AQT will not read the whole table, but just the number of rows specified in the Display-Limits options. For instance, if you have a million-row table, and the Display-Limit is set to 1000, AQT will read only 1000 rows from the table.
When AQT does the SQLMoreResults, Sybase/SQL Server/Teradata will then read all the remaining rows in table. In our example of the million-row table, when the SQLMoreResults is done, Sybase/SQL Server/Teradata will read the remaining 999000 rows before moving to the next result-set. This will take a very long time, and cause a high I/O load on your database.
It is unclear whether this is bug in the ODBC Drivers for Sybase/SQL Server/Teradata, or whether it is working as "designed".
To prevent this problem from happening, for Sybase/SQL Server/Teradata we will not issue the SQLMoreResults unless:
The only impact of this is that if you are running a Transact-SQL code-block in AQT, you may not see all the results-sets that are being generated.
This is one of the Technical Parameter options. When this option is selected, AQT will always issue the SQLMoreResults in all circumstances. If you are running Sybase / SQL Server / Teradata code-blocks, you may wish to set this option on.
If you access any large tables with this option set on, you will notice very long response times.
