Another (probably dumb) question - IDS SQL results cache

I'm trying a couple of performance tests on simple queries; for example, setting/unsetting IFX_FLAT_UCSQ. So, I set up an SQL and run something like: timex dbaccess my_db query1 and get a run time of, say 4.5 seconds. Then I set IFX_FLAT_UCSQ and get 1.17 seconds, i.e. a good result. To confirm, I unset IFX_FLAT_UCSQ and run the query again; and get 1.18 seconds. Doh! The query hasn't changed so the result set is still in cache from the previous run. In essence, this means that I can't prove it was the setting/unsetting of IFX_FLAT_UCSQ which caused the improved query time. So my question is, is there a way to clear the cache or do I have to restart the server in between tests? (Bearing in mind that I'm probably oversimplifying and it's 10-1 on that I've missed something simple anyway and it's Monday so I've got my "Mr. Dumb" hat on.)

Malc You could try a selection that completely overwrites you buffers with unrelated data! but the quick and simple way is to bounce the engine. Keith On 16 Oct 2006 07:22:49 -0700, malc_p@btinternet.com <malc_p@btinternet.com> wrote: > I'm trying a couple of performance tests on simple queries; for > example, setting/unsetting IFX_FLAT_UCSQ. > So, I set up an SQL and run something like: > > timex dbaccess my_db query1 > > and get a run time of, say 4.5 seconds. Then I set IFX_FLAT_UCSQ and > get 1.17 seconds, i.e. a good result. > To confirm, I unset IFX_FLAT_UCSQ and run the query again; and get 1.18 > seconds. > Doh! The query hasn't changed so the result set is still in cache from > the previous run. > In essence, this means that I can't prove it was the setting/unsetting > of IFX_FLAT_UCSQ which caused the improved query time. > > So my question is, is there a way to clear the cache or do I have to > restart the server in between tests? > > (Bearing in mind that I'm probably oversimplifying and it's 10-1 on > that I've missed something simple anyway and it's Monday so I've got my > "Mr. Dumb" hat on.) > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

Your test was good and it showed that you didn't get a boost with IFX_FLAT_UCSQ. Try it this way. With IFX_FLAT_UCSQ unset, run the original query twice throw away the first runtime. Then set IFX_FLAT_UCSQ and run the query again. You are now comparing cached to cached which is essentially what you did by running the cofirmation step. malc_p@btinternet.com wrote: > I'm trying a couple of performance tests on simple queries; for > example, setting/unsetting IFX_FLAT_UCSQ. > So, I set up an SQL and run something like: > > timex dbaccess my_db query1 > > and get a run time of, say 4.5 seconds. Then I set IFX_FLAT_UCSQ and > get 1.17 seconds, i.e. a good result. > To confirm, I unset IFX_FLAT_UCSQ and run the query again; and get 1.18 > seconds. > Doh! The query hasn't changed so the result set is still in cache from > the previous run. > In essence, this means that I can't prove it was the setting/unsetting > of IFX_FLAT_UCSQ which caused the improved query time. > > So my question is, is there a way to clear the cache or do I have to > restart the server in between tests? > > (Bearing in mind that I'm probably oversimplifying and it's 10-1 on > that I've missed something simple anyway and it's Monday so I've got my > "Mr. Dumb" hat on.)