Hi, I ran into the question below in one of the free tests to measure your professional knowledge... I was so intrigued that I jot the whole question down. I'd like to hear other people's opinion on what would be the considerations for the correct answer. Thank you, - Tatyana ------------------------ CREATE TABLE EMP (emp_id int NOT NULL, name varchar(100), salary int, dept_id int NOT NULL, loc_ID int NOT NULL) CREATE TABLE RAT (rat_id int NOT NULL, emp_id int NOT NULL, rat numeric(10, 2)) CREATE TABLE LOC (loc_id int NOT NULL, code char(2), description varchar(256)) CREATE TABLE DEPT (dept_id int NOT NULL, code varchar(10),description varchar(256)) CREATE unique index emp_emp_id_inx ON EMP(emp_id) CREATE unique index rat_emp_id_inx ON RAT(emp_id) CREATE unique index rat_rat_id_inx ON RAT(rat_id) CREATE unique index loc_loc_id_inx ON LOC(loc_id) CREATE unique index dept_dept_id_inx ON DEPT(dept_id) *Assuming very large sets of data for the tables defined above which one of the queries below would run the fastest?* SELECT E.name FROM EMP E INNER JOIN LOC L ON L.loc_id = E.loc_ID WHERE E.emp_id = 1 SELECT E.name FROM EMP E INNER JOIN DEPT D ON D.dept_id = E.dept_id INNER JOIN LOC L ON L.loc_id = E.loc_ID WHERE E.emp_id = 11 SELECT E.name FROM EMP E INNER JOIN LOC L ON L.loc_id = E.loc_ID WHERE E.emp_id = 11 SELECT E.name FROM EMP E INNER JOIN DEPT D ON D.dept_id = E.dept_id WHERE E.emp_id = 1 SELECT E.name FROM EMP E INNER JOIN RAT R ON R.emp_id = E.emp_id WHERE E.emp_id = 1
Given that the `RAT` table is the only one to have an index on the `emp_id` field, I would suggest that that would be the fastest, as that wouldn't involve a table scan of the `RAT` table during the JOIN. Assuming the tables are all of similar size, that is.
Given the assumption that the same number of rows exist for emp_id 1 and 11, then all bar the 2nd query will have similar execution plans as they are a simple 2 table join, whereas the 2nd one is a 3 table join. Still given that, I don't see much performance difference in any of the queries!
By way of making up for my misdemeanour earlier I have created these tables, inserted 2M rows in each and executed the queries. Firstly - the estimated plans for Q1,Q2,Q3,Q4,Q5 are in %age terms, 19,25,19,19,19 (I know, this is > 100 but thats the execution planner for you). All the other plans are a combination of 2 index seeks and a RID lookup on the EMP table. Q2 has an Index Seek on EMP, a RID Lookup on EMP, an Index Seek on LOC and an final seek on DEPT. With 1M rows in each table there was no difference in them. With 2M rows I get: Q1 - 430 ms Q2 - 193 ms Q3 - 46 ms Q4 - 103 ms Q5 - 73 ms so, there you have it.