SQL Inner Join Issue

Question

In one table, there are two foreign key from another table.

First table is documents and another table is common.

My current code,

SELECT a.* FROM documents AS a INNER JOIN common AS c ON a.docId = c.id INNER JOIN common as cc ON a.statusId = cc.id

what's the best way to join for the better performance?

Thanks.

There is no better way if your query produces the result you want. Note: It is not so good to use `select *`. Much better to specify the fields you actually want. http://stackoverflow.com/questions/3639861/why-is-select-considered-harmful — Mikael Eriksson, Dec 21 '11 at 07:26

score 2 · Answer 1 · answered Dec 21 '11 at 07:29

2

Your SQL looks fine. Make sure your fk columns have indices, and you should be good to go. What exactly is your cause for concern?

answered Dec 21 '11 at 07:29

Jake Feasel

16,785
5
53
66

what's the reason of using index ? I am just curious if there are better ways. – kevin Dec 21 '11 at 07:43
Yeah, I don't know much about index. Those index should be for the documents table? It will be faster ? – kevin Dec 21 '11 at 07:47
Yes, on a.docId and a.statusId. You may want to have a single index that includes both of these, to maximize this particular join. – Jake Feasel Dec 21 '11 at 07:49

score 1 · Answer 2 · answered Dec 21 '11 at 07:35

Why you JOIN the common table twice and don't even select any column out of it? If you use the join for filtering you can try do it like this:

SELECT a.* 
FROM documents AS a 
WHERE (SELECT COUNT(*) FROM common 
       WHERE a.docId = id OR a.statusId = id)>0

Then you make sure that id, docId and statusId are indexed.

If you just forgot to add the c and cc table to your column selects you are fine, just speed up setting indexes if you haven't already.

I forget to select the columns from the common table. It should be **a.*,c.Name, cc.Name** — kevin, Dec 21 '11 at 07:46

score 1 · Answer 3 · answered Dec 21 '11 at 07:43

select fields dont use select * this can increase performance on data traffic across server and client and set keys and indices to the tables

Select a.fieldone, a.fieldtwo FROM documents AS a 
INNER JOIN common AS c ON a.docId = c.id
INNER JOIN common as cc ON a.statusId = cc.id

score 1 · Answer 4 · answered Dec 21 '11 at 08:21

1

Basic Join Strategy to follow

1) Index the joined fields. 
2) Ensure that statistics are upto date, this way SQL wont recalculate plan and will used the cached plan
3) Join from the smaller table to the bigger one, help sql server
choose the best plan 
4) Read the Query Analyser Plan, if you see a Hash Join being performed on two large tables then add a covering index to make it choose a nested loop join which is better 
5) If a small table is joined to a large table a Hash join is so much
better. 
6) Avoid bookmark lookups. See this for more info http://www.sqlservercentral.com/articles/Performance+Tuning/bookmarklookups/1899/

answered Dec 21 '11 at 08:21

lloydom

377
2
11

Thanks for the information. What's statistics ? How can I read the query analyser plan? and What's Hash join? Sorry about my ignorance. I am not good at SQL. – kevin Dec 21 '11 at 08:24
1

Statistics are DB statistics, which are used by sqlserver for freshness of the index. You can see the "Actual Execution Plan" in sqlserver under Query : Menu . This article is a basic explanation on hash join http://msdn.microsoft.com/en-us/library/aa178403(v=SQL.80).aspx , you dont have to know it but it is useful when tuning queries – lloydom Dec 21 '11 at 08:26

score 1 · Answer 5 · edited May 23 '17 at 12:03

There is always more than one way to write any query using SQL and it's probably a good idea to test at least two candidate queries for performance. There will be many factors influencing performance not least of which will be the SQL product you are using.

Noting that the two joins to common are semijoins here's a suggested alternative:

SELECT * 
  FROM documents AS a 
 WHERE EXISTS (
               SELECT * 
                 FROM common AS c 
                WHERE a.docId = c.id
              )
       AND EXISTS (
                   SELECT * 
                     FROM common AS cc 
                    WHERE a.statusId = cc.id
                  );

SQL Inner Join Issue

5 Answers5

Basic Join Strategy to follow