I have a SQL query going to an Informix DB, which initially takes up to three minutes to complete, regardless of the size of the data returned.

If I call the query again, even with completely different parameters, within the next 10 minutes, it completes within seconds.

It feels like the SQL is being compiled and cached, but I don't understand how one relatively simple query should take three minutes to compile and then only a few seconds to execute:

SELECT *
FROM internet:ref_referrals inner join internet:crvet_application on internet:ref_referrals.ref_reference=internet:crve t_application.referral_no
WHERE date(ref_enter_date) between '2007/01/01' and '2007/01/30'

The problem does not seem to appear on any other queries. ANY ideas would be greatly appreciated.

Yes, you'r right. First time Informix reads the data from disk, other times
mostly from buffers.
And why it takes three minutes to compile depends on many factors: fi: how the optimizer process the query(look at the explain of the query), how the table looks like on the disk(how many extents, fragmented or not...), how much data the query returns...and so on.

I don't know your table layout but here are a few common suggestions:

You could speed up your initial query by setting suitable indices on the referencing columns (ref_reference, referral_no) and on the conditional columns (ref_enter_date) helping the database not to go through the whole tables for your results.

Try to avoid the use of functions where it is not neccessary. Maybe you could use the following clause to speed up:
"where ref_enter_date between '2007-01-01 00:00:00' and '2007-01-30 23:59:59'"
(depending on your column definition you have to add milliseconds or leave out the seconds or whatever)