Thanks a lot for your help,your script seems logically but execution time is almost the same

Executing statement...
66903234 fetches, 60972 marks, 3322 reads, 503 writes.

19593 inserts, 0 updates, 0 deletes, 33170949 index, 99576 seq.
Delta memory: 2127804 bytes.
GN_DEBUG: 19593 inserts. 
Total execution time: 0:01:59 (hh:mm:ss)
Script execution finished.


,I debug and the execution of the procedure is not one time,now it is executed for 19593 times,just as many records has mytable1 (a)
I use Firebird 2.5.2 SS on Xp Sp3 x86

Thanks


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


 

>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

Well, the added requirement makes the query slightly different, John, but changing it to a LEFT JOIN is simple:

with ExecuteMeOnce as
(SELECT DISTINCT numb FROM PR_GET_SOLD(2013,1))
SELECT a.date, a.id
FROM mytable1 a
LEFT JOIN ExecuteMeOnce x ON a.numb = x.numb
WHERE a.year = 2013
AND (a.month = 1
OR x.numb IS NOT NULL)

I'm curious to learn whether this does improve the speed or if it still takes two minutes, so please report back.

HTH,
Set



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