Is btc_07L0SD70N the actual temporary file name created when you create the temporary cursor? Is it a real dbf? It looks like you are getting the actual file name by using DBF() then using that in the SELECT instead of just using the cursor name. If this is the temp file name created, you should use the name of the cursor instead of the actual DBF name.

>Hi Mark,
>
> I tried inner select and found, that it's much slower than inner join.
>IOW, I tried these three syntaxes:
>

>set safe off
>lnsec=seconds()
>set talk on
>SELECT town FROM ct!sitemstr st, c:\windows\temp\btc_07L0SD70N temp ;
>where prefcode = "P" and town=code into table gg
>wait window 'Elapsed time is '+transform(seconds()-lnSec)
>
>lnsec=seconds()
>set talk on
>SELECT town FROM ct!sitemstr ;
>inner join c:\windows\temp\btc_07L0SD70N ;
>on town=code where prefcode = "P"  into table ggg
>wait window 'Elapsed time is '+transform(seconds()-lnSec)
>
>lnsec=seconds()
>set talk on
>SELECT town FROM ct!sitemstr where town in (select code from c:\windows\temp\btc_07L0SD70N) ;
>and prefcode = "P"  into table ggg
>wait window 'Elapsed time is '+transform(seconds()-lnSec)

>
>First select took 0.3 sec., second 0.25 and the third took 347 sec.
>Table has 4151890 records, e.g more than 4 million. Result is 0 records (which is really strange).
>
>For one town it works fine.
>
>
>>Is the table really 3 million records? Where is the table? How many records are returned? What kind of performance if you just select the records for only 1 town [i.e., WHERE TOWN = "My Town"]?
>>
>>I would think the fastest query would be your first example where you have the sub-query. The more tables you join the worse the performance.
>>
>>>Hi Mark,
>>>
>>>No, I'm not calling function in where clause, I showed the method code, because it's how I generate wherexp. The final SQL statement would be look like one, I showed. But for some reason it doesn't work.
>>>
>>>Example:
>>> select * from ... joindcond inner join temptable ttt on sitemstr.town=ttt.btcCode where ...
>>>
>>>Number of joins less than 6.
>>>
>>>I may test without deleted tag, but I think, the problem is something different.
>>>
>>>I'm just stuck :(
>>>
>>>Thank for your help.
>>>
>>>BTW, forgot to mention, that SiteMstr has index on town and temp table has index on BtcCode.
>>>
>>>>If you are calling a function in the WHERE clause, that is the source of the performance problem. That function gets called for EVERY record. Either that, or I am confused as to why the WhereExp class code is included. Also, do you mean 3 Million records? What is the performance if you remove your deleted tag but still leave DELETED ON?
>>>>
>>>>>Hi everyone,
>>>>>
>>>>>Last few days I'm struggling with one of my application. This application is used to specify user's criteria and it should return where expression for SQL.
>>>>>
>>>>>I'd like to give the users of this application an ability to select unlimited number of codes from multiselect grid.
>>>>>Firstly I put selected codes in a temprorary table.
>>>>>My first attempt was to create syntax like this:
>>>>>select * from ...
>>>>>where ... and town in (select BtcCode from temptable).
>>>>>
>>>>>For some reason, this query takes too much time even on a single table, e.g.
>>>>>select * from sitemstr where town in (select BtcCode from temptable)
>>>>>The speed is unacceptable (~1,5 min. on ~3mln. records in SiteMstr).
>>>>>BTW, SiteMstr has index on deleted() and set deleted is on.
>>>>>
>>>>>My second attempt was to use inner join instead of second inner select. For some reason, this code failed:
>>>>>
>>>>>SELECT * FROM tranmstr left JOIN misclndr on tranmstr.tranid = misclndr.tranid inner JOIN propmstr on tranmstr.propid = propmstr.propid inner JOIN sitemstr on tranmstr.propid = sitemstr.propid left JOIN bldgmstr on tranmstr.propid = bldgmstr.propid INNER JOIN c:\windows\temp\btc_07L0WCO5Z btcTown ON sitemstr.town=btcTown.BtcCode where (prefcode = "P" and SOURCE="S") AND between(extrYear+extrWeek,oJC.LowWeek,oJC.HighWeek) INTO TABLE &qry_arg3
>>>>>
>>>>>
>>>>>Could you see here a problem? I may expect not enough free disk space on my local computer, because syntax seems fine to me.