>Just for a fun can you try WHERE instead of JOIN?
>BTW, How many records are in the temp table and how big result set do you expect?

I'll try at home with the test table. The temp table has 110 records. The main table has 5mln records. I think, the resulting set would be huge, may be 800000 recs. This is just a plain test, the live query uses 7 joins to tables, each of them has ~3-5mln. recs... :(

>>>Nadya,
>>>Did you try SET DELETED OFF or add a tag ON DELETED() just to see if it makes any difference. Do the fields match in length? You don't have any expressions in the key tags?
>>
>>Set deleted off doesn't make much difference, both tables have the same length of ZipCode field (Char 5). The tag expression is index on btcCode tag btcCode (simple).
>>
>>If I run query just from temp table, btcCode index is used. However, if I use this table in join condition, I see none as optimization for this table and full optimization for SiteMstr. SiteMstr has ~5mln. records, the resulting set should be huge, but I never run it to the end, because I don't have enough patience to wait more than 1 min. on query execution...
>>
>>>>Daniel,
>>>>
>>>>There are 110 items in this table. My main question is : why it shows none for this table optimization?
>>>>
>>>>>Nadya,
>>>>>How many matching records are there in btcZipcode? With other words, how many records do you have in the result set? If you have only a few zipCodes in btcZipcode, you could build a where clause using IN() or INLIST() instead of joining the two tables. For example if btcZipcode contains the zipCodes "A", "B", "F" and "X", you could run the following query:
>>>>>

>>>>>SELECT sitemstr.* ;
>>>>>       from o:\redp\dbc\ma\sitemstr ;
>>>>> WHERE sitemstr.zipcode IN ("A", "B", "F" and "X") ;
>>>>>INTO TABLE ..\..\work\query\qryU0ZA34J

>>>>>
>>>>>I have used this technique to increase performance, but it only works if you have less than 25 elements.
>>>>>
>>>>>Another thing to try is to SET DELETED OFF and see if this makes any difference.
>>>>>
>>>>>HTH
>>>>>>Hi everybody,
>>>>>>
>>>>>>I'm running this simple test:
>>>>>>
>>>>>>

>>>>>>SELECT sitemstr.* ;
>>>>>>       from o:\redp\dbc\ma\sitemstr ;
>>>>>>       inner join c:\wistemp\btc_0IU0ZA4P5 btcZipcode on sitemstr.zipcode = btcZipCode.btcCode ;
>>>>>>        INTO TABLE ..\..\work\query\qryU0ZA34J

>>>>>>
>>>>>>Temp table has an index on btcCode and SiteMstr has an index on ZipCode. sys(3055,11) shows, that optimization level for SiteMstr is full and for temp table is none. The query executes very long time (SiteMstr has ~3mln. records). The original query (not the test one) has 7 joins already and it takes forever. Is it here a way to optimize it?
>>>>>>
>>>>>>I've tried to change select ... where ZipCode in (select btcCode from ) form and it also shows none optimization for temp table, though seems to execute a little bit faster (still very slow).