View runs slower

Question

View runs slower

sathwik.em91

SSC Eights!

Points: 878
More actions
January 28, 2020 at 3:37 pm

#3720947
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 years, 10 months ago by sathwik.em91.

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

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

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

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 Default port Points: 1400 More actions · Answer 2

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

select much
      ,better
      ,code
from to_read

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 4

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: 398882 More actions · Answer 5

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 6

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: 398882 More actions · Answer 7

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

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.

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)