Hi!
I have a join which looks something like this:
SELECT ...
FROM @memberselection t
INNER JOIN order o
ON o.member_id = t.member_id
INNER JOIN transactions t
ON t.order_id = o.order_id
For a large set in @memberselection this takes forever. For a selection of about 1000 members, it takes 30 second.
PK on @memberselect is member_id.
I have an index on order table on member_id. Member_id is VARCHAR(30).
If I complement the join between order and @memberselection like below, time will drop to less than 1 second, even on larger sets!
SELECT ...
FROM @memberselection t
INNER JOIN order o
ON o.member_id = t.member_id
AND o.customer_id = t.customer_id <--- Added join
INNER JOIN transactions t
ON t.order_id = o.order_id
there is an index on order table on customer_id and customer_id is INTEGER.
I thought that this big difference was because the server had an easier time dealing with INT's then VARCHARs, so I tried changing the member_id to BIGINT instead (its too large to use INT)
Then time for query without customer_id dropped to 14 seconds, about half compared to VARCHAR columns.
Should there really be such a big difference in processing time when joining on INT vs BIGINT columns!?
I cannot see any other differences (except that the query plan when joining on customer_id needs some extra step to lookup member_id, so it should really be slower!).
In both cases it uses the expected index and the indices are identical except one starts with member_id and one with customer_id.
I can of course change all my joins to include customer_id, but Im interested in why this phenomenon happens in the first place...
I can also mention that stats are updated and query cache cleared between each test..
cheers
/Linus