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.
Mark McCasland
Midlothian, TX USA