Why SQL server query running too slow around 2+ hours? It has lots of joins, left joins and ctes. I am asked tune it

Question

1.00/5 (2 votes)

See more:

I have got SQL query for its performance tuning as it gets executed in 2+ hours. It has lots of Joins, Left Joins, Group By and CTE

Any suggestion over it please

What I have tried:

I tried to figure out of its due to lots of joins

Posted 16-Jun-21 8:36am

mohit Gupta 2021

Updated 18-Jun-21 2:53am

Add a Solution

Comments

Maciej Los 16-Jun-21 14:47pm

There's tons of reason...

mohit Gupta 2021 16-Jun-21 14:50pm

If you can suggest some in context of my query

Patrice T 16-Jun-21 15:03pm

No, because you didn't gave any context

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patrice T · Answer 1 · 2021-06-16T08:56:00

Solution 1

Quote:
Why SQL server query running too slow around 2+ hours?

All in your title, because : "It has lots of joins, left joins and ctes."
You start to understand why SQL database design and optimization is a whole job.
We have no way to turn you in an expert over night.

A good database design is a match between tables structures, tables indexes and queries. The way one is designed impacts the 2 others.
Unfortunately, there is no magic trick to solve your problem, only a deep study of your database can pinpoint where is the mismatch.

Since you didn't have any detail, it is impossible to help you any further than general indications.

Posted 16-Jun-21 8:56am

Patrice T

Comments

Maciej Los 16-Jun-21 14:58pm

I was lazy to explain that. 5ed!

Patrice T 16-Jun-21 15:01pm

Thank you.
I know, wheater getting hot theses days in Europe.

Maciej Los 16-Jun-21 15:12pm

Oh, yeah. Few minutes after 9PM and there's over 20°C.

Patrice T 16-Jun-21 15:34pm

about 24°C here in France. :)

Maciej Los 16-Jun-21 15:36pm

Wow!

_-_-_-me 18-Jun-21 9:27am

29 degree C at 9 Pm for us.

Richard MacCutchan 16-Jun-21 16:23pm

Same here even after a short rain storm.

Tony Hill · Answer 2 · 2021-06-16T08:59:00

Solution 2

The best place to start is to look at the execution plan in MS Server management studio to find out what parts of the
query are the most costly and try and see if you can improve them.

Posted 16-Jun-21 8:59am

Tony Hill

Jörgen Andersson · Answer 3 · 2021-06-18T02:53:00

Solution 3

Add appropriate indexes. Usually on the columns that are used in JOINs or in the WHERE conditions

Posted 18-Jun-21 2:53am

Jörgen Andersson

Updated 18-Jun-21 2:54am

v2