on 2014 May 05 10:30 AM
Which statement will need the minimum time to execute? Sure Select 1 will be a candidate, but is this really the fastest?
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:
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.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.set @int_var = 0
, assuming a create variable @int_var int
was already performed.I hope this helps.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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...
Well, I was going to suggest that SELECT 0 runs much faster because the data's smaller... 🙂
waitfor delay '0:00';
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
In theory, it runs in zero time... in practice, not so much 🙂
(0.1 second per execution, approximately)
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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 🙂
Please confirm you want to measure client-server round-trip latency, excluding work done inside the engine.
User | Count |
---|---|
68 | |
10 | |
10 | |
7 | |
6 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.