New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
SUBSELECT extrem slow with FB2.0 [CORE1969] #2407
Comments
Commented by: @dyemanov It would be helpful if you would provide full plans for all the queries involved. Also, make sure that your index statistics is updated. |
Commented by: Andreas Steibl (snoopy_spy) did you mean this? It seems, that it only takes long time, if the table in the subselect has many records (KOSTEN 684000 records) the curiouse thing is, thate the two queries for each alone, are very fast, but if you combine them in a sub select, the result is awefull slow |
Commented by: @dyemanov What is the plan on FB 1.5? |
Commented by: Mercea Paul (m24paul) Try SELECT * FROM PERSONAL P WHERE EXISTS (SELECT 1 FROM KOSTEN K WHERE K.PARENTID = 46 and http://K.PARENTID=P.ID) Or try a join SELECT P.* But in this case is similar to SELECT * FROM PERSONAL WHERE ID=46; |
Commented by: Andreas Steibl (snoopy_spy) I allready know the workaround with the join, the problem is, that i need these subselect for Deleting too, and there i can't use a join. And this SQL Statemant is as slow as the statemant i use on FB1.5 the plan is |
Commented by: Andreas Steibl (snoopy_spy) Should i prepare a test Database for this issue? I think we need a table with many many records, (in my case it was over 600000 records in the table) |
Commented by: @dyemanov Yes, please prepare a test case and attach it here. |
Commented by: Andreas Steibl (snoopy_spy) Backup File with Test Data I use following Statement for testing SELECT * |
Modified by: Andreas Steibl (snoopy_spy)Attachment: test3.exe [ 10952 ] |
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Commented by: @dyemanov I see that: a) This is a completely different query than you were talking here before I suggest you to point us to a real problem that can be seen, otherwise this ticket will be rejected. |
Commented by: Andreas Steibl (snoopy_spy) Oh really shame on me. In the DB version of the 1.5 i had an index on OTHER_ID and therefore it was so fast Why does the index matter, which is only used in the fields of the SELECT? I know, that it is really necessary for Fields in WHERE and so. Sorry again for this issue and confusing |
Commented by: @dyemanov The IN predicate is internally (by the engine) converted into something behaving like a correlated EXISTS predicate, so an indexed relationship on OTHER_ID becomes important. By the SQL standard, "IN" means the same as "= ANY" and the equality comparison can be injected into the subquery in order to benefit from available indices on the linkage field. |
Modified by: @pcisarstatus: Resolved [ 5 ] => Closed [ 6 ] |
Submitted by: Andreas Steibl (snoopy_spy)
Attachments:
test3.exe
I have a simple SQL Statemant for testing
SELECT * FROM PERSONAL WHERE ID IN (SELECT PARENTID FROM KOSTEN WHERE PARENTID = 46)
this SQL Statemant needs about 5 Secounds (FB >= 2.0)
in FB 1.5 it takes only some ms.
Some other SUB Selectes needs in FB >= 2.0 about minutes and in FB .5 only secounds!!!!!
if i use following code
SELECT * FROM PERSONAL WHERE ID IN (46)
or
SELECT PARENTID FROM KOSTEN WHERE PARENTID = 46
it take only some ms
It seems, that no INDEX is used for the first table (PLAN (PERSONAL NATURAL)) if i try to use an Index in the PLAN Statemant, i get an error : Index PERSONAL_ID cannot be used in the specified plan
The text was updated successfully, but these errors were encountered: