hanging query for 2.5 [CORE5412] #5685
Comments
Modified by: Ray Holme (rholme)environment: Firebird Linux 2.5.2 => Firebird Linux - LI-V2.5.2.26539 |
|
Commented by: @dyemanov What is the point in using IN for a singleton subquery? It should rather be INVOICE_ID = (select max(INVOICE_ID) ...). |
|
Commented by: Ray Holme (rholme) You are quite right, of course. But the query is valid and was taken from another of mine which returns a large number of rows. Just because you are right does not mean that the query is wrong and I am sure someone else will do it. Interesting to note the the "select min()" version works fine; just the "select max()" does not. You can close this if you like or wait till someone else makes a mistake like this. Thanks Dimitri. For the record, Paul has a copy of the minimal DB and queries. |
|
Commented by: Sean Leyne (seanleyne) Ray, 1- MAX() requires a descending index to perform quickly 2- while IN () is valid operand, the fact that you are using incorrectly (your result set is always singleton) can contribute to poor performance 3- IN () is also known to be a poor choice -- EXISTS() will always perform better in the general case |
Modified by: Sean Leyne (seanleyne)status: Open [ 1 ] => Resolved [ 5 ] resolution: Won't Fix [ 2 ] |
|
Commented by: @dyemanov We already have tickets to improve IN performance (avoid nested sub-query execution in some cases), so I agree this ticket can be closed. |
Modified by: @pcisarstatus: Resolved [ 5 ] => Closed [ 6 ] |
Submitted by: Ray Holme (rholme)
I was running a query looking for the range of a problem.
select invoice_id, datetime from invoices
where invoice_id in (select min(invoice_id) from invoice_lines where srv_order is null)
or invoice_id in (select max(invoice_id) from invoice_lines where srv_order is null);
After returning the first row (min) the query hangs.
So I tried:
select invoice_id, datetime from invoices
where invoice_id in (select max(invoice_id) from invoice_lines where srv_order is null);
This too hangs and never retrurns (well I did not wait more than 1 minute).
But running this query returns results fine immediately.
select max(invoice_id) from invoice_lines where srv_order is null;
and the following amended query works fine. ( supplying the min and max values) explicitly)
select invoice_id, datetime from invoices where invoice_id in (5, 11284);
I could create a version of the DB for you.
I do NOT believe you will need this as the problem is I(MHO) related to the select max hanging in a sub-query.
Note that this is an active DB (open by an inactive webapp - this should not matter, but ....)
The text was updated successfully, but these errors were encountered: