SQL Server Join between two unique columns poor performance

Question

I have the following query that is taking a long time to execute.

SELECT
    Table1.A, Table1.B, Table2.C
FROM
    Table1
    INNER JOIN Table2
        ON Table1.UniqueCol = Table2.UniqueCol
WHERE
    Table1.UniqueCol = @storedProcParam

Both tables have a unique nonclustered index on UniqueCol which is a varchar(64) but neither has it as its primary key. One of the tables has a few million records and the other has a few thousand. Is it the fact that the column is a varchar(64) that is holding back performance, or is there something else I'm missing?

Assuming you have an index on the UniqueCol field, how is the @storedProcParam defined? Is it defined as a parameter to a stored procedure or a local variable in the stored procedure. I've seen better performance on query cache of the execution plan when using a local variable vs. a passed in variable. Not much else to go by though... — sgeddes
– sgeddes, Commented Mar 25, 2015 at 23:26
Does the query run better with a hard coded value vs. the parameter? — sgeddes
– sgeddes, Commented Mar 26, 2015 at 0:22

Martin Smith · Accepted Answer · 2015-03-25 23:46:15Z

1

Apart from some external issue such as blocking the only reason that springs to mind would be if the parameter was of a higher precedence datatype that causes an implicit cast and prevents a simple index seek on the two tables (using the implied predicate on table2) and a cross join of the 0 or 1 row result that both sides return.

You say the columns are varchar. You would get this scan instead of seek if the parameter was nvarchar under some collations.

edited Mar 25, 2015 at 23:46

answered Mar 25, 2015 at 23:40

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ashishduh Over a year ago

I checked the actual execution plan and I see exactly this issue. I didn't realize one of the tables was nvarchar(64), thanks!

Brennan Pope · Accepted Answer · 2015-03-26 01:58:04Z

0

The values in your select clause should be included in your non-clustered index. This would allow the engine to use the index exclusively instead of having to go to your clustered index. Have you rebuilt your clustered and/or non-clustered indexes recently? Do you know if your indexes are highly fragmented? Can you please post your actual execution plan?

answered Mar 26, 2015 at 1:58

Brennan Pope

1,1237 silver badges13 bronze badges

Collectives™ on Stack Overflow

SQL Server Join between two unique columns poor performance

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related