Optimize joins from multiple tables

Question

How can I optimize Performance of the below mentioned query when the table structure is as shown in the pic below

select CounterID, OutletTitle, CounterTitle
from(
    select OutletID, Text as OutletTitle 
    from Outlets as q1
    inner join 
    TranslationTexts as tt 
    on q1.TitleID=tt.TranslationID
    where tt.Locale='ar-SA' and q1.CompanyID=311 and q1.OutletID=8 --Locale & CompanyID & OutletID
    ) as O    
inner join    
(
    select CounterID, Text as CounterTitle, OutletID
    from Counters as q1
    inner join 
    TranslationTexts as tt 
    on q1.TitleID=tt.TranslationID
    where tt.Locale='ar-SA' and q1.OutletID=8 --Locale & OutletID   
) as C
on O.OutletID=C.OutletID

Editing your question to include sample data and desired results would help others understand what your query is supposed to be doing. — Gordon Linoff, Aug 04 '16 at 12:05
If tt.Locale has a small range of values, you can create a dictionary table for it and use foreign key in a where clause - searches over varchar data are slow. Also, you have pretty similar where clauses in both of your subqeries - think if you can move it to the outer query not to perform the same operation twice. — PacoDePaco, Aug 04 '16 at 12:15

score 2 · Answer 1 · answered Aug 04 '16 at 12:41

You should try this request :

SELECT CounterID, tou.Text as OutletTitle, tco.Text as CounterTitle
FROM Counters as co
    INNER JOIN Outlets as ou ON co.OutletID = ou.OutletID
    INNER JOIN TranslationTexts as tco on co.TitleID=tco.TranslationID
    INNER JOIN TranslationTexts as tou on ou.TitleID=tou.TranslationID
WHERE co.CompanyID=311 and co.OutletID=8 AND tco.Locale='ar-SA' and tou.Locale='ar-SA'

To have much better performance, you could add some indexes on the 3 tables.

The query performed good and the result was much better after indexing. Thanks for the code. — Mohammed Kamran Azam, Aug 06 '16 at 05:25

DVT · Answer 2 · 2016-08-04T12:51:57.080

This is a different approach. I cannot say about improvement in performance because that depends on a lot of other things, but I believe it is an equivalent version and an easier one to read.

SELECT
    C.CounterID
    , tt.Text AS OutletTitle
    , tt.Text AS CounterTitle
FROM
    Outlets AS q1
    INNER JOIN TranslationTexts AS tt ON q1.TitleID=tt.TranslationID
    INNER JOIN Counters C ON c.OutletID=q1.OutletID
    INNER JOIN TranslationTexts AS tt2 ON tt2.TranslationID=tt.TranslationID AND tt2.Locale=tt.Locale
WHERE
    tt.Locale='ar-SA' and q1.CompanyID=311 and q1.OutletID=8;

score 1 · Answer 3 · answered Aug 05 '16 at 09:53

The question is what you want to optimize.. readability (and maintainability) and/or performance ?

Most people have their own 'style' when writing queries. I prefer the one below, but to the server it will probably look the same and most likely the system will have the exact same amount of 'work' to get the data even though it 'looks' different to us humans. I'd suggest to google around a bit and learn how to interpret a Query Plan.

SELECT q2.CounterID, 
       tt1.Text as OutletTitle, 
       tt2.Text as CounterTitle

FROM Outlets as q1

INNER JOIN Counters as q2 
        ON q2.OutletID = q1.OutletID

INNER JOIN TranslationTexts as tt1
        ON tt1.TranslationID = q1.TitleID 
       AND tt1.Locale        = 'ar-SA'

INNER JOIN TranslationTexts as tt2 
        ON tt2.TranslationID = q2.TitleID
       AND tt2.Locale        = 'ar-SA' 

WHERE q1.CompanyID = 311 
  AND q1.OutletID  = 8

On of the things I notice is that you pass both CompanyID and OutletID as filters for the Outlets table. Since OutletID is the primary key of that table I wonder if you really need the filter on CompanyID. At best it will eliminate the record because it's the wrong company, but somehow I'm under the impression that you already know the right CompanyID.

As for performance, I'd advice these indexes

CREATE INDEX idx_Locale ON TranslationTexts (Locale, Translation_id)
CREATE INDEX idx_CompanyID ON Outlets (CompanyID) INCLUDE (TitleID, OutletID)

Most likely you even can make that index on Local a UNIQUE index making it work even better.

I am trying to achieve performance as the records are way too many, and maintainability is a lesser concern here. However i tried the code mentioned in the above answer as it is same as yours. Thanks for the code and help. — Mohammed Kamran Azam, Aug 06 '16 at 05:26
Well, 'reformatting' the code is only going to have a minor effect; but the indexes should result in quite an improvement. Good luck! — deroby, Aug 07 '16 at 09:57

Optimize joins from multiple tables

3 Answers3