Force inner select to be executed
I have a query that I am attempting to run, but inner query is being rolled up into the overall query - per the optimizer (and jobexam observation). While I know that if the inner query runs first, I will eliminate a number of records (making the result set smaller, and return quicker).
Just an example of what I am attempting to do:
select Account.Name, Account.State, Transaction.Amt, Transaction.Date, Transaction.Service
from Transaction
left join Account
on Account.Id = Transaction.Account
where Account.Id in (
Select Account.Name
from Account
where Account.Type is not null
and Account.Id>123456789
and Account.Id<=323456789
)
and Transaction.Date >= ?
and Transaction.Date <= ?
Index on 'Transaction.Date, Transaction.Account'
Is there a way to FORCE the inner select to be executed first? I want to use the Index mentioned above, but I would like the Account.Id refined to applicable 'Type' that is not null.
Comments
You might use %NOFLATTEN keyword.
Also, you might check if extentsize for the tables and selectivities for the columns are correct (http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=GSQLOPT_opttable).
I attempted to use '%NOFLATTEN', which changed the 'show plan', but the range is not acknowledged in the plan, showed 'looping on'.
I also ran a real SQL and watched via JOBEXAM and the process started with 0.
I found that my Account.Id is looping because that is a calculated value (multiple properties combined into one).
At first sight I'd say the Query Generator is right as your Sub Select Just adds some more WHERE conditions.
Your range on Transction.Date with related index might be much more limiting than your range on Account.Id.
A index on Account.Type might also speed up your query.
Select Account.Name
from Account
?? Is Account.Name the same as Account.Id ???
IN ( ) expects EXACT VALUES !
Suggestion if no done yet: Run tune Table for both tables
Next: publish the generated Query plan.
You are correct, it is more WHERE conditions. In the original query I did have those values as more WHERE, but I am attempting to speed up the processing.
I started looking at the data, and found the query was doing A LOT of process/retrieving of data that was later thrown on the floor. If I could make the query use the Account.Type first, I would eliminate extra time joining other (large) tables.
You are correct, that where clause is not exactly right (as this is not my real SQL, but a representation of what I was doing)
Should've been:
select Account.Name, Account.State, Transaction.Amt, Transaction.Date, Transaction.Service
from Transaction
left join Account
on Account.Id = Transaction.Account
where Transaction.Account in (
Select Account.Id
from Account
where Account.Type is not null
and Account.Id>123456789
and Account.Id<=323456789
)
and Transaction.Date >= ?
and Transaction.Date <= ?
OK, that looks better 
now go to Mgmt Portal /SQL and verify for both tables that you see values in column Selectivity (marked)
IF THERE IS NO SELECTIVITY ANY QUERY PLAN IS JUST GUESSWORK.
if this is empty Query Generator just can guess and do a lot of unnecessary extra work.
so got Tune Table click it
and this you get there information that the Query Generator allows to make useful optimizations (marked)
Next enter your specific query and click to "Show Plan"
that marked information tells you what is happening and Relative Cost qualifies the expected performance.
This query plan tells you what is really happening.
A Total different idea.
if Transaction.Account is defined as Account object (could also be calculated)
[ Property Account as Account ; ]
then you may use implicit join for your query.
it looks like this:
from Transaction
on Account.Id = Transaction.Account
where Transaction.Account in (
Select Account.Id
from Account
where Transaction.Account ->Type is not null
and Transaction.Account->Id>123456789
and Transaction.Account->Id <=323456789
and Transaction.Date >= ?
and Transaction.Date <= ?
I think what you want to do is write this a a JOIN without the subquery and then use %INORDER to force the compiler to start with the account table. then we should pick the Type index and everything should work the way you want.
from %INORDER Account JOIN Transaction
on Account.Id = Transaction.Account
WHERE.....