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.