I have my main table (Table1) which contains approx 500,000 rows - my queries on this table perform perfectly.
I have another table (Table2) which has additional breakdown information of the data held with Table1. This table, Table2, contains approx 4 million rows.
When I join Table2 to Table one my queries go from taking <1sec to 6mins+
Both tables are indexed. There are no key constraints. The foreign key in Table2, which relates to one of the index keys in Table1, is made up of other information (a bit of a blob I'm affraid) eg: "I\123" might be a typical contents of the key in Table2 whereas in Table1 it would just be "123".
Other than that the only thing that I can think is slowing the query is the size of Table2.
I have also used SQL's Estimated Execution Plan which does highlight that one process takes up 44% which is very high compared to all the other processes which never raise higher than 1% - it indicates that the process taking up 44% is a "Bookmark Lookup"
Hope someone can help