Optimization of Query
-
Hi,
Can anyone tell me how this query could be optimised?
SELECT distinct I.Code, c., c.[desc]
FROM Instrument I
LEFT JOIN Trade t On I.ID = t.InstrumentID
LEFT JOIN Currency C on C.id = t.CurrencyID
WHERE c. is not null
order by i.Code
Thanks
-
What does the execution plan look like?
Things to not want from an optimization perspective (but still may be necessary)
DISTINCT - sort is negative on performance
NOT - negations may force scans instead of seeks
WHERE - too vague arguments or too many rows returned - this takes time.
Indexes - we want seeks and not scans (unless tables are very small in which case it's no big deal)
Sometimes optimization isn't all about the SQL itself, which may be a result of how the tables are defined, thus a query is written in a certain way. In those cases optimization also may involve the underlying table(s)
/Kenneth
-
>>LEFT JOIN Currency C on C.id = t.CurrencyID
>>WHERE c. is not null
That is equivalent to:
INNER JOIN Currency C on C.id = t.CurrencyID
... with no need for a WHERE. Might change the exec. plan.
-
Cheers guys. Will look into it.
-
Not necessarily equal..?
c.key isn't part of the join definition, as far as we know there may be c.key containing both null and non-values. If conditions where c.id = t.currencyID is true but also c.key is null, and that should not be returned, then the left join is needed.
Edit ----
Correction on myself: The JOIN may be an INNER join, LEFT is not necessary, it is the WHERE key IS NOT NULL that is still necessary.
/Kenneth
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply