Multiple result-sets for Sybase / MS SQL Server / Teradata

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:

• stored procedure. A stored procedure can return multiple result-sets
• code-block. This is discussed in detail below.

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.

Code-Block

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.

How AQT processes code-blocks

In order to deal with statements that return more than one result-set, the logic in AQT for processing queries is as follows:

1. process result-set
2. get next result-set (which is ODBC call SQLMoreResults)
3. if there is another result-set, go to 1), else finish

This works fine on all databases except Sybase, MS SQL Server and Teradata.

Problem with 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".

Circumvention

To prevent this problem from happening, for Sybase/SQL Server/Teradata we will not issue the SQLMoreResults unless:

• AQT has read all the rows in the result-set
• a stored procedure is being run
• option Allow multiple result-sets for Sybase, SQL Server / Teradata has been specified (see below)

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.

Allow multiple result-sets for Sybase, SQL Server and Teradata

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.