Hi Svein,thanks a lot for your help!
I also need to add one condition to my query,but I don't know how to do it using your script,so my new query:

 SELECT a.date,a.id FROM mytable1 a WHERE a.year=2013
 AND (a.month=1 OR  (A.NUMB
                      IN (SELECT DISTINCT X.NUMB FROM PR_GET_SOLD(2013,1) X ) ))

so adding this I think I can't use join as you suggested in your script

thanks !


________________________________
From: Svein Erling Tysvær <svein.erling.tysvaer@...>
To: "firebird-support@yahoogroups.com" <firebird-support@yahoogroups.com>
Sent: Tuesday, February 19, 2013 6:41 AM
Subject: Re: [firebird-support] Slow query using stored procedure


 

>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 ( ) 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


[Non-text portions of this message have been removed]