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.
If it's not broken, fix it until it is.
My Blog