Code-blocks in Sybase / MS SQL Server

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 (bug?) with the Sybase and SQL Server that you should be aware of if you are using this feature.

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 and MS SQL Server

Problem with Sybase and MS SQL Server

When AQT processes a query as described in the previous paragraph, it hits a major performance problem with Sybase and MS SQL Server.

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 will at that point read all the remaining rows in table. In our example of the million-row table, when the SQLMoreResults is done, Sybase/Server 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 Sybase/SQL Server, or whether it is working as "designed".

Circumvention

To prevent this problem from happening, for Sybase and SQL Server 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 and SQL Server 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 and SQL Server

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 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.