Dorf Dorf - 7 months ago 54
SQL Question

SQL Query - Performance Optimization

Im not so good at SQL, so I asked you guys for help on writing a query.

SQL Query - Table Joining Problems

I got an answer and it works! Its just noticeably slow. I hate to do it but Im really hoping someone out there wants to recommend some ways to optimize the query. I have not even attempted this on my own because I dont know enough about SQL to even begin googling.


What might help is to create indexes on the columns you're joining with. For example;

CREATE INDEX name_for_index ON Main (StatusID);

It generates a look-up table for this column that the algoritm that performs the query will use.

Edit: If you're not allowed to change the database, you may be out of luck. I have seen cases where easing on the JOIN statements improved the performance, that would be this;

  Main m, Status st, Secondary s
  st.ID = m.StatusID
  AND s.MainID = m.ID
  ( s.MainID IS NULL AND m.WhenDate = <YourDate>
    s.MainID IS NOT NULL AND s.WhenDate = <YourDate> )
  AND TypeId = <TypeFilter>
  AND ... other filters, if you need any ...

And then handling your other case, where the INNER JOIN is needed a bit more verbose.