SELECT TOP 10 *
FROM   ((SELECT DISTINCT 1                                   AS ToolId,
                         r.date,
                         r.link,
                         r.title,
                         r.summary,
                         COALESCE(ia.name, 'Unknown Author') AS AuthorName
         FROM   vwBlogresults r
                LEFT JOIN Blogauthor ia
                       ON r.id = ia.id
                INNER JOIN BlogSection b
                        ON r.id = b.articleid
         WHERE  b.Sectionid IN ( 1, 2, 3, 4,
                               5, 6, 7, 8,
                               9, 10, 36, 38, 40 )
                AND isnewswire = 0
                AND isnewswire = 0)
        UNION
        (SELECT DISTINCT 3                AS ToolId,
                         r.date,
                         r.link,
                         r.title,
                         r.summary,
                         'Unknown Author' AS AuthorName
         FROM   vwvideoresults r
                INNER JOIN videoSection b
                        ON r.id = b.videoid
         WHERE  b.Sectionid IN ( 1, 2, 3, 4,
                               5, 6, 7, 8,
                               9, 10, 36, 38, 40 ))) res
ORDER  BY date DESC  

quote:

---

Originally posted by MIK_2008


1) Change in Join Order, in the first select statment move the left join to last e.g. "r inner join b left join ia"

---

• Create a temp table for the results of the inner joins then join to the outer table.


• Proper indexing - Somtimes the optimizer picks a bad plan to due to poor indexes. (PS - If you look at the actual execution plan, that might help guide you to a missing index that'll help solve the issue)


• Somtimes the optimizer it "too smart" for it's own good and if you do the inner joins first and add a FORCE ORDER hint that will get thing back in shape.

quote:

---

Originally posted by Lamprey

quote:

---

Originally posted by MIK_2008


1) Change in Join Order, in the first select statment move the left join to last e.g. "r inner join b left join ia"

---

The join order shouldn't make a difference as the oprimizer is cost-based. But, I agree it's a good habbit to put the outer joins last if possible.

---

quote:

---

Originally posted by MIK_2008

quote:

---

Originally posted by Lamprey

quote:

---

Originally posted by MIK_2008


1) Change in Join Order, in the first select statment move the left join to last e.g. "r inner join b left join ia"

---

The join order shouldn't make a difference as the oprimizer is cost-based. But, I agree it's a good habbit to put the outer joins last if possible.

---

Needs a little more explanation. Actually I mentioned this based on the logic(which a read somewhere in an article). its better to join table in terms of number of records in ascending order.
e.g.
Table1 = 2M records
table2 = 1Mrecords
table3 = 10 records

if my desired data is based on the joining of these three tables and I join them as
table 1 join table2 join table3
then what SQL would do is to pick data of the first join "table1 with table2", let say the resulting records are 1M, and then this data set is joined with table3, resulting the 10 records required.

However if I change the order table3 join table2 join table1. the SQL would only focus on the 10 records of the table 3 and would search only the corresponding (10) records in other two tables. won't it Turn to be faster!!?

Thanks!

Cheers
MIK

---