Sql Query optimization Issue in JOIN

Question

I have multiple JOIN statements in one query and I have to check two settings in the JOIN. How to do that in best way?

table: WebSettings
-------------------------------------------------------
Name            | Setting   | InstID    | WebSettingID
-------------------------------------------------------
'EnableError'   | 2 | 1111  | 1
'ErrorsSetting' | 0 | 2121  | 2

Index :

InstID
WebSettingID (InstID)


table: InstProd
-------------------------------------------------------
InstitutionID   | Name
-------------------------------------------------------
1111        | 'Bank of Ind'
2121        | 'IOB'


Index :

InstitutionID


SELECT 

Column1,
DATEADD(.....)

FROM 
dbo.InstProd I 
INNER JOIN dbo.WebSettings WS1 ON
WS1.InstitutionID = I.InstID AND
WS1.Name = 'EnableError' AND WS1.Setting=2
INNER JOIN dbo.WebSettings WS2 ON
WS2.InstitutionID = I.InstID AND
WS2.Name = 'ErrorsSetting' AND WS2.Setting=0
WHERE.....

I want to check one WebSettings = 2 and other = 0 in the JOIN

Is there a best solution to do so?

Please explain better you request. What do you want as output? — Joe Taras, Jun 28 '16 at 07:23
That code won't even run. You're missing AND/OR clauses in your code. — Rich Benner, Jun 28 '16 at 07:27
@Keppy: Explain your aim. I don't know if your choose (JOIN) is the best or not. But you must post your table structure and your wished result — Joe Taras, Jun 28 '16 at 07:29

score 1 · Answer 1 · answered Jun 28 '16 at 07:38

Yes, you don't need to join WebSettings twice , though you didn't post your expected output , so I don't know exactly what to do, but adjust this:

SELECT <column1>,<column2>,
       MAX(CASE WHEN ws.Name = 'EnableError' AND ws.settings = 2 THEN <YourDesiredColumn> END) as enable_column,
       MAX(CASE WHEN ws.Name = 'ErrorsSetting' AND ws.settings = 0 THEN <YourDesiredColumn2> END) as Errors_column
FROM bo.InstProd I 
INNER JOIN dbo.WebSettings WS1 ON
WS1.InstitutionID = I.InstID AND
(WS.Name,ws.setting) in (('EnableError',2),('ErrorsSetting',0)) 
GROUP BY <column1>,<column2>

This will give you both enable and error columns in the same row , just like your query.

I have tried this and not seeing any improvement in performance compared to my script and decided to go with mine. Thanks for your support. — Keppy, Jun 28 '16 at 11:18

score 1 · Answer 2 · answered Jun 28 '16 at 07:38

1

Minimizing JOINS may give result what you expecting if you are not using WS2

SELECT 
 Column1,
 DATEADD(.....)

FROM
dbo.InstProd I 
INNER JOIN dbo.WebSettings WS1 ON WS1.InstitutionID = I.InstID 
                               AND ( WS1.Name = 'EnableError' 
                                     AND WS1.Setting = 2 )
                               OR ( WS1.Name = 'ErrorsSetting' 
                                    AND WS1.Setting = 0)  
WHERE.....

answered Jun 28 '16 at 07:38

Jaydip Jadhav

12,179
6
24
40

I will come back to this after verifying. Thanks – Keppy Jun 28 '16 at 07:45
1

I have tried this and not seeing any improvement in performance compared to my script and decided to go with mine. Thanks for your support. – Keppy Jun 28 '16 at 11:18

score 1 · Accepted Answer · answered Jun 28 '16 at 07:52

You can decide to not use JOIN, as my following solutions

If you want all InstProd with EnableError = 2 and ErrorSettings = 0

Try this:

SELECT I.*
FROM InstProd I
WHERE EXISTS
    (SELECT 'EnableError = 2'
        FROM WebSettings WS
        WHERE WS.InstID = I.InstitutionID
        AND WS.Name = 'EnableError' AND WS.Setting = 2)
AND EXISTS
   (SELECT 'EnableError = 2'
        FROM WebSettings WS
        WHERE WS.InstID = I.InstitutionID
        AND WS.Name = 'ErrorSettings' AND WS.Setting = 0)

If you want all InstProd with check about two setting values

Try this:

SELECT I.*,
CASE
    WHEN
        (SELECT COUNT(1)
        FROM WebSettings WS
        WHERE WS.InstID = I.InstitutionID
        AND WS.Name = 'EnableError' AND WS.Setting = 2) > 0
        AND
        (SELECT COUNT(1)
        FROM WebSettings WS
        WHERE WS.InstID = I.InstitutionID
        AND WS.Name = 'ErrorSettings' AND WS.Setting = 0) > 0
  THEN 'OK'
  ELSE 'KO'
END
FROM InstProd I

Thanks. In performance point of view, which one you will suggest? JOIN or EXISTS if my number of records is high? — Keppy, Jun 28 '16 at 11:24
I think doesn't exist a command for all situation. Depends what you want, in this case exists clause it's ok for the first case (because you don't show these info but you use only for conditions), for the second case I prefer (because are only two fields) a subquery in select field list. With a JOIN you must apply a group by or a distinct clause to discard duplicated rows — Joe Taras, Jun 28 '16 at 13:27

Sql Query optimization Issue in JOIN

3 Answers3