I have a query that extracts from two INNER joined tables. The tables contain approximately 30 million rows and 400,000 rows. The expected results should return around 300,000 rows. The query runs very vast (less than 1 second) through the first 200,000 rows returned, and then it starts to crawl returning at a rate of about 1000/sec for the remaining rows.
What is causing the apparent slowdown, and what can I do to speed it up?
I have tried adding an index hint as well as 'Cardinality' in the hint line. Nothing seems to help much.