Query running very slow

1

Can anyone advise as to why this query is so slow please.

Both queries run in seconds seperatly however when I join them it takes a very long time.

DECLARE @MinDate DATETIME = '20160102', @MaxDate DATETiME = DATEADD(yyyy, Year(Getdate()) - 1900 + 1, -1);

With DateRanges_Cte(xDateRange) As (
Select Date from
(SELECT TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
) Alldates Left Outer Join CRM..HolidaySetItems On Alldates.Date=HSIt_HolidayDate
Where DATENAME(DW, Date) Not In ('Saturday','Sunday') and HSIt_HolidayDate Is Null
)

Select xDateRange, Alldata.*
From DateRanges_Cte Left Outer Join (

Select Convert(datetime,Convert(date,Oppo_Opened)) [Date], Max(dataComm.Telesales) Telesales, Max(dataComm.[F2F Calls]) [F2F Calls], Max(dataProj.[Projs opened]) [Projs opened]
, count(distinct Oppo_OpportunityId) [Oppos opened], Max(dataComm.QFU) [QFU], Max(dataComm.EQFU) [EQFU], Max(dataFlipped.[Oppos filipped]) [Oppos flipped]
, Sum(Case When Year(Oppo_Closed) >= 2016 Then 1 Else 0 End ) [SOPs]
from Opportunity
Left Outer Join
(Select Convert(datetime,Convert(date,comm_completedtime)) xDate, Sum(Case When comm_action='TELES' Then 1 Else 0 End) Telesales
, Sum(Case When comm_action='QFU' Then 1 Else 0 End) [QFU], Sum(Case When comm_action='EQFU' Then 1 Else 0 End) [EQFU]
, Sum(Case When comm_action In ('SALVI','MEETING','CPDP','DV') THen 1 Else 0 End) [F2F Calls]
, Sum(Case When comm_action In ('TSQFU','TSQFU2','CO') THen 1 Else 0 End) [TQFU/TQFU2/CO]
From Communication Inner Join Comm_Link On Comm_CommunicationId=CmLi_Comm_CommunicationId
Where Comm_Deleted Is Null and CmLi_Deleted IS Null and Year(comm_completedtime)>=2016 and Comm_Status='Complete' and Comm_ChannelId In (7,50013,50024)
Group by Convert(datetime,Convert(date,comm_completedtime))
) dataComm On dataComm.xDate=Convert(datetime,Convert(date,Oppo_Opened))

  Left Outer Join
  (Select Convert(datetime,Convert(date,proj_opened)) xDate, Count(proj_projectId) [Projs opened]
   From Project
   Where Proj_Deleted Is Null and Year(proj_opened)>=2016
   Group by Convert(datetime,Convert(date,proj_opened))
   ) dataProj On dataProj.xDate=Convert(datetime,Convert(date,Oppo_Opened))

   Left Outer Join
   (Select Convert(datetime,Convert(date,oppo_flipped_date)) xDate, Count(distinct Oppo_OpportunityId) [Oppos filipped]
    From Opportunity
    Where Oppo_Deleted Is Null and Oppo_Description Not Like 'Auto :%' and Year(oppo_flipped_date)>=2016 and Oppo_ChannelId In (7,50013,50024)
    Group by Convert(datetime,Convert(date,oppo_flipped_date))
    ) dataFlipped On dataFlipped.xDate=Convert(datetime,Convert(date,Oppo_Opened))

Where Oppo_Deleted Is Null and Oppo_Description Not Like 'Auto :%' and Oppo_ChannelId In (7,50013,50024) --and Convert(datetime,Convert(date,Oppo_Opened)) In (Select xDateRange from DateRanges_Cte)
and Year(oppo_opened)>=2016

Group by Convert(datetime,Convert(date,oppo_opened))
) Alldata On Alldata.[Date]=xDateRange

--Order by 1 desc

2

hi

one idea is

query 1 into temp table #abc
query 2 into temp table #def
now join #abc and #def

another idea is to filter the data first
lets say table has 1000 rows
one of the filters is where name = 'sam' which returns 5 rows
then do this first into temp table #xyz
and then use #xyz for the rest of the query

3

Thanks for your response.
I have changed the query as below and it works perfectly in SSMS.

DECLARE @MinDate DATETIME = '20160102', @MaxDate DATETiME = DATEADD(yyyy, Year(Getdate()) - 1900 + 1, -1);

--With DateRanges_Cte(xDateRange) As (
Select * Into #TempTable
From (
Select Date from
(SELECT TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
) Alldates Left Outer Join CRM..HolidaySetItems On Alldates.Date=HSIt_HolidayDate
Where DATENAME(DW, Date) Not In ('Saturday','Sunday') and HSIt_HolidayDate Is Null
) X

Select #TempTable.[Date],Telesales,[F2F Calls],[Projs opened],[Oppos opened],[QFU],[EQFU],[Oppos flipped],[TQFU/TQFU2/CO],[SOPs Opened]
From #Temptable Left Outer Join (

Select Convert(datetime,Convert(date,Oppo_Opened)) [Date], Max(dataComm.Telesales) Telesales, Max(dataComm.[F2F Calls]) [F2F Calls], Max(dataProj.[Projs opened]) [Projs opened]
, count(distinct Oppo_OpportunityId) [Oppos opened], Max(dataComm.QFU) [QFU], Max(dataComm.EQFU) [EQFU], Max(dataFlipped.[Oppos filipped]) [Oppos flipped]
, Sum(Case When Year(Oppo_Closed) >= 2016 Then 1 Else 0 End ) [SOPs Opened],Max([TQFU/TQFU2/CO]) [TQFU/TQFU2/CO]
from Opportunity
Left Outer Join
(Select Convert(datetime,Convert(date,comm_completedtime)) xDate, Sum(Case When comm_action='TELES' Then 1 Else 0 End) Telesales
, Sum(Case When comm_action='QFU' Then 1 Else 0 End) [QFU], Sum(Case When comm_action='EQFU' Then 1 Else 0 End) [EQFU]
, Sum(Case When comm_action In ('SALVI','MEETING','CPDP','DV') THen 1 Else 0 End) [F2F Calls]
, Sum(Case When comm_action In ('TSQFU','TSQFU2','CO') THen 1 Else 0 End) [TQFU/TQFU2/CO]
From Communication Inner Join Comm_Link On Comm_CommunicationId=CmLi_Comm_CommunicationId
Where Comm_Deleted Is Null and CmLi_Deleted IS Null and Year(comm_completedtime)>=2016 and Comm_Status='Complete' and Comm_ChannelId In (7,50013,50024)
Group by Convert(datetime,Convert(date,comm_completedtime))
) dataComm On dataComm.xDate=Convert(datetime,Convert(date,Oppo_Opened))

  Left Outer Join
  (Select Convert(datetime,Convert(date,proj_opened)) xDate, Count(proj_projectId) [Projs opened]
   From Project
   Where Proj_Deleted Is Null and Year(proj_opened)>=2016
   Group by Convert(datetime,Convert(date,proj_opened))
   ) dataProj On dataProj.xDate=Convert(datetime,Convert(date,Oppo_Opened))

   Left Outer Join
   (Select Convert(datetime,Convert(date,oppo_flipped_date)) xDate, Count(distinct Oppo_OpportunityId) [Oppos filipped]
    From Opportunity
    Where Oppo_Deleted Is Null and Oppo_Description Not Like 'Auto :%' and Year(oppo_flipped_date)>=2016 and Oppo_ChannelId In (7,50013,50024)
    Group by Convert(datetime,Convert(date,oppo_flipped_date))
    ) dataFlipped On dataFlipped.xDate=Convert(datetime,Convert(date,Oppo_Opened))

Where Oppo_Deleted Is Null and Oppo_Description Not Like 'Auto :%' and Oppo_ChannelId In (7,50013,50024) and Year(oppo_opened)>=2016

Group by Convert(datetime,Convert(date,oppo_opened))
) Alldata On #TempTable.[Date]=Alldata.[Date]

Order by 1

Drop table #TempTable

However I get the following error when trying to run the same query in excel.

image
image917×126 14.2 KB

4

the error you are getting is saying .. cannot open database server ..

once it connects then it can run the Query !!!

5

Hello, thanks for the reply.
That's is what's confusing me as I can run ok from SSMS but not from excel what am I missing

6

hi

you will have to start debugging !!!

what are the things involved when running SQL in Excel
is each one working correctly ..
in what sequence things happen
please check and verify each step !!

please google search lots of articles !!

i have faced these issues as a software developer a MILLION times
something does not work .. gives some vague error message ..
it could be a million different things .. and i have to find out WHAT
big PAIN big big big PAIN
:slight_smile:

7

Hello - this is now resolved added SET NOCOUNT ON to the start and all ok now