Subject | RE: [firebird-support] Why "SELECT FIRST 1 ..." and "EXISTS ( SELECT * FROM ... )" are so slow in a big database? |
---|---|

Author | Antti Nivala |

Post date | 2006-07-14T23:15:29Z |

Paul,

I'm sorry but I don't understand what you mean. Are you saying that adding an ORDER BY clause would make the query run faster? I don't understand how, and I did not get it any faster by adding "ORDER BY PV ". It still takes 400 ms to return just one record. Can you give me more details if the ORDER BY clause can really help speed up the query.

My point is that I want to make a QUICK test for existence of any records where PV = 2.

When I use "NOT EXISTS ( SELECT * FROM DV_PV WHERE PV = 2 )" I don't understand why it takes much longer for values with duplicates and almost nothing for values with a small number of duplicates. It appears to me that the millions of duplicate values for the "PV = 2" key in the index make the EXISTS check so slow, but I do not understand why. I assumed FB would just visit the index and see that there are values for "PV = 2" and that's it. Why would it read all the duplicates from the index before concluding that at least one value exists?

The same slowness appears in the "SELECT FIRST 1 * FROM DV_PV WHERE PV = 2" example.

How can I make a quick test for existence in this case?

Antti

I'm sorry but I don't understand what you mean. Are you saying that adding an ORDER BY clause would make the query run faster? I don't understand how, and I did not get it any faster by adding "ORDER BY PV ". It still takes 400 ms to return just one record. Can you give me more details if the ORDER BY clause can really help speed up the query.

My point is that I want to make a QUICK test for existence of any records where PV = 2.

When I use "NOT EXISTS ( SELECT * FROM DV_PV WHERE PV = 2 )" I don't understand why it takes much longer for values with duplicates and almost nothing for values with a small number of duplicates. It appears to me that the millions of duplicate values for the "PV = 2" key in the index make the EXISTS check so slow, but I do not understand why. I assumed FB would just visit the index and see that there are values for "PV = 2" and that's it. Why would it read all the duplicates from the index before concluding that at least one value exists?

The same slowness appears in the "SELECT FIRST 1 * FROM DV_PV WHERE PV = 2" example.

How can I make a quick test for existence in this case?

Antti