Query Run Time

Question

Post reply

Query Run Time

water490

SSChasing Mays

Points: 647
More actions
December 14, 2021 at 4:59 am

#3963334

Hi everyone

The query I am working on seems slow to me. The table has about 600 records and the query takes about 15 seconds to run. I put on statistics and here is what I get. I don't know how to interpret this. Are the stats showing what I am seeing? I took a look at the execution plan too and I have no clue on how to use it to improve the query. The file is 40 MB which is bigger than my query 22 KB. I am seeing a lot of Nested Loops (Inner Join) with very high %. Some are like 3000% others are 9000% etc. I am not an expert but I am pretty sure these are not good things. I am not sure what information to provide so you can actually help me. Please let me know what you need and I will provide it.

(1 row affected)

Table 'Data'. Scan count 15172, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 10472, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table 'Data'. Segment reads 7586, segment skipped 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)

Completion time: 2021-12-13T20:34:18.5386878-08:00

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

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

water490 SSChasing Mays Points: 647 More actions · Answer 1

the concern I have is that if it takes 15 seconds to process a 600 row table then what it is going to look like when it has to process a 5million plus row table. I am not really sure where to start on improving its performance. I thought that having CTEs would improve performance so that way certain calculations are done once and only once. Any suggestions on what I should look at when I am improving its performance?

Phil Parkin SSC Guru Points: 245813 More actions · Answer 2

The information we need to help, at a minimum:

The query itself
The Actual query plan (.sqlplan), added as a text attachment.

Once we have these, there may be requests for additional info.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Michael L John One Orange Chip Points: 26551 More actions · Answer 3

Michael L John

One Orange Chip

Points: 26551

December 14, 2021 at 1:33 pm

#3963502

If possible, the schema of the tables would help.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 4

I'm with everyone else. To help you, we need to see code & structures. However, a couple of points.

If you're seeing 3000% usage, I know two things. First, you're using a multi-statement table-valued user-defined function. Those are notoriously problematic when it comes to performance. I would look to eliminate it, right now. Second, you're using an old version of SSMS because the 3000% mess was fixed in later versions. Might want to update.

Also, 10472 reads suggests more than a little bit above 600 rows in the table, unless they are EXTREMELY wide rows (lots of columns). I don't think we're getting quite a complete picture here.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

water490 SSChasing Mays Points: 647 More actions · Answer 5

water490

SSChasing Mays

Points: 647

December 14, 2021 at 5:16 pm

#3963575

Hi everyone

Thanks for the replies. If I include the execution plan then does the query come with it automatically or do I have to add the query separately?

Thank you

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

water490 wrote:

Hi everyone

Thanks for the replies. If I include the execution plan then does the query come with it automatically or do I have to add the query separately?

Thank you

The execution plan has the query in it. However, if you're using UDFs, that code won't be in the plan.

If possible, execute the query and get the execution plan plus runtime metrics (aka, the Actual Plan). If you can't, just the execution plan will help.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

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

Not to mention that the code included in the execution plan is frequently truncated or a bit "warped". I recommend always including the code separately. It doesn't take long to do and can save time elsewhere.

--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)
Intro to Tally Tables and Functions

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 8

You can also use this site: https://www.brentozar.com/pastetheplan/

If you use the above, posting the code here would also be a good idea - even if the code is included in the plan. When posting the code - please use the insert/edit code sample button.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

Guess the op lost interest.

It also looks like we've had a problem with DBCC TIMEWARP on this thread. The original post was on 14 Dec 2021 at 11:59PM.

Even the Op's second post was almost a full day before that according to the date and time of 14 Dec 2021 at 12:05AM.

--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)
Intro to Tally Tables and Functions

GaryV Hall of Fame Points: 3307 More actions · Answer 10

Jeff Moden wrote:

Guess the op lost interest.

It also looks like we've had a problem with DBCC TIMEWARP on this thread. The original post was on 14 Dec 2021 at 11:59PM.

Even the Op's second post was almost a full day before that according to the date and time of 14 Dec 2021 at 12:05AM.

Maybe the user made a trip across the International Date Line between posts. 🙂