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.tysv...@kreftregisteret.no> 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]
