Subject Re: [IBO] ibo_cursor
Author Helen Borrie
At 09:34 PM 06-09-01 -0600, you wrote:
>Hi Helen,
>Question, in your opinion would a server procedure with an
>IF (EXISTS .... the way be of the simular speed as no procedure,
>but just NOT QUERY.IsEmpty of the type 'SELECT FIRST 1 FROM' ?

I don't exactly have an opinion - I'd want to test both EXISTS() AND SELECT FIRST 1 against my likely datasets and compare results! The EXISTS() approach I described returns a single datum as a result set; the other can return a row which would be preferable if a row was wanted, rather than just an answer.

SELECT FIRST... is available only in Firebird 1.x at present.

> From source to server would probably be about the same, but I
>wonder about time execution in the server.

That depends on several things, including the size of the "catchment" and the existence of usable indexes. As I understand it, EXISTS() doesn't form a rowset on the server, but merely populates a variable - Claudio could tell us exactly what happens in the code. Any other SELECT is going to form a rowset (and maybe you WANT that rowset if it exists); but, for example,

WHERE {criteria for a single row}

is going to eat as much server capacity as it takes to find the row and eliminate non-candidates; and either a single-column rowset or a low-byte return value is going to be smaller than a network packet. Unless the table is huge, my guess is that we might not see a significant difference in execution time.

QUERY.IsEmpty isn't optimal because of the unnecessary client-side overhead of buffering a bi-directional dataset. Even if you want to get that singleton dataset if it exists, you'd still want an ib_cursor, rather than an ib_query, and test it for EOF.

So, my opinion is - test and compare.


All for Open and Open for All
InterBase Developer Initiative ยท