MSSQL select query optimization

Question

Is there any optimization possible in the select query below?

    SELECT 
    AwardAction.strActionName, AwardType.strAwardName, 
    Award.strStudentId,Award.iCount
    FROM
    [dbo].[Awards] Award, [dbo].[AwardAction] AwardAction,
    [dbo].[AwardTypes] AwardType
    WHERE
    AwardType.ApplicationId=@ApplicationId and 
    Award.ID=@ID and 
    AwardAction.Action=Award.Action and 
    AwardType.Type=Award.Type

The design of the table is something like this

enter image description here

Sorry I couldn't move the question to codereview as it already has answers

This question might be better-suited for the [Code Review](http://codereview.stackexchange.com/) Stack Exchange. — Matthew Haugen, Jul 16 '14 at 09:02
I am not able to delete the question since it is already answered — Vignesh Subramanian, Jul 17 '14 at 05:00
I'd say you don't have to. And if the [so far only] answer actually resolves the issue in your question, you can even accept it formally, that could be helpful for future visitors. — Andriy M, Jul 17 '14 at 05:22

score 1 · Accepted Answer · answered Jul 16 '14 at 09:04

1

You better write explicit joins:

SELECT 
AwardAction.strActionName, AwardType.strAwardName, 
Award.strStudentId,Award.iCount
FROM
[dbo].[Awards] Award
inner join [dbo].[AwardAction] AwardAction on AwardAction.Action=Award.Action
inner join [dbo].[AwardTypes] AwardType on AwardType.Type=Award.Type
WHERE
AwardType.ApplicationId=@ApplicationId and 
Award.ID=@ID

To optimize the execution of the query, you have to define indexes on the rows AwardType.ApplicationId, Award.Type and Award.Action.

answered Jul 16 '14 at 09:04

Lorenz Meyer

19,166
22
75
121

is Left join better than inner join in terms of performance?? – Vignesh Subramanian Jul 16 '14 at 10:05
@vignesh: Why do you ask? A left join and an inner join are *different* kinds of join, it just makes no sense to compare their performance. – Andriy M Jul 16 '14 at 12:41
@AndriyM ya i agree that they are different kinds of joins but I came across this answer [http://stackoverflow.com/a/2726683/848841] which has compared the performance with details – Vignesh Subramanian Jul 17 '14 at 03:29
@vignesh I don't understand. You link to an answer that clearly states that inner join is faster, so why do you ask the contrary ? – Lorenz Meyer Jul 17 '14 at 04:48
@vignesh: The linked answer also mentions that the two joins are generally not interchangeable, so again, why do you want to know which is faster? – Andriy M Jul 17 '14 at 04:58

MSSQL select query optimization

1 Answers1