Help me tune this query...Please. :)

Question

Help me tune this query...Please. :)

tim.cloud

Ten Centuries

Points: 1164
More actions
April 4, 2013 at 12:40 pm

#394526

OK, so I have a query that works great, but runs dog slow, and I think it's because of needing to get the MAX date_started record in the WHERE clause. How can I use CTE or modify this to make it run better?
SELECT CASE WHEN LEN(p4.[name]) = 0 THEN p5.[name] ELSE p5.[name] + '\' + p4.[name] END AS server_instance_name
,p3.[name] [database_name]
,p4.[name] AS 'instance'
,p5.[name] AS 'server'
,p2.start_datetime
,p2.action_status
,p1.backup_type
,p2.utc_offset
,DATEDIFF(ss,p2.start_datetime,p2.end_datetime) AS 'duration'
,p2.uncompressed_KB
,p2.compressed_KB
FROM SQLsafeRepository.dbo.backup_sets p1
INNER JOIN SQLsafeRepository.dbo.actions p2 ON p2.backup_set_id = p1.backup_set_id
INNER JOIN SQLsafeRepository.dbo.databases p3 ON p3.database_id = p2.database_id
INNER JOIN SQLsafeRepository.dbo.instances p4 ON p4.instance_id = p3.instance_id
INNER JOIN SQLsafeRepository.dbo.servers p5 ON p5.server_id = p4.server_id
WHERE p2.start_datetime = (SELECT MAX(t2.start_datetime)
FROM SQLsafeRepository.dbo.backup_sets t1
INNER JOIN SQLsafeRepository.dbo.actions t2
ON t2.backup_set_id = t1.backup_set_id
INNER JOIN SQLsafeRepository.dbo.databases t3
ON t3.database_id = t2.database_id
INNER JOIN SQLsafeRepository.dbo.instances t4
ON t4.instance_id = t3.instance_id
INNER JOIN SQLsafeRepository.dbo.servers t5
ON t5.server_id = t4.server_id AND t3.[name] = p3.[name]
WHERE p5.[name] = t5.[name]
AND p4.[name] = t4.[name]
AND p3.[name] = t3.[name]
AND t1.backup_type = 0
AND t2.action_type = 0)

Viewing 7 posts - 1 through 6 (of 6 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

I don't see anything hugely obvious here. Unfortunately there aren't a lot of details either. Take a look at this article about how to best post questions about performance problems. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323518 More actions · Answer 2

the actual execution plan, if oyu can post it as a .SQLPlan file, would give us the most detailed information;

aside from that, here's what i see:

i see six objects in your query, ie SQLsafeRepository.dbo.databases; is that a VIEW, or is it a real table.

if it is a view, it may be gathering a lot of information that is not needed in this query. i would already think that querying real underlying tables will improve the performance a lot.

if it's a table, is there an index on database_id ,[name]?

any of the columns in the WHERE statmeents are candidates for indexes if they don't already exist.

any columns being selected are candidates for INCLUDE on the index to speed things up as well.

index on SQLsafeRepository.dbo.actions.start_datetime? as well?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ScottPletcher SSC Guru Points: 101105 More actions · Answer 3

Agree absolutely. Can you not specify any time of date range to help limit the rows to be processed?

And, what the heck, maybe try this first too:

SELECT CASE WHEN LEN(p4.[name]) = 0 THEN p5.[name] ELSE p5.[name] + '\' + p4.[name] END AS server_instance_name

,p3.[name] [database_name]

,p4.[name] AS 'instance'

,p5.[name] AS 'server'

,p2.start_datetime

,p2.action_status

,p1.backup_type

,p2.utc_offset

,DATEDIFF(ss,p2.start_datetime,p2.end_datetime) AS 'duration'

,p2.uncompressed_KB

,p2.compressed_KB

FROM SQLsafeRepository.dbo.backup_sets p1

INNER JOIN SQLsafeRepository.dbo.actions p2 ON p2.backup_set_id = p1.backup_set_id

INNER JOIN SQLsafeRepository.dbo.databases p3 ON p3.database_id = p2.database_id

INNER JOIN SQLsafeRepository.dbo.instances p4 ON p4.instance_id = p3.instance_id

INNER JOIN SQLsafeRepository.dbo.servers p5 ON p5.server_id = p4.server_id

INNER JOIN

(SELECT t5.[name] AS server_name

,t4.[name] AS instance_name

,t3.[name] AS db_name

,MAX(t2.start_datetime) AS start_datetime

FROM SQLsafeRepository.dbo.backup_sets t1

INNER JOIN SQLsafeRepository.dbo.actions t2

ON t2.backup_set_id = t1.backup_set_id

INNER JOIN SQLsafeRepository.dbo.databases t3

ON t3.database_id = t2.database_id

INNER JOIN SQLsafeRepository.dbo.instances t4

ON t4.instance_id = t3.instance_id

INNER JOIN SQLsafeRepository.dbo.servers t5

ON t5.server_id = t4.server_id

WHERE t1.backup_type = 0

AND t2.action_type = 0

GROUP BY t5.[name]

,t4.[name]

,t3.[name])

AS t_max ON t_max.server_name = p5.name AND t_max.instance_name = p4.name AND t_max.db_name = p3.name AND t_max.start_datetime = p2.start_datetime

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

mickyT SSChampion Points: 10360 More actions · Answer 4

You could also try something like

SELECT CASE WHEN LEN(p4.[name]) = 0 THEN p5.[name] ELSE p5.[name] + '\' + p4.[name] END AS server_instance_name

,p3.[name] [database_name]

,p4.[name] AS 'instance'

,p5.[name] AS 'server'

,p2.start_datetime

,p2.action_status

,p1.backup_type

,p2.utc_offset

,DATEDIFF(ss,p2.start_datetime,p2.end_datetime) AS 'duration'

,p2.uncompressed_KB

,p2.compressed_KB

FROM SQLsafeRepository.dbo.backup_sets p1

INNER JOIN SQLsafeRepository.dbo.actions p2 ON p2.backup_set_id = p1.backup_set_id

INNER JOIN SQLsafeRepository.dbo.databases p3 ON p3.database_id = p2.database_id

INNER JOIN SQLsafeRepository.dbo.instances p4 ON p4.instance_id = p3.instance_id

INNER JOIN SQLsafeRepository.dbo.servers p5 ON p5.server_id = p4.server_id

OUTER APPLY (

SELECT MAX(t2.start_datetime) max_datetime

FROM SQLsafeRepository.dbo.backup_sets t1

INNER JOIN SQLsafeRepository.dbo.actions t2 ON t2.backup_set_id = t1.backup_set_id

INNER JOIN SQLsafeRepository.dbo.databases t3 ON t3.database_id = t2.database_id

INNER JOIN SQLsafeRepository.dbo.instances t4 ON t4.instance_id = t3.instance_id

INNER JOIN SQLsafeRepository.dbo.servers t5 ON t5.server_id = t4.server_id

WHERE p5.[name] = t5.[name]

AND p4.[name] = t4.[name]

AND p3.[name] = t3.[name]

AND t1.backup_type = 0

AND t2.action_type = 0

) p6

WHERE p2.start_datetime = p6.max_datetime

This may not help your performance though

Paul White has a couple of articles on using apply

http://www.sqlservercentral.com/articles/APPLY/69953/

http://www.sqlservercentral.com/articles/APPLY/69954/

EDIT: misplaced )

Alexander Suprun SSCertifiable Points: 6253 More actions · Answer 5

tim.cloud (4/4/2013)
and I think it's because of needing to get the MAX date_started record in the WHERE clause.

Why don't you verify this assumption by running the sub-query separately?

Alex Suprun

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Use ROW_NUMBER, something like this:

USE SQLsafeRepository;

;WITH UnfilteredResult AS (

SELECT CASE WHEN LEN(p4.[name]) = 0 THEN p5.[name] ELSE p5.[name] + '\' + p4.[name] END AS server_instance_name

,p3.[name] [database_name]

,p4.[name] AS 'instance'

,p5.[name] AS 'server'

,p2.start_datetime

,p2.action_status

,p1.backup_type

,p2.utc_offset

,DATEDIFF(ss,p2.start_datetime,p2.end_datetime) AS 'duration'

,p2.uncompressed_KB

,p2.compressed_KB

,rn = CASE

WHEN p1.backup_type = 0 AND p2.action_type = 0

THEN ROW_NUMBER() OVER(PARTITION BY p1.backup_set_id ORDER BY p2.start_datetime DESC)

ELSE NULL END

FROM dbo.backup_sets p1

INNER JOIN dbo.actions p2 ON p2.backup_set_id = p1.backup_set_id

INNER JOIN dbo.databases p3 ON p3.database_id = p2.database_id

INNER JOIN dbo.instances p4 ON p4.instance_id = p3.instance_id

INNER JOIN dbo.servers p5 ON p5.server_id = p4.server_id

)

SELECT *

FROM UnfilteredResult

WHERE rn = 1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden