Make a Query Faster w/o multiple joins

Question

So basically, I'm selecting multiple columns from multiple views (18 columns and 8 views to be exact). The total runtime is roughly 36 minutes. I tried cleaning up everything to make it more readable, but now it's taking even longer; 50 minutes and going.

Here's a sample of what's going on:

    SELECT (18 columns)
           -- different cases happening here
    FROM view1 m
    left join  view2 dep  on --something        
    inner join view3 c on --something 
    inner join view4 cl on --something 
    inner join view5 cc on --something  
    inner join view6 cp on --something 
    inner join view7 mp on --something 
    inner join view8 ma on --something

I'm just trying to get my columns but they are coming from several different places. Is there any way to make this faster? Is creating a temp table necessary? And if so, how should I implement it which would increase the query speed?

Thank You.

1) Not a jQuery question. 2) What are the indexes on the tables? — Taplar
– Taplar, Commented May 23, 2019 at 17:28
What does that have to do with if the tables have indexes or not? — Taplar
– Taplar, Commented May 23, 2019 at 17:40
It is difficult to derive performance from your query alone. Do you have an execution plan you can show us? — Simon
– Simon, Commented May 23, 2019 at 17:40
You can use this website to share your execution plan with us -> brentozar.com/pastetheplan — M. Kanarkowski
– M. Kanarkowski, Commented May 23, 2019 at 17:43

Russell Fox · Accepted Answer · 2019-05-23 19:07:49Z

1

Every join is going to add time/resources to the query, and when you join against views, you're adding every join in every view to your query. And, it only takes one non-performant view to slow down everything else. Three options I can think of:

Get rid of the views and join directly to the tables therein. Only select the joins you need, and only select the columns you need. Make sure every column in the join has an index (i.e., for "FROM tbl1 JOIN tbl2 ON tbl1.SomeID1 = tbl2.SomeOtherID", make sure there's an index on tbl1.SomeID1, and there's an index on tbl2.SomeOtherID.
If you can work on "old" data, you can run your query at night and fill a flattened table that you can run queries against the next day. Look for tips on creating a data warehouse.
Take the tips from #1, above, and try to optimize every view.

answered May 23, 2019 at 19:07

Russell Fox

5,4651 gold badge26 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jona Over a year ago

Thank You. Essentially got rid of a lot of temp tables which reduced the time by 30 minutes. Not to myself: DON'T USE TEMP TABLES!

Collectives™ on Stack Overflow

Make a Query Faster w/o multiple joins

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related