Which statement will need the minimum time to execute? Sure Select 1 will be a candidate, but is this really the fastest?
asked 05 May '14, 09:30
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:
I hope this helps.
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
waitfor delay '0:00';
answered 05 May '14, 11:24
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.:
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;
answered 06 May '14, 09:16