why is this too lsow ?

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

Plan.pesession (6.2 kB)
avatar image By doutman 0 asked Apr 20 at 11:52 AM
more ▼
(comments are locked)
10|10000 characters needed characters left

2 answers: sort voted first

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.

avatar image By SDyckes2 96 answered Apr 20 at 05:47 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

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.

avatar image By Hugo Kornelis 271 answered Apr 22 at 02:44 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x683
x494
x19

asked: Apr 20 at 11:52 AM

Seen: 21 times

Last Updated: Apr 22 at 02:44 PM