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

  • 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)

  • 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/

  • 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!

  • 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".

  • 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 )

  • 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

  • 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

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

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