Hi All,
I've got a large job management system, containing some 40 tables, it's more query intensive then write intensive.
Anyway, long story short; two tables: tblQLBoardsUP and tblTasks.
tblQLBoardsUp contains around 8,000 rows, tblTasks contains around 76,000 records.
The following query:
SELECT tblQLBoardsUp.*, Tasks_tbl.*
FROM tblQLBoardsUp INNER JOIN Tasks_tbl ON tblQLBoardsUp.TaskID = Tasks_tbl.TaskID
WHERE tblQLBoardsUp.AgencyID = '3963CE27-376E-490B-B676-25B9D5D9008D' runs in around 2 seconds, and uses a Hash Match/Inner Join. I notice from the query plan the DB is basicly getting every row from Tasks_Tbl and matching them to each row in qlboards up.
If I change INNER JOIN to INNER LOOP JOIN, the query runs in less then 1 second every time, and in my opinion, the query plan looks a lot more efficent.
So my question is, why is the optimiser choosing a Hash Match over an Inner Loop? What indexes should be on each table to do what I want.
Yes, I can use join hints for this, BUT the actual production query joins around 3 more tables of similar size, and I would rather have SQL choose the best join rather then having to tell it what to do..
The index are as follows:
tblQLBoardsUp : AgencyID clustered, TaskID non clustered
Tasks_tbl: PropertyID clustered, TaskID non clustered
I've got a large job management system, containing some 40 tables, it's more query intensive then write intensive.
Anyway, long story short; two tables: tblQLBoardsUP and tblTasks.
tblQLBoardsUp contains around 8,000 rows, tblTasks contains around 76,000 records.
The following query:
SELECT tblQLBoardsUp.*, Tasks_tbl.*
FROM tblQLBoardsUp INNER JOIN Tasks_tbl ON tblQLBoardsUp.TaskID = Tasks_tbl.TaskID
WHERE tblQLBoardsUp.AgencyID = '3963CE27-376E-490B-B676-25B9D5D9008D' runs in around 2 seconds, and uses a Hash Match/Inner Join. I notice from the query plan the DB is basicly getting every row from Tasks_Tbl and matching them to each row in qlboards up.
If I change INNER JOIN to INNER LOOP JOIN, the query runs in less then 1 second every time, and in my opinion, the query plan looks a lot more efficent.
So my question is, why is the optimiser choosing a Hash Match over an Inner Loop? What indexes should be on each table to do what I want.
Yes, I can use join hints for this, BUT the actual production query joins around 3 more tables of similar size, and I would rather have SQL choose the best join rather then having to tell it what to do..
The index are as follows:
tblQLBoardsUp : AgencyID clustered, TaskID non clustered
Tasks_tbl: PropertyID clustered, TaskID non clustered
