How to Speed Up Simple Join

Question

I am no good at SQL.

I am looking for a way to speed up a simple join like this:

SELECT
    E.expressionID,
    A.attributeName,
    A.attributeValue
FROM 
    attributes A
JOIN
    expressions E
ON 
    E.attributeId = A.attributeId

I am doing this dozens of thousands times and it's taking more and more as the table gets bigger.

I am thinking indexes - If I was to speed up selects on the single tables I'd probably put nonclustered indexes on expressionID for the expressions table and another on (attributeName, attributeValue) for the attributes table - but I don't know how this could apply to the join.

EDIT: I already have a clustered index on expressionId (PK), attributeId (PK, FK) on the expressions table and another clustered index on attributeId (PK) on the attributes table

I've seen this question but I am asking for something more general and probably far simpler.

Any help appreciated!

Answer 1

You definitely want to have indexes on attributeID on both the attributes and expressions table. If you don't currently have those indexes in place, I think you'll see a big speedup.

Answer 2

In fact, because there are so few columns being returned, I would consider a covered index for this query

i.e. an index that includes all the fields in the query.