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.
Add a comment
|
2 Answers
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;
...
FROM
Main m, Status st, Secondary s
WHERE
st.ID = m.StatusID
AND s.MainID = m.ID
AND
( s.MainID IS NULL AND m.WhenDate = <YourDate>
OR
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.
3 Comments
Dorf
I should have mentioned it but it will take an act of god for me to make any modifications to the database.. Hopefully there is a way to change the query and get faster results...
GolezTrol
You can do some optimizations other than adding indexes, but it will not yield much results and it will make your query much more complex. I would strongly advise you to ask your DBA to have a look and add the right indexes. Or give you the rights to do so.
Dorf
Thanks a lot to both of you. I think Im going to have to push for the act of god. Going to play around with both of your queries but I think the proper answer is 'add indexes'. Really appreciate both of your guys' time!!
Sometimes a trick like this might work a little, but as you can see, it mainly complicates your query. But like I said in the comments: I really urge you to ask your DBA to add proper indexes, and maybe even teach you how to to determine which indexes you need and give you the rights to add them.
I've seen cases where the OR in the WHERE clause is resolved by using two separate, almost similar selects which are combined by USING ALL. But I believe this trick is mainly useful on Oracle, and mainly on tables that do have proper indexes. Worth a try, though.
SELECT
x.*
FROM
(SELECT
m.ID as MainID,
m.WhenDate as MainWhenDate,
m.InfoText,
m.StatusID,
st.StatusText,
m.TypeID,
NULL AS SecondaryWhenDate,
m.WhenDate AS ActualWhenDate
FROM
Main m
INNER JOIN Status st ON st.ID = m.StatusID
LEFT JOIN Secondary s ON s.MainID = m.ID
WHERE
s.MainID IS NULL AND
m.WhenDate = <YourDate>
UNION ALL
SELECT
m.ID as MainID,
m.WhenDate as MainWhenDate,
m.InfoText,
m.StatusID,
st.StatusText,
m.TypeID,
s.WhenDate AS SecondaryWhenDate,
s.WhenDate AS ActualWhenDate
FROM
Main m
INNER JOIN Status st ON st.ID = m.StatusID
INNER JOIN Secondary s ON s.MainID = m.ID
WHERE
s.WhenDate = <YourDate>) x
WHERE
x.TypeId = <TypeFilter>
AND ... other filters, if you need any ...