okay I've got a stored procedure that creates a table #Branches. it is populated with pk's from the Categories table based on certian criteria. when the table gets HUGE (which it often does, sometimes over 1,000,000 records) I have a sql statement that is taking forever to execute. and this execution loops alot... so the statement is as follows: SELECT COUNT(*) FROM Categories with (nolock) WHERE categories.admininactivated=0 and Categories.LEAF_ID IN ( SELECT DISTINCT BRANCH_ID FROM #Branches with (nolock) ) AND Categories.BRANCH_ID NOT IN (SELECT DISTINCT BRANCH_ID FROM #Branches with (nolock)) I think what's taking so much time is querying the #Branches table which of course is not indexed two times... does anyone have any ideas as to how to solve this or speed it up?
I would guess that the main bottlenecks are the subqueries. They will each be performing a full table scan which at least triples the execution time of the entire query. Is there any way to combine the query into a single query using JOINS?