asked
Which query would run the fastest?
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.
Good point. Query #5 will be able to join to the RAT table on an index, so it can do an index seek rather than a clustered index or table scan.
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!
Thank you, Kev. I would, too, think that the 2nd query - since it sticks out with an extra join in it - would be a good candidate for running the longest. But what could be the decisive difference between the other queries?.. Could the fact that RAT table got two indexes play any role? Tatyana
@Kev - emp_id has a unique index... only one row for any given id.
What does the estimated execution plan look like for each one?
@Tatyana - Dont misunderstand me, your question is not at all tiring, I simply misread it as I am already tired - its been a 16hr day for me so far. This whole forum is for people to ask questions about SQL things that they dont understand or want explained again. Providing you are asking questions, we are all happy to provide answers. You have no reason to apologise, that is for me to do as I shouldnt have skimmed through your question but read it accurately and then given an answer.
I reckon this is one of those theoretical questions, rather than a practical...
Oh, right. A bit tired here. I nearly just corrected the grammar and left!
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.
I seem to remember reading a couple of blog posts recently about the ways that SSMS lies to you... Now, if only I could remember who wrote them...
@Fatherjack - no problem :) I tried to do this, too, but did not get to 2M rows. So, the execution plan did not change with the number of rows growing? That was a question from the Brainbench SQL (ANSI) Fundamentals test (BTW, it's free now). I do think that the correct answer was implied to be that one of @Fatherjack's (that is, Q5)... I did learn something new. Thank you, everybody! Tatyana
