Query running slow on prod.

  • Dear All,

    I have attached two text files, one with SQL query and another with test data and tables. This is from my local system.

    On production server the queries are taking really long time. Right now we are using first query on prod server.

    I don't have the execution plan from prod server, so it is not attached, but I will try to get it for you.

    Any help will be appreciated.

    Query pasted for quick reference:

    select case when comp.activity_flag = 1 then 'Active' else 'Inactive' end as activity_flag,

    comp.name,

    comp.comp_code,

    (select count(1)

    from dbo.person (nolock)

    where person.comp_code = comp.comp_code

    and person.activity_flag = 1

    and person.reg_flag = 0) as active_users,

    (select count(1)

    from dbo.person (nolock)

    where person.comp_code = comp.comp_code

    and person.activity_flag = 1

    and person.reg_flag = 1) as new_users,

    (select count(1)

    from dbo.person (nolock)

    where person.comp_code = comp.comp_code

    and person.activity_flag = 0) as inactive_users,

    (select count(1)

    from dbo.worker (nolock)

    where worker.owner_code = comp.comp_code) as worker,

    case when

    (selecttop (1) 1

    fromdbo.os_contract (nolock)

    whereos_contract.owner_code = comp.comp_code

    andos_contract.msp_flag = 1) is null then 'false' else 'true' end as msp_flag,

    test_comp_flag,

    parent_comp_flag

    from dbo.comp (nolock)

    where comp.comp_type = 1

  • For me, it's giving 32 records each and it's almost in a flash. Where is the issue ?

  • Why read the persons table three times when you only have to read it once?

    SELECT

    activity_flag = case when comp.activity_flag = 1 then 'Active' else 'Inactive' end,

    comp.name,

    comp.comp_code,

    x1.active_users,

    x1.new_users,

    x1.inactive_users,

    worker = (select count(1)

    from dbo.worker (nolock)

    where worker.owner_code = comp.comp_code),

    msp_flag = CASE WHEN x2.owner_code IS NULL THEN 'false' ELSE 'true' END,

    test_comp_flag,

    parent_comp_flag

    FROM dbo.comp

    OUTER APPLY (

    SELECT

    active_users = SUM(CASE WHEN activity_flag = 1 AND reg_flag = 0 THEN 1 ELSE 0 END),

    new_users = SUM(CASE WHEN activity_flag = 1 AND reg_flag = 1 THEN 1 ELSE 0 END),

    inactive_users = SUM(CASE WHEN activity_flag = 0 THEN 1 ELSE 0 END)

    FROM dbo.person p

    WHERE p.comp_code = comp.comp_code

    ) x1

    LEFT JOIN (

    SELECT owner_code

    FROM dbo.os_contract

    WHERE msp_flag = 1

    GROUP BY owner_code

    ) x2

    ON x2.owner_code = comp.comp_code

    WHERE comp.comp_type = 1

    If your queries are forcing SQL Server to work three times harder than it needs to, then it should come as no surprise that they are slow when running against larger data sets than were used for development. Getting the correct results from a query represents about 20 or 30% of the effort required. Most of the balance is tuning the query.

    “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

  • Provided test data is from my local, so it will not give any issue. Getting data from prod server is not possible for me.

    I have tried all three options (Two queries I attached and third suggested by you), there is a marginal difference between them. All are taking between 5 - 5.30 minutes to execute and returning 341 rows.

    These are the I/O stats :

    Table 'worker'. Scan count 1, logical reads 25439, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'person'. Scan count 341, logical reads 17962891, physical reads 0, read-ahead reads 10466, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'comp'. Scan count 1, logical reads 1065, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'os_contract'. Scan count 1, logical reads 146, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  • -- try changing this

    OUTER APPLY (

    SELECT

    active_users = SUM(CASE WHEN activity_flag = 1 AND reg_flag = 0 THEN 1 ELSE 0 END),

    new_users = SUM(CASE WHEN activity_flag = 1 AND reg_flag = 1 THEN 1 ELSE 0 END),

    inactive_users = SUM(CASE WHEN activity_flag = 0 THEN 1 ELSE 0 END)

    FROM dbo.person p

    WHERE p.comp_code = comp.comp_code

    ) x1

    -- to this

    LEFT JOIN (

    SELECT

    comp_code,

    active_users = SUM(CASE WHEN activity_flag = 1 AND reg_flag = 0 THEN 1 ELSE 0 END),

    new_users = SUM(CASE WHEN activity_flag = 1 AND reg_flag = 1 THEN 1 ELSE 0 END),

    inactive_users = SUM(CASE WHEN activity_flag = 0 THEN 1 ELSE 0 END)

    FROM dbo.person

    GROUP BY comp_code

    ) x1 ON x1.comp_code = comp.comp_code

    “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

  • T.Ashish (6/17/2013)


    Provided test data is from my local, so it will not give any issue. Getting data from prod server is not possible for me.

    I have tried all three options (Two queries I attached and third suggested by you), there is a marginal difference between them. All are taking between 5 - 5.30 minutes to execute and returning 341 rows.

    These are the I/O stats :

    Table 'worker'. Scan count 1, logical reads 25439, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'person'. Scan count 341, logical reads 17962891, physical reads 0, read-ahead reads 10466, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'comp'. Scan count 1, logical reads 1065, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'os_contract'. Scan count 1, logical reads 146, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Have you tested with a couple of new indexes?

    (32 row(s) affected)

    Table 'os_contract'. Scan count 32, logical reads 64, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'comp'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'person'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'worker'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    “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

  • Still not helping.

    We are using a filter "where comp.comp_type = 1" in the query.

    If I change the filter comp.comp_type = 2 then it is taking only 15 seconds.

    We have 3 comp types in table

    Comp_type Count

    1 1

    2 341

    3 21374

  • T.Ashish (6/17/2013)


    Provided test data is from my local, so it will not give any issue. Getting data from prod server is not possible for me.

    I have tried all three options (Two queries I attached and third suggested by you), there is a marginal difference between them. All are taking between 5 - 5.30 minutes to execute and returning 341 rows.

    These are the I/O stats :

    Table 'worker'. Scan count 1, logical reads 25439, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'person'. Scan count 341, logical reads 17962891, physical reads 0, read-ahead reads 10466, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'comp'. Scan count 1, logical reads 1065, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'os_contract'. Scan count 1, logical reads 146, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    1) You seem to be scanning the person table. As Chris suggests, this can almost certainly be addressed with proper index.

    2) What is the worker table doing? Not sure why that is necessary.

    3) We REALLY need the actual query plan!!

    4) You probably need OPTION (RECOMPILE) on this statement (even with hard-coded values, but ESPECIALLY if you use variables in your "real" code) due to the extreme data-value-distribution issues you have with the 3 values of comptype. You ABSOLUTELY DO NOT WANT the same plan for each of those 3 values, ESPECIALLY 1 and 3!!

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Why read the persons table three times when you only have to read it once?

    I have seen this type of query SOOO many times at clients over the years. I currently have one that has a DISASTROUS propensity for it!! :Whistling:

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • TheSQLGuru (6/17/2013)


    T.Ashish (6/17/2013)


    Provided test data is from my local, so it will not give any issue. Getting data from prod server is not possible for me.

    I have tried all three options (Two queries I attached and third suggested by you), there is a marginal difference between them. All are taking between 5 - 5.30 minutes to execute and returning 341 rows.

    These are the I/O stats :

    Table 'worker'. Scan count 1, logical reads 25439, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'person'. Scan count 341, logical reads 17962891, physical reads 0, read-ahead reads 10466, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'comp'. Scan count 1, logical reads 1065, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'os_contract'. Scan count 1, logical reads 146, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    1) You seem to be scanning the person table. As Chris suggests, this can almost certainly be addressed with proper index.

    2) What is the worker table doing? Not sure why that is necessary.

    3) We REALLY need the actual query plan!!

    4) You probably need OPTION (RECOMPILE) on this statement (even with hard-coded values, but ESPECIALLY if you use variables in your "real" code) due to the extreme data-value-distribution issues you have with the 3 values of comptype. You ABSOLUTELY DO NOT WANT the same plan for each of those 3 values, ESPECIALLY 1 and 3!!

    Thanks for the reminder Kevin 🙂

    Here's a few indexes which improve performance considerably. I'm not going to claim they are ideal without seeing the plan or playing with the tables:

    CREATE NONCLUSTERED INDEX [ix_comp_type] ON [dbo].[comp]

    ([comp_type] ASC)

    CREATE CLUSTERED INDEX [ucx_os_contract_id] ON [dbo].[os_contract]

    ([owner_code] ASC,[msp_flag] ASC)

    CREATE NONCLUSTERED INDEX [ix_comp] ON [dbo].[person]

    ([comp_code] ASC,[activity_flag] ASC,[reg_flag] ASC)

    CREATE NONCLUSTERED INDEX [ix_comp_code] ON [dbo].[worker]

    ([owner_code] ASC)

    “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

  • this is the actual plan from prod server.

  • T.Ashish (6/17/2013)


    this is the actual plan from prod server.

    Thanks for posting.

    I'd recommend you implement the indexes I posted earlier and repost the actual plan from prod with the indexes in place. Taken in isolation from all other queries against these tables, they are fine and should give you at least an order of magnitude lift in performance for this query: however, you should examine index usage stats and query activity (most frequent/most expensive) and tweak if necessary.

    Here's a slightly modified version of the query you ran in prod. I've removed the eccentric and offputting formatting and corrected a filter in the os_contract subquery:

    DECLARE @0 BIT, @1 INT

    SELECT @0 = 1, @1 = 2

    SELECT

    activity_flag = case when c.activity_flag = 1 then 'Active' else 'Inactive' end,

    msp_flag = case when bsc.msp_flag = 1 then 'true' else 'false' end,

    c.name,

    c.comp_code,

    p.active_users,

    p.new_users,

    p.inactive_users,

    w.worker,

    c.test_comp_flag,

    c.parent_comp_flag

    FROM dbo.comp c ( NOLOCK )

    LEFT JOIN (

    SELECT

    comp_code,

    active_users = SUM(case when activity_flag = 1 and reg_flag = 0 then 1 else 0 end),

    new_users = SUM(case when activity_flag = 1 and reg_flag = 1 then 1 else 0 end),

    inactive_users = SUM(case when activity_flag = 0 then 1 else 0 end)

    FROM dbo.person

    GROUP BY comp_code

    ) p

    ON p.comp_code = c.comp_code

    LEFT JOIN (

    SELECT

    COUNT(*) worker,

    owner_code

    FROM dbo.worker

    GROUP BY owner_code

    ) w

    ON w.owner_code = c.comp_code

    LEFT JOIN (

    SELECT

    msp_flag,

    owner_code

    FROM dbo.os_contract

    WHERE msp_flag = @0

    GROUP BY msp_flag, owner_code

    ) bsc

    ON bsc.owner_code = c.comp_code

    --and bsc . msp_flag = @0

    WHERE c.comp_type = @1

    ORDER BY c.name

    “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 12 posts - 1 through 11 (of 11 total)

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