I am executing one sql statement like following

SELECT
{+ ordered,avoid_full(locations),avoid_full(cg_ref_co des),avoid_full(shipments)}

loc_zone, cgr.rv_meaning, loc_id, shp.shp_bl, '' cnt_c_id
FROM
cg_ref_codes cgr,
locations loc,
shipments shp,
shipments shp1
WHERE (loc_closed_bl = shp.shp_bl)
OR (loc_shp_bl = shp1.shp_bl)
AND NOT EXISTS
(select 'X' from containers where cnt_loc_id = loc_id)
AND loc_class = nvl('P', loc_class)
AND loc_type = nvl('R', loc_type)
AND cgr.rv_low_value = loc_zone
AND cgr.rv_domain = 'ZONES'


This query takes 1 sec to execute ,When I am saying this query
into temp sss with no log
It never comes out . I am new to informix ,Can u pl. tell me all the possible reason why it is happening. I am facing same problem with most of quries while I am trying to replace the order by quries with temp .
Samahit

When you do "and not exists" and other things like that in a query, it will quite naturally take longer. But for a DBMS to take one-second to do anything is beginning to get unusual.

There are a couple of things about your query that you really need to watch out for . . . . . . to avoid if you can help it. . .

(1) You don't want to create a situation where the DBMS might be forced to conduct a separate (nested...) search of one table (e.g. containers) for every record or even every group of records in another. Suddenly a 1,000 record table might generate 1,000 separate queries and we all know what 1,000 x 1,000 turns into: a really big number!

(2) You really don't want to put a whole bunch of tables into a single query all at once, again if you can help it. You can run into that "one thousand times a thousand" problem once again. Now, DBMSes are generally very, very smart about inventing solutions to the most complex of queries, but you are always going to be better off to the extent that you can simplify them. The computer has no choice but to say, "Yes, master." Be nice to your servant.

Now, the only way to really be sure is to look at the execution-plan of the query you're proposing. Often a slightly-rewritten query will be considerably faster (or slower).

It may just be a personal peeve of mine, but I find myself preferring to write a sequence of queries, running one right after the other with temporary-tables between, than to write {yet another pun-ism} "Cucamonga queries." If the query-plan shows that the DBMS is basically going to do it this way, then goody for it, let it do the work, but... sometimes it works better to break the problem down.

Over the years I acquired somewhat of a useful reputation for shaving hours off a company's reporting-time by some simple observations like these. For example, one client spent about five hours running sales-reports. (Ever notice they're always sales reports?) Looking at the very-complex queries which drove each one, I observed that each one was very repetitive. I rewrote the sequence to generate intermediate tables once, run simpler queries against them, and then "drop." Five hours reduced to twenty-five minutes. Not bad. It seems that reports (and especially, reports for salesmen!) superficially require the most complex queries of all. At least, if you go on the assumption that "you can only use one query;" usually not the case.

Oops... I ramble. Again. HTH.

Agree sundialsvcs

Anyway thanks for reply ..

But in my case I cannot rewrite the query.At the best I can add some hints ,create some more indexes etc.
I am surprise why into temp is taking time. Is is just because inserting the rows.
Do I need to rebuild the temp db space.or some setting I hv to check ..

Samahit