>Nadya,
>
>Does sometemptable have an index built on the join field? I don't see the temp table in the query. Also, maybe you gave us psuedo-syntax for the zipcode join but the table where zipcode is kept isn't apparent. The first choice is to optimize the query by indexing the "btccode" field in the temp table.
>
>HTH
>Bill

Bill,

The temp table is indeed indexed on btcCode. The query with 110 codes was executing for 15 minutes. In my point of view, this speed is unacceptable, though my manager was quite happy about it, because originally it took 4h. (It didn't have zipcodes restrictions at all, so the result was very huge).

That's why I was thinking, may be 5 inlists would be better than one additional join. I don't have time to play with it right now, so I'm wondering, what would be yours and others suggestions...

>
>>Now, while you're in, here is a question, which I'd like to hear your expert advice.
>>
>>For one-time jobs we use Standard type of query. The query may look like this:
>>

>>set talk on
>>set escape on
>>sys(3054,11)
>>OPEN DATA l:\REDP\DBC\CT\CT
>>SELECT tranmstr.*,sitemstr.*,;
>>propmstr.*,bldgmstr.*,;
>>MiscLndr.LName,Apn,MapNum,BlockNum,LotNum, ;
>>UnitNum,WardNum,ParcelID  ;
>>FROM TranMstr INNER JOIN SiteMstr on TranMstr.PropID=SiteMstr.PropID ;
>>INNER JOIN PropMstr on TranMstr.PropID=PropMstr.PropID ;
>>LEFT JOIN BldgMstr on TranMstr.PropID=BldgMstr.PropID ;
>>LEFT JOIN MiscLndr on TranMstr.TranID=MiscLndr.TranID ;
>>LEFT JOIN AssrMstr on TranMstr.PropID=AssrMstr.PropID ;
>>where  Prefcode = "P"  AND ZIPCODE='06793' ;
>>and InactvFl#"A" and COMPLEVL="P" and TranMstr.SellName="Y"   ;
>>INTO TABLE ..\..\work\query\qryU0ZA34J

>>
>>Now, I need to have all these joins (BTW, we discussed, that they probably can be modified a little bit) and if the user selects long list of zipcodes (I currently set max number to be 72), I add another join, like:
>>

>>SELECT tranmstr.*,sitemstr.*,;
>>propmstr.*,bldgmstr.*,;
>>MiscLndr.LName,Apn,MapNum,BlockNum,LotNum, ;
>>UnitNum,WardNum,ParcelID  ;
>>FROM TranMstr INNER JOIN SiteMstr on TranMstr.PropID=SiteMstr.PropID ;
>>INNER JOIN PropMstr on TranMstr.PropID=PropMstr.PropID ;
>>LEFT JOIN BldgMstr on TranMstr.PropID=BldgMstr.PropID ;
>>LEFT JOIN MiscLndr on TranMstr.TranID=MiscLndr.TranID ;
>>LEFT JOIN AssrMstr on TranMstr.PropID=AssrMstr.PropID ;
>>inner join sometemptable on ZIPCODE=sometemptable.btcCode ;
>>where  Prefcode = "P"   ;
>>and InactvFl#"A" and COMPLEVL="P" and TranMstr.SellName="Y"   ;
>>INTO TABLE ..\..\work\query\qryU0ZA34J

>>
>>This query seems to execute too long. (I've tested the first query with one zipcode and it was almost instantanious).
>>
>>What do you think? Increase max number, so it would be, say, 5 inlist commands, or use it in where condition like zipcode in (select ...) [How much subqueiries are allowed per one Query?]
>>
>>Thanks in advance.