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's gravatar image

accept rate: 14%


At least this might be easier to answer as "What is the slowest statement?" :)

(05 May '14, 09:45) Volker Barth

I could try to answer this... but what are you really trying to do?

(05 May '14, 09:49) Mark Culp
Replies hidden

...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"...)

(05 May '14, 10:01) Volker Barth

Wouldn't this require 2 roundtrips to the db server one for begin transaction and one for commit?

(05 May '14, 11:23) Martin

Not in client-side autocommit mode... - but the NO-OP could only occur for the the 2nd and further requests, that's correct.

(05 May '14, 11:33) Volker Barth

FWIW SELECT 1 is semantically equivalent to SELECT 1 FROM dummy.

(05 May '14, 21:00) Breck Carter
showing 3 of 6 show all flat view

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.

permanent link

answered 06 May '14, 09:16

Ian%20McHardy's gravatar image

Ian McHardy
accept rate: 38%

edited 06 May '14, 09:23


GOTO wins by a WIDE margin :)

DECLARE @loop_counter INTEGER;
SET @loop_counter = 1;
WHILE @loop_counter <= 1000000 LOOP
   SET @loop_counter = @loop_counter + 1;
SELECT @msec AS "1000000 empty loop";

DECLARE @loop_counter INTEGER;
SET @loop_counter = 1;
WHILE @loop_counter <= 100 LOOP
   WAITFOR DELAY '0:00';
   SET @loop_counter = @loop_counter + 1;
SELECT @msec AS "100 WAITFOR";

DECLARE @loop_counter INTEGER 
SET @loop_counter = 1
WHILE @loop_counter <= 1000000 BEGIN
   GOTO a 
   SET @loop_counter = @loop_counter + 1 
SELECT @msec AS "1000000 GOTO" 

1000000 empty loop 
Execution time: 0.54 seconds

Execution time: 10.991 seconds

1000000 GOTO 
Execution time: 0.836 seconds
permanent link

answered 05 May '14, 11:22

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

edited 05 May '14, 20:53

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

Please confirm you want to measure client-server round-trip latency, excluding work done inside the engine.

(06 May '14, 09:07) Breck Carter

it doesn't work inside a timing loop unless you code it all as Transact SQL... didya notice that

Thanks for the pointer, myself had certainly not noticed that.
FWIW: I guess I have never ever used GOTO in real SQL code:)

(06 May '14, 09:16) Volker Barth
showing 1 of 8 show all flat view

waitfor delay '0:00';

permanent link

answered 05 May '14, 11:24

Mark%20Culp's gravatar image

Mark Culp
accept rate: 40%


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)...

alt text

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;
permanent link

answered 06 May '14, 09:16

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

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

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

Well, I was going to suggest that SELECT 0 runs much faster because the data's smaller... :)

alt text

(06 May '14, 14:52) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 05 May '14, 09:30

question was seen: 956 times

last updated: 06 May '14, 14:52