Don't Confuse LastPlanText With LastStatement

Here's how the SQL Anywhere 16 Help describes the LastPlanText and LastStatement connection properties:

LastPlanText Returns the long text plan of the last query executed on the connection. You control the remembering of the last plan by setting the RememberLastPlan option of the sa_server_option system procedure, or using the -zp server option. See -zp database server option. LastStatement Returns the most recently prepared SQL statement for the current connection. The LastStatement value is set when a statement is prepared, and is cleared when a statement is dropped. Only one statement string is remembered for each connection. If sa_conn_activity reports a non-empty value for a connection, it is most likely the statement that the connection is currently executing. If the statement had completed, it would likely have been dropped and the property value would have been cleared. If an application prepares multiple statements and retains their statement handles, then the LastStatement value does not reflect what a connection is currently doing. When client statement caching is enabled, and a cached statement is reused, this property returns an empty string.

It's easy to assume both properties apply to the same SQL statement, and they often do... but they don't have to. Here's an example using the code from this article:

CREATE PROCEDURE p() BEGIN WAITFOR DELAY '00:00:01'; SELECT COUNT_BIG(*) FROM SYSTAB AS A CROSS JOIN SYSTABCOL AS B CROSS JOIN SYSUSER AS C; END; SELECT * FROM p();

This Foxhound snapshot shows SELECT * FROM p() as the Last Statement value, but the Last Plan Text applies to a different statement, the SELECT COUNT_BIG(*) statement inside the stored procedure:



That's the difference between these two connection properties:

• LastStatement shows what came from the client application,
  

• whereas LastPlanText applies to the last query run by the server whether it came from the client or (in this case) came from within a stored procedure.
  

Are you SURE about that?

Question: Is it possible the behavior of LastPlanText and LastStatement in the example above is related to peculiar symptoms described in Beware The Slow Result Set Query and Hunting The Slow Result Set Query where the SELECT statement used to return a result set didn't show up at all in the procedure profiler and was fairly well hidden in the database tracing output?

Answer: Let's run a test with a stored procedure that contains time-consuming queries but doesn't return a result set at all:

CREATE PROCEDURE p ( OUT @x INTEGER ) BEGIN DECLARE @y INTEGER; SELECT COUNT_BIG(*) INTO @x FROM SYSUSER AS A CROSS JOIN SYSUSER AS B CROSS JOIN SYSUSER AS C CROSS JOIN SYSUSER AS D; SELECT COUNT_BIG(*) INTO @y FROM SYSTAB AS A CROSS JOIN SYSTABCOL AS B CROSS JOIN SYSUSER AS C; SET @x = @x + @y; END; CREATE OR REPLACE VARIABLE @x INTEGER; CALL p ( @x ); SELECT @x;

When execution hit the first slow SELECT INTO @x statement, Foxhound showed that LastStatement contains the CALL statement but LastPlanText showed the plan for the SELECT:



When the next slow SELECT INTO @y started executing, Foxhound still showed the CALL in LastStatement, but LastPlanText contained the plan for the next SELECT:



That pretty much confirms LastStatement is a client statement thing whereas LastPlanText follows the plans wherever they may be coming from, client or server.

Note: The "Show More" and "Show Less" buttons will be available in Foxhound Version 3 when it is released.