why is this too lsow ?

doutman 2018-04-20 11:52:25

A simple query takes hours to run , the top 1000 takes 25 mins
any suggestions

SDyckes2 2018-04-20 17:47:20
There is not a WHERE clause to filter any of the records. The plan shows an estimated 17+ Trillion records being returned with the estimated size of over 6 million GB. I would try to filter the number of records you are returning and build a non-clustered index to support the JOIN condition and WHERE clause.
Hugo Kornelis 2018-04-22 14:44:03
In addition to what SDyckes2 wrote: you mention the top 1000 taking 25 minutes, but I see no TOP (1000) in the query. If you only need the first 1000 rows, then add that TOP clause – it will allow the optimizer to create a plan that finds the first 1000 matches a lot faster.

That being said – a query that tries to return 17+ trilllion rows is almost certainly incorrect. I suggest that you fix the query first and focus on performance when it returns the correct results. Move to a test database with much smaller input tables, debug the query, then move back to production and start tuning.