>SELECT DISTINCT X.NUMB FROM PR_GET_SOLD(2013,1) X ) > >is executed in 0.219s with result of 77 rows :
Here you're asking Firebird to execute the SP once. >also this query : > >SELECT a.date,a.id FROM mytable1 a WHERE a.year=2013 > >is executed in 0.235s with result of 19593 rows : Again, you're asking Firebird to execute the query once. >Put putting together : > > SELECT a.date,a.id FROM mytable1 a WHERE a.year=2013 > AND ( A.NUMB > IN (SELECT DISTINCT X.NUMB FROM PR_GET_SOLD(2013,1) X ) ) > >it takes >2 minutes and returns 1693 rows : Of course, you're asking Firebird to execute the query only once, but for each that has a.year = 2013 you're asking Firebird to execute the stored procedure, which would mean many iterations of the stored procedure. Firebird doesn't optimize such a statement. Although theoretically possible when the stored procedure is purely selectable, I would never expect such optimization of lazy coding. IN (<subselect>) can be very slow and adding DISTINCT doesn't make it quicker! Rather, try something like: with ExecuteMeOnce as (SELECT DISTINCT NUMB FROM PR_GET_SOLD(2013,1)) SELECT a.date,a.id FROM mytable1 a JOIN ExecuteMeOnce X on A.NUMB = X.NUMB WHERE a.year=2013 Now, I haven't tested and don't know whether this will be quick, but think it is likely to execute in less than a second. HTH, Set
