Alex Rivera | Logout

How to Speed Up Simple Join

Asked 2009-05-27T17:06:27.203
15

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!

Edit
Report

1 Answer

2

I bet your problem is the huge number of rows that are being inserted into that temp table. Is there any way you can add a WHERE clause before you SELECT every row in the database?

answered 2009-05-27T19:01:21.713

Your Answer