What is the quickest statement?

MCMartin · ‎2014 May 05

Which statement will need the minimum time to execute? Sure Select 1 will be a candidate, but is this really the fastest?

ian_mchardy · ‎2014 May 06

Martin, you never actually said what you are trying to do. If you are trying to ensure there is a round-trip to the server to ensure it is available:

from DBLib, I believe db_ping_server( &sqlca, 0, NULL, NULL, NULL ) (apparently undocumented) is actually the most efficient request in that it actually does a round trip and requires the least amount of server processing.
from most APIs (including ODBC, OLE DB, ADO.NET [I think], SQL Anywhere JDBC), executing anything that returns a result set requires multiple round trips, so probably isn't the best candidate. From ESQL, you can do an EXEC SQL SELECT 1 INTO :hostvar and I believe that only requires one round trip. FYI, you can count round trips by comparing connection_property( 'RequestsReceived' ) before and after say 100 of the request in question. Note getting this property value with require around two round trips in most APIs.
while a commit or rollback is certainly a quick request when there is nothing to be committed or rolled back, the client side doesn't acutally send an API (for example EXEC SQL COMMIT WORK or ODBC SQLEndTran) commit or rollback to the server if the last request was a commit or rollback (a performance optimization). But with SQL Anywhere, you can prepare and execute a COMMIT or ROLLBACK, which should be one of the quickest requests if there is nothing to commit or rollback.
another example of a quick statement, is set @int_var = 0, assuming a create variable @int_var int was already performed.
if knowing if the connection is still active is good enough, then instead of polling the server with a request, from DBLib look at the DB_CALLBACK_CONN_DROPPED callback. From ODBC, look at SQL_ATTR_CONNECTION_DEAD.

I hope this helps.

Breck_Carter · ‎2014 May 06

FWIW here's an excerpt from the Foxhound Version 3 Help (now in beta)...

Latency... Heartbeat, Sample Time

Latency, also known as response time or access time, is a measure of how long it takes the the database to respond to a single request: The Heartbeat column shows how long it took for Foxhound to execute the SELECT * FROM DUMMY heartbeat or "canarian" query. This number should be very small, and is often displayed as 0s or 0.1s.

The Sample Time column shows how long it took for Foxhound to gather all the performance data for this sample. The sample time should always be longer than the heartbeat time, and it will grow longer with an increasing number of connections because of the extra work involved to analyze each sample.

The Heartbeat and Sample Time numbers are calculated by Foxhound; they are not based on any builtin SQL Anywhere properties.

Although Heartbeat and Sample times are calculated to the millisecond (0.001 second), Foxhound doesn't show them with any more precision than one decimal place (0.1 second) because the underlying logic is no more precise than that. Sample times are simply rounded to the nearest tenth of a second.

However, Heartbeat times are often very small and would appear as zero if they were rounded to the nearest tenth of a second. Because it may be important to see the difference between zero and non-zero values, Heartbeat times are handled differently: zero values are shown as 0s and non-zero values are adjusted upwards to the nearest tenth of a second; i.e, the smallest non-zero value shown is 0.1s even if the calculated Heartbeat time is as small as 0.001 second.

The formula used is ROUND ( heartbeat_time_in_milliseconds + 49, -2 ).

A long Sample Time may indicate a performance problem with Foxhound rather than the target database; e.g.:

Sample Time is over 1 second but everything else is running OK on the target database.
Sample Time is large but the Heartbeat time remains small; e.g., 0s or .1s.
Sample Time increases rapidly with the number of connections to the target database.
Sample Time increases with the number of connections but the Heartbeat time remains small; e.g., 0s or .1s.
Sample Time is over 10 seconds causing the Interval time to also exceed 10 seconds.

To see where the performance problem lies, repeatedly run the following ISQL script against your target database; if this script runs quickly but Foxhound reports a large Sample Time, the problem lies with Foxhound:

SELECT * INTO #temp1 FROM rroad_engine_properties();
SELECT * INTO #temp2 FROM rroad_database_properties(1);
SELECT * INTO #temp3 FROM rroad_connection_properties();
DROP TABLE #temp1;
DROP TABLE #temp2;
DROP TABLE #temp3;

MarkCulp · ‎2014 May 05

waitfor delay '0:00';

Breck_Carter · ‎2014 May 05

GOTO a
a:

GOTO wins by a WIDE margin 🙂

BEGIN
DECLARE @t TIMESTAMP;
DECLARE @loop_counter INTEGER;
DECLARE @msec INTEGER;
SET @t = CURRENT TIMESTAMP;
SET @loop_counter = 1;
WHILE @loop_counter <= 1000000 LOOP
   SET @loop_counter = @loop_counter + 1;
END LOOP;
SET @msec = DATEDIFF ( MILLISECOND, @t, CURRENT TIMESTAMP );
SELECT @msec AS "1000000 empty loop";
END;

BEGIN
DECLARE @t TIMESTAMP;
DECLARE @loop_counter INTEGER;
DECLARE @msec INTEGER;
SET @t = CURRENT TIMESTAMP;
SET @loop_counter = 1;
WHILE @loop_counter <= 100 LOOP
   WAITFOR DELAY '0:00';
   SET @loop_counter = @loop_counter + 1;
END LOOP;
SET @msec = DATEDIFF ( MILLISECOND, @t, CURRENT TIMESTAMP );
SELECT @msec AS "100 WAITFOR";
END;

BEGIN
DECLARE @t TIMESTAMP 
DECLARE @loop_counter INTEGER 
DECLARE @msec INTEGER 
SET @t = CURRENT TIMESTAMP
SET @loop_counter = 1
WHILE @loop_counter <= 1000000 BEGIN
   GOTO a 
   a:
   SET @loop_counter = @loop_counter + 1 
END
SET @msec = DATEDIFF ( MILLISECOND, @t, CURRENT TIMESTAMP ) 
SELECT @msec AS "1000000 GOTO" 
END

1000000 empty loop 
------------------ 
               532 
Execution time: 0.54 seconds


100 WAITFOR 
----------- 
      10989 
Execution time: 10.991 seconds


1000000 GOTO 
------------ 
         830 
Execution time: 0.836 seconds

What is the quickest statement?

Accepted Solutions (0)

Answers (4)

Answers (4)

Re: How to trace authorization error for a specifi...

cumulative amounts

Re: Vendor notes and Attachments data migration fr...

Re: Store attachments from Standard Fiori apps in ...

Re: Fiori attachments storage into OpenText archiv...