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 Thanks Breck for running the timing. I'm still waiting to hear from Martin to know what he was/is really wanting to know and/or do? E.g. is he just trying to do a client side round trip test? ... or is it something else? FWIW: "goto a; a:" is not a single statement ;-)
(05 May '14, 22:27)
Mark Culp
Replies hidden
You are correct it is a client side round trip test and furthermore I am just curious, often the obvious solutions don't need to be the optimal ones.
(06 May '14, 02:44)
Martin
So we can assume that a code block - as Breck has tested - is not what you are looking for? Do you need a result set?
(06 May '14, 03:20)
Volker Barth
no a result set is not needed
(06 May '14, 07:06)
Martin
There are a LOT of things wrong with goto, none of which have anything to do with politically correct "goto considered harmful" crap from yesteryear. You point out one flaw... but wait! there's more! The goto has two characteristic errors: first, spelling it go to, and second, writing the label as :a Plus, it doesn't work inside a timing loop unless you code it all as Transact SQL... didya notice that? gosh, Transact SQL sucks :) Plus, chances are it won't work from the client side... but that is requirement drift [cough] change :)
(06 May '14, 08:58)
Breck Carter
The code block is not part of the solution, just a way to test the performance. Having said that, goto doesn't qualify (see above), but it sure is fast... surprised me :)
(06 May '14, 09:01)
Breck Carter
1
Please confirm you want to measure client-server round-trip latency, excluding work done inside the engine.
(06 May '14, 09:07)
Breck Carter
Thanks for the pointer, myself had certainly not noticed that.
(06 May '14, 09:16)
Volker Barth
|
waitfor delay '0:00'; 2
In theory, it runs in zero time... in practice, not so much :) (0.1 second per execution, approximately)
(05 May '14, 20:56)
Breck Carter
|
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; So Ian has just suggested a performance improvement for your FH3 heartbeat query, right?
(06 May '14, 09:36)
Volker Barth
Replies hidden
No. The performance problem discussed above is not caused by the heartbeat query. Rather, anomalies in the heartbeat and sample query times are indicators of a performance problem elsewhere in Foxhound; i.e., Foxhound cannot handle the load of data coming from the target database(s). This can happen when (for example) you try to monitor 100 busy target databases from a single copy of Foxhound that's running on a feeble desktop (an interesting "smoke test" in itself :) FWIW once upon a time Foxhound used the dbping utility but it was dumped because it had a lot of [cough] issues. It is working better now, and it is in the Foxhound pipeline to be resurrected... not for replacing the heartbeat query for an existing connection, but for measuring the time to establish a new connection to the target database... and producing an Alert when new connections are being refused (a far more important purpose). Foxhound tries to monitor availability and performance from a client application point of view. There are a million tools for studying and measuring raw network performance, and Foxhound will never be one of them. Having said all that, Ian's mention of the undocumented DBlib call is worth investigating, even though Foxhound doesn't currently use DBlib.
(06 May '14, 10:20)
Breck Carter
2
Thanks for the explanation - and I have to confess my small hint was not meant that seriously - I just thought that Ian's suggestions might perform better than "SELECT 1":) Besides that, it's great if you give us some insight in the design decisions behind Foxhound - that's always appreciated here, methinks...
(06 May '14, 11:12)
Volker Barth
1
Well, I was going to suggest that SELECT 0 runs much faster because the data's smaller... :)
(06 May '14, 14:52)
Breck Carter
|
At least this might be easier to answer as "What is the slowest statement?" :)
I could try to answer this... but what are you really trying to do?
...particularly one would need to know if the statement should generate a result set or not (e.g. AFAIK a COMMIT/ROLLBACK is a NO-OP if it immediately follows a previous COMMIT/ROLLBACK under certain conditions, so that should be a "cheap operation"...)
Wouldn't this require 2 roundtrips to the db server one for begin transaction and one for commit?
Not in client-side autocommit mode... - but the NO-OP could only occur for the the 2nd and further requests, that's correct.
FWIW SELECT 1 is semantically equivalent to SELECT 1 FROM dummy.