View runs slower

  • sathwik.em91

    Right there with Babe

    Points: 779

    I have the following query

    SELECT

    DateADD(day,9-DATEPART(dw,cdata .call_datetime ),cast(cdata .call_datetime as date) ) as effective_date

    , A.EmployeeID AS employee_id

    , COUNT(cdata .Extension) AS metric_value

    --, ISNULL(MS.metric_id,'') AS metric_id

    FROM

    (

    SELECT

    EmployeeID

    FROM

    ad.it_activedirectorydata

    WHERE

    [Enabled] = 'True'

    ) A

    INNER JOIN [vw_exampl] cdata ON A.EmployeeID =cdata .employeeID

    INNNER JOIN tbl2 ON A.employyeid=tbl2.employeeid

    GROUP BY

    A.EmployeeID

    , DateADD(day,9-DATEPART(dw,cdata .call_datetime ),cast(cdata .call_datetime as date) )

    I can see that the view runs pretty slow and makes this query to run slow.It takes hours to complete and which I dont want it to happen, also the view has nearly 5 million rows of data in it.

    Could someone recommend me how to make the query faster?

    Any feedback or suggestions on the query?

     

    • This topic was modified 4 weeks, 1 day ago by  sathwik.em91.
  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21534

    we're all going to ask the same thing - can you upload a query plan - I would suspect a missing index or the group by dateadd function….

    without seeing the view code, it's quite tough for us... query plan is your best bet

    MVDBA

  • ktflash

    Ten Centuries

    Points: 1114

    and please use the "insert code sample" function next time

    select much
    ,better
    ,code
    from to_read

    I wanna be the very best
    Like no one ever was

  • Grant Fritchey

    SSC Guru

    Points: 396339

    Yep, execution plan.

    Also, grouping by a function means poor, or no, index use. Calculated column with an index could help.

    Still, execution plan.

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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Jonathan AC Roberts

    SSCoach

    Points: 17166

    I'm just wondering why you wrote it with an inline-table?

    When this would do the same:

    SELECT DATEADD(day, 9 - DATEPART(dw, cdata.call_datetime), CAST(cdata.call_datetime AS date)) AS effective_date,
    A.EmployeeID AS employee_id,
    COUNT(cdata.Extension) AS metric_value
    --, ISNULL(MS.metric_id,'') AS metric_id
    FROM ad.it_activedirectorydata A
    INNER JOIN [vw_exampl] cdata
    ON A.EmployeeID = cdata.employeeID
    INNER JOIN tbl2
    ON A.employyeid = tbl2.employeeid
    WHERE A.[Enabled] = 'True'
    GROUP BY A.EmployeeID, DATEADD(day, 9 - DATEPART(dw, cdata.call_datetime), CAST(cdata.call_datetime AS date))

     

  • Grant Fritchey

    SSC Guru

    Points: 396339

    Also, thinking on this, are you saying that the query and the view are the same, but the view is slower? Or are you saying that you wrote a query against a view, with a completely different table structure than what you're showing us? Either means to compare, we need both execution plans.

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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21534

    Grant Fritchey wrote:

    Also, thinking on this, are you saying that the query and the view are the same, but the view is slower? Or are you saying that you wrote a query against a view, with a completely different table structure than what you're showing us? Either means to compare, we need both execution plans.

    I just saw

    vw_exampl

    and assumed that was the view the OP refered to..... BAD DBA... NEVER ASSUME ANYTHING. 🙂

    MVDBA

  • Grant Fritchey

    SSC Guru

    Points: 396339

    MVDBA (Mike Vessey) wrote:

    Grant Fritchey wrote:

    Also, thinking on this, are you saying that the query and the view are the same, but the view is slower? Or are you saying that you wrote a query against a view, with a completely different table structure than what you're showing us? Either means to compare, we need both execution plans.

    I just saw

    vw_exampl

    and assumed that was the view the OP refered to..... BAD DBA... NEVER ASSUME ANYTHING. 🙂

    Heck. It might be. At this point it's all unclear to me.

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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Jeff Moden

    SSC Guru

    Points: 995687

    sathwik.em91 wrote:

    Could someone recommend me how to make the query faster?

    Yes, but we need more information.  Please Read'n'Heed the article at the second link in my signature line below for how to provide such information so we can help you.

    sathwik.em91 wrote:

    Any feedback or suggestions on the query?

    Yes... see above. 😉

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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)

Viewing 9 posts - 1 through 9 (of 9 total)

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