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