How to rewrite query to take small time when executed ?

Question

1.00/5 (2 votes)

See more:

I work on SQL server 2012 it take too much time to enhance it

so how to enhance it to take small time

how to write query this best enhance to take less time ?

it take too much time near to half hour to return 50000 rows

What I have tried:

https://www.brentozar.com/pastetheplan/?id=H1XwcNHXv

Posted 27-Aug-20 4:02am

ahmed_sa

Updated 4-Sep-20 6:59am

Add a Solution

Comments

OriginalGriff 27-Aug-20 10:28am

What have you tried?
Where are you stuck?
What help do you need?

Patrice T 27-Aug-20 10:37am

You need to switch from "magic" to "more magic" (Jargon file)

Richard MacCutchan 27-Aug-20 10:45am

"I canna change the laws o' physics Captain"
Do not use queries that return so many rows. If you really need 50,000 at one time then add some code to page them so you can start work on the first subset while the rest are being extracted.

Patrice T 4-Sep-20 14:08pm

So true first scientific officer :)

Richard MacCutchan 5-Sep-20 3:00am

It's logical Captain. :)

1 solution

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)

CHill60 · Answer 1 · 2020-09-04T06:59:00

On a previous post of yours I gave you some information on how to analyze your query - see
When run query for only part it take too much time for only one part why ?[^]. One glance at your execution plan tells me that perhaps you ought to look at the indexes on table Parts.Nop_PartsFamilyAttribute

You have already found Brent's site so explore the information that is provided there - e.g. sp_BlitzIndex® - SQL Server's Index Sanity Test[^]

See @Richard-MacCutchan 's comment and do something about having to return so many rows at once - this article might help SQL Server 2005 Paging Results[^] (ignore the 2005 version, it should still work in 2012)

We really cannot provide you with much more help without table schema information and some sample data (ideally the script to create the tables and populate them with a small sample of the data). It's also a good idea to explain what it is that you are trying to get out of the database, e.g. some expected results for the sample data given. Help us to help you.