Query Slow Result

Question

Query Slow Result

immaduddinahmed

Ten Centuries

Points: 1375
More actions
December 6, 2019 at 9:08 am

#3705267

hi ,
i getting result of query 65954 rows its take 20 seconds how i can i make query fast.

immad

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Maybe 20 seconds is fast. Care to provide more information, please - query, execution plan, table DDL, SQL Server version and edition, server RAM, max server memory, no of processors, maxdop, cost threshold for parallelism and anything else you think may be relevant?

John

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 2

one thing i'd add to john's very valid points... do you start getting results immediately, but it takes 20 seconds to complete? or does it take 20 seconds and you get your results after

if you start getting rows straight away then it's likely a table scan issue. if it's the other way then we need a copy of the query

MVDBA

immaduddinahmed Ten Centuries Points: 1375 More actions · Answer 3

immaduddinahmed

Ten Centuries

Points: 1375

December 6, 2019 at 10:05 am

#3705282

it take 20 seconds and results get after

immad

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 4

you have to give us more, even just the query definition

we can't help you unless you share a bit more information

MVDBA

immaduddinahmed Ten Centuries Points: 1375 More actions · Answer 5

immaduddinahmed

Ten Centuries

Points: 1375

December 6, 2019 at 11:32 am

#3705328

Its Solved i use temp table

Thanks

immad

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 6

Well, just in case you have more issues like this, I'd like to recommend my books. Query tuning is a pretty vast topic. Follow the links below.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 7

immaduddinahmed wrote:

hi ,
i getting result of query 65954 rows its take 20 seconds how i can i make query fast.

It's easy... just hit the "Go faster" button.

immaduddinahmed wrote:

Its Solved i use temp table
Thanks

That's one of them. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lindsay Newbie Points: 9 More actions · Answer 8

Different Ways to Improve SQL Query Performance

Avoid Multiple Joins in a Single Query. ...

Eliminate Cursors from the Query. ...

Avoid Use of Non-correlated Scalar Sub Query. ...

Avoid Multi-statement Table Valued Functions (TVFs) ...

Creation and Use of Indexes. ...

Understand the Data. ...

Create a Highly Selective Index. ...

Position a Column in an Index.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 9

Lindsay wrote:

Different Ways to Improve SQL Query Performance
Avoid Multiple Joins in a Single Query. ...

Whoa!

No. Absolutely not. You structure your data as is appropriate with primary keys and enforced referential constraints. Then, you write queries that correctly use these keys as join criteria. Possibly indexing some of the foreign keys. However, you don't avoid joins or try to eliminate joins or make your relational database engine non-relational as a query tuning choice. No. In fact, foreign keys and joins can make queries faster.

I have, what is now an VERY old story, about a 86 table join that ran in about 500ms. Now, it took 5 minutes to compile, but it ran in 500ms. Joins are not an inherent problem in a relational storage engine. It's built around the idea. Please, be cautious offering this kind of advice.

And this one:

"Position a Column in an Index"

What does that even mean? Rearrange the key order to better support a query? Add a column as INCLUDE to make an index covering? I can't tell.

Sorry, I don't like being critical on posts, but perpetuating bad information hurts us all. Better to try to clarify things than leave them laying about.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 10

Grant is correct, if you put your database into 3rd normal form you will have to join. In my honest opinion getting 3nf is the most important part of getting not only performance, but also easy coding

one of the lessons I learned early in my career was that your database table structure should not force a specific coding pattern(ie forcing you to use functions)

i would add - avoid using redundant joins, this can be really tricky when you use views

MVDBA

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 11

Position a Column in an Index.

are you talking about choosing the order of columns in an index?

MVDBA